Calculating in column with the formula -Error

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Calculating in column with the formula -Error

Davy Elias -davyintermediary@gmail.com
Hi ,

I calculating number in column , but the result is different if we calculate  not with the formula


I calculate  354.84 x 13,320  the result is 4,726,468.80

but if we using 354.84 from formula

126.72+3.15- (+25%*126.72) + (2.5%* (25%*126.72)) result 98.9820
181.5 + 3.15 -(25%*181.5) +2.5%*(25%*126.72)) result is 140.4094
148.5 + 3.15 -(25%*148.5) +2.5% x(25%*148.5)) result is 115.4531

Then we sum  98.9820 +140.4094+115.4531 =354.8445

then we calculate the result 354.9885 * 13,320 the result is 4,726,528.74

with calculator or I input the number single 354.84 x 13,320 = 4,726,468.80

Question : Why is different  value ?

do you have solution for this ?

Tks
Davy

Virus-free. www.avast.com


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

calculating.ods (13K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Calculating in column with the formula -Error

Martin Groenescheij

On 17/07/17 1:38 PM, Davy Elias -[hidden email] wrote:

> Hi ,
>
> I calculating number in column , but the result is different if we
> calculate  not with the formula
>
>
> I calculate  354.84 x 13,320  the result is 4,726,468.80
>
> but if we using 354.84 from formula
>
> 126.72+3.15- (+25%*126.72) + (2.5%* (25%*126.72)) result 98.9820
> 181.5 + 3.15 -(25%*181.5) +2.5%*(25%*126.72)) result is 140.4094
> 148.5 + 3.15 -(25%*148.5) +2.5% x(25%*148.5)) result is 115.4531
>
> Then we sum  98.9820 +140.4094+115.4531 =354.8445
>
> then we calculate the result 354.9885 * 13,320 the result is
> _*4,726,528.74
>
> *_
> with calculator or I input the number single 354.84 x 13,320 =
> 4,726,468.80
>
> Question : Why is different  value ?

Your problem is that you display the calculated results with two
decimals while the real value has sometimes 3 or 4 decimals.

Calc use the real values and when you calculated the results manually
you enter them with 2 decimals as displayed.

You can see this if you change the cell format to display more decimals.


>
> do you have solution for this ?

You are the solution, by changing the way you do the calculation.
If you want the results to be the same as you did manual then you need
to change your formulas e.g.
Change Cell G4 from *=+0.25*D4* to *=ROUND(+0.25*D4;2)**

*

>
> Tks
> Davy
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> Virus-free. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Calculating in column with the formula -Error

Keith N. McKenna
In reply to this post by Davy Elias -davyintermediary@gmail.com
On 7/16/2017 11:38 PM, Davy Elias -[hidden email] wrote:

> Hi ,
>
> I calculating number in column , but the result is different if we
> calculate  not with the formula
>
>
> I calculate  354.84 x 13,320  the result is 4,726,468.80
>
> but if we using 354.84 from formula
>
> 126.72+3.15- (+25%*126.72) + (2.5%* (25%*126.72)) result 98.9820
> 181.5 + 3.15 -(25%*181.5) +2.5%*(25%*126.72)) result is 140.4094
> 148.5 + 3.15 -(25%*148.5) +2.5% x(25%*148.5)) result is 115.4531
>
> Then we sum  98.9820 +140.4094+115.4531 =354.8445
>
> then we calculate the result 354.9885 * 13,320 the result is _*4,726,528.74
>
> *_
> with calculator or I input the number single 354.84 x 13,320 = 4,726,468.80
>
> Question : Why is different  value ?
>
> do you have solution for this ?
>
> Tks
> Davy
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> Virus-free. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
>
>
> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
Davy;

Basically the problem is that 354.8445 and 354.84 are different numbers.
To make the calculations in your spreadsheet match change the formula in
I8 to =+I7*ROUNDDOWN(I6;2). What that does is round the value in I6 down
2 places thus matching the value j6.

Regards
Keith


signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Calculating in column with the formula -Error

Brian Barker
In reply to this post by Davy Elias -davyintermediary@gmail.com
At 10:38 17/07/2017 +0700, Davy Elias wrote:
>I calculating number in column, but the result is different if we
>calculate not with the formula

The result is different because you have made mistakes!

>I calculate 354.84 x 13,320 the result is 4,726,468.80

... which is exactly correct.

>but if we using 354.84 from formula
>126.72+3.15- (+25%*126.72) + (2.5%* (25%*126.72)) result 98.9820
>181.5 + 3.15 -(25%*181.5) +2.5%*(25%*126.72)) result is 140.4094
>148.5 + 3.15 -(25%*148.5) +2.5% x(25%*148.5)) result is 115.4531

Hold on! First, the second and third formulae have extra right
parentheses. Perhaps we should drop the last one in each case?

If these formulae are meant to be parallel, that 126.72 in the second
formula should surely be 181.5? The first result is then correct, but
the second and third are 140.409375 and 115.453125, which are not
quite what you say, but do round to your figures to the precision you
have quoted.

>Then we sum 98.9820 +140.4094+115.4531 =354.8445

Correct.

>then we calculate the result 354.9885 * 13,320 the result is 4,726,528.74

No: that's 4728446.82. But in any case, where did 354.9885 come
from?! Using the value from above, 354.8445, instead, you get the
result you claim.

>with calculator or I input the number single 354.84 x 13,320 = 4,726,468.80

Correct.

>Question : Why is different value ?

Simply because you have rounded some of your values mid-way through
the calculation.

>do you have solution for this ?

Yes:

1. If you round values in the middle of a calculation, you will get
different results. Keep all the precision you can work with until the
final result - rounding that if you wish.

2. Spreadsheet calculations are performed using the full precision of
your computer system - plenty for what you are doing here. But be
aware that the precision *shown* in cells is controlled by cell
formatting, so may not be accurate. The correct values are still
hiding in the cells and will be used in further calculations, just as
you need. (This is not so if you have Tools | Options... | OpenOffice
Calc | Calculate | Precision as shown ticked, but that is not your
problem here.) Your two final values are derived from the accurate
stored intermediate value of 354.8445 and the different, rounded
value of 354.84.

3. You are subtracting 25% and adding 2.5% of that. 2.5% of 25% is
0.625%, so you are subtracting 24.375%, leaving 75.625% of the
original. Your formula above:
126.72+3.15- (+25%*126.72) + (2.5%* (25%*126.72))
condenses to the much more simple
75.625%*126.72 +3.15 .

I trust this helps.

Brian Barker


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

Loading...