# Possible Formula Problem Classic List Threaded 5 messages Open this post in threaded view
|

## Possible Formula Problem

 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.
Open this post in threaded view
|

## Re: Possible Formula Problem

 It's a matter of precision. Calc gives the correctly rounded result. Your manual calculation is made from truncated intermediate results. Using Crimson Editor I get: (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) /\$ 2005/11/8, Blair & Carolyn Johnson <[hidden email]>: > 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. > > --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email]
Open this post in threaded view
|

## Re: Possible Formula Problem

 In reply to this post by Blair & Carolyn Johnson 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. 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. > >   > --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email]