IF statement evaluating with rounding error

[henryg]

I have the sum of a column which evaluates to 2 decimal places exactly, but when referenced in a IF formula it evaluates with many/multiple decimal places, ie Excel is introducing a rounding error.

So, say, A6=sum(A1:A5) evaluates to 21.75 exactly, in my IF(A6<>G7...) formula it would evaluate to 21.749999.

It took me a while to realise I had to round the sum formula to get rid of the problem, but how can a formula evaluate to different values directly as compared to when referenced?

 

[scottylad2]

It's more likely a formatting issue, try going intot he Format Cells dialogue and make sure each cell is formatted the same, first display 5 numbers after the decimal then just 2, your answers should be identical. The other thing to try is on the resluts of both cells, in another cell =Result1=Result2 and if it evaluates to FALSE then there's another issue

 

[henryg]

It's more likely a formatting issue, try going intot he Format Cells dialogue and make sure each cell is formatted the same, first display 5 numbers after the decimal then just 2, your answers should be identical.

All formats are the same, and each individual summed value has only 2 decimal places or is rounded to 2. There are no values after the point to 20 places.

The other thing to try is on the resluts of both cells, in another cell =Result1=Result2 and if it evaluates to FALSE then there's another issue

The only FALSE value is given if I take away the rounded sum.

I didn't know about this == method, so the problem is worth it just for the education.


Henry