You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by "Davy Elias -davyintermediary@gmail.com" <da...@gmail.com> on 2017/07/17 03:38:42 UTC
Calculating in column with the formula -Error
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>
Re: Calculating in column with the formula -Error
Posted by Martin Groenescheij <Ma...@Groenescheij.COM>.
On 17/07/17 1:38 PM, Davy Elias -davyintermediary@gmail.com 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: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
Re: Calculating in column with the formula -Error
Posted by Brian Barker <b....@btinternet.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: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Calculating in column with the formula -Error
Posted by "Keith N. McKenna" <ke...@comcast.net>.
On 7/16/2017 11:38 PM, Davy Elias -davyintermediary@gmail.com 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: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
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