EXCEL 2000 - IF function returns wrong result from logical test

[Sean Duffy]

I've tried the following simple IF test as a check on totals in a sheet where
I'm compiling accounts:

=IF(C24=SUM(C25:C50),"","!")

bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm
getting a FALSE result and "!" is appearing in the check cell

In another cell i've summed C25:C50 to prove to myself that it really is the
same number - yes, it is!

the number in C24 is from another sheet in the same workbook and is in turn
the result of a SUM in that sheet - I've double checked that value and it is
correct also.

the values in each of the cells C25 to C50 are each from a a different sheet
in the same workbook

Can anyone suggest why I'm getting this false "FALSE" result?

Thanks

 

[Rick Rothstein]

What are your numbers? Are they floating point values by any chance?

 

[John C]

If any of the values are calculated, there could be rounding errors.
i.e.: C24 could really be $45.000003
and SUM(C25:C50) might really be $45.0007
but if both are formatted as $45.00, then they look equal, but really aren't.

 

[Sean Duffy]

Thanks for this suggestion - I've checked but no luck

 

[Sean Duffy]

The numbers are totals for expenditure in a business against various budget
headins so they are simple pounds and pence figures - 93,647.02 - 'floating
decimal points' sounds frightening but an interesting accounting technique!!

 

[John C]

Well, sometimes Excel adds extra digits in the 10,000ths place, because it
can. You still might try modifying your formula as such:

=IF(ROUND(C24,2)=ROUND(SUM(C25:C50),2),"","!")

 

[Sean Duffy]

John C, Thanks a million for your suggestion - it works!

 

[John C]

You are welcome, and thanks for the feedback.

 

[ShaneDevenshire]

Hi,

Of course there is the core question of why this happens. Excel is run on a
computer which works in binary, which means all numbers are stored and
calculated in binary. Each number is represented as a series of ones and
zeros, for example: 1010101011. This number is not always an exact
equivalent of the decimal equivalent, hense there may be a small error in the
last decimal places. That is enough of an error to cause the Excel not to
see the two numbers as equal.

As suggested, if this is critical you would resort to a round function. In
many cases formatting the cell to 0 or 2 decimals will "hide" the issue. But
in your case Excel is checking all digits out to 15 decimals so hiding isn't
a solution.