Sum in Pivot table returns not precise number

[Legacy 137941]

Some of the numbers added results in rounding errors. For example three numbers with two decimal points added should result in 25,000.00 but the result is 24,999,9999999990000

 

[pgc01]

Hi jr1
Welcome to the board

There can be rounding errors, due to the conversions Base10<->Base2. Most of the computer programs use the IEEE 754 standard where this is decribed in detail. Anyway, if you remember base conversions from school, you remember that a number that has a finite number of decimals in one base may have an infinite number of decimals (although periodic) in another.

For more details check:

http://support.microsoft.com/kb/78113

 

[Legacy 137941]

Thanks for your asnwer, It helped explaining Excel shorfall. Precision as displayed did not help either. I will have to use formula to make the statement true with > 0.001 and < 0.001 to fix my problem. Any other suggestion?

 

[pgc01]

Hi

One of the usual solutions is to round the number. For example if you use 2 decimals use

=Round(A1,2)

Another would be to format the number with 2 decimals and use "Set precision as displayed". This would impact, however, all other values in the workbook.