there appears to be a basic addition / subtraction error in Excel

Question

Hi,

Generated a simple file in Excel looking at charges to credit card vs payment to track current balance.

I had some basic conditional formatting which would highlight when outstanding balance was zero... however an apparent zero did not highlight.

I then reviewed the numbers to make sure all were (again) basic 2 decimal currency numbers, all appeared ok... however the calc column was showing errors from (in some instances) the 3rd decimal.

I expanded the numbers to show more decimal places (30) to ensure my input numbers were definitely not hiding some fat finger errors.... all appeared to be correct.... zeros from the third decimal onwards.

Transferred al this to a new file to (again) make sure all basic numbers and I had not introduced unforced error.

Basic calc colm was.... =(D830-A828)+B828 where D830 was transferred in negative balance of -€4.11 ... Col A was new charge and Col B was paymnet.

Sample:

49.99000000000000000000000000000000	0.00000000000000000000000000000000		-107.78000000000000000000000000000000
49.52000000000000000000000000000000	0.00000000000000000000000000000000		-57.79000000000020000000000000000000
0.00000000000000000000000000000000	100.00000000000000000000000000000000		-8.27000000000021000000000000000000
0.00000000000000000000000000000000	850.00000000000000000000000000000000		-108.27000000000000000000000000000000
21.30000000000000000000000000000000	0.00000000000000000000000000000000		-958.27000000000000000000000000000000
10.72000000000000000000000000000000	0.00000000000000000000000000000000		-936.97000000000000000000000000000000

Note as the calc moves up (more recent charges / payments are introduced at top of file) the "apparent Error ......... current balance -€108.27.... no charges €0.00 and payment of €100.00 results in New Balance of -€8.27000000000000210000000000...

Any ideas of what's happening here??

Regards

Answer 1

Thanks HansV for the response.

Understand this is likely associated with the inherent inaccuracy of storing / manipulating floating point numbers. However when Excel is using currency as the "data type" I thought maybe there should be some rounding already applied to avoid these errors (in my instance often occurring well beyond significant decimal places -1.00E-11).

Thanks again

Answer 2

The way Excel handles numbers causes tiny rounding errors.

Since all your numbers are currency amounts, you should round the result of calculations to 2 decimal places:

=ROUND(D830-A828+B828, 2)