On Tuesday 08 November 2005 07:09 am, Blair & Carolyn Johnson wrote:

> Thank you to those who replied to my original email. I didn't know I could

> not send an attachment. I will hopefully explain the problem here. The

> problem I have is with a formula giving me the wrong answer.....

> I have had several "knowledgeable" people look at this and so far no

> solution ......

> In Column A Row 1 type $21.09

> In Column B Row 1 type formula =(A1*500+58)/500*1.01

> The result should be $21.42

> What I am trying to calculate is - $21.09 is the purchase price and for

> every 500 purchased, a $58.00 fee is applied, plus I want to make a 1%

> profit.

> Next, to calculate the profit I would receive on 500 units, the formula I

> used is =(B1-A1)*500-58 This formula gives me the selling price less the

> purchase price for 500 units and then less the $58.00 fee.

> The problem is this formula gives me $106.03 and yet I manually calculate

> this to be $107.00.

> I then tried just typing the numbers $21.09 and $21.42 in open cells and

> applying the formula to them and it works fine. I get the $107.00 result.

> The only difference is that B1 has a formula in it, but I don't think that

> should make any difference, because that is the nice thing about

> spreadsheets!!

> Can anyone tell me what I am doing wrong, or is this a problem with the

> program?

> Thank you.

From a couple of members of this mailing list (one explains the problem and

the other tells you how to change your formula to give the results you need):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

(21.09*500+58)/500*1.01

$ans = 21.418060

(Correctly rounded to 21.42 in calc)

(21.418060-21.09)*500-58

$ans = 106.030000

(Correctly displayed as 106.03 in calc)

(21.42-21.09)*500-58

$ans = 107.000000

(Your truncated, erroneous calculation)

/$

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Change the formula in column B row 1 with =ROUND((A1*500+58)/500*1.01;2)

then you will get the $107.00 you are expecting.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---------------------------------------------------------------------

To unsubscribe, e-mail:

[hidden email]
For additional commands, e-mail:

[hidden email]