Possible Formula Problem

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Possible Formula Problem

Blair & Carolyn Johnson
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.
Reply | Threaded
Open this post in threaded view
|

Re: Possible Formula Problem

Henrik Sundberg
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]

Reply | Threaded
Open this post in threaded view
|

Re: Possible Formula Problem

Joe Conner
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]

Reply | Threaded
Open this post in threaded view
|

Re: Possible Formula Problem

Terry Hackett
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]

Reply | Threaded
Open this post in threaded view
|

Re: Possible Formula Problem

Dan Lewis-6
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.
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]