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. |
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] |
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] |
In reply to this post by Blair & Carolyn Johnson
I suspect the problem is that the formula is working to factions of
cents. If you do the calculation by hand without rounding to the nearest cent, you get the answer $21.418 not $21.42. --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
In reply to this post by Blair & Carolyn Johnson
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. 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] |
Free forum by Nabble | Edit this page |