Excel 2003 - Simple IF() Formula Will Not Work For Me

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 07 Nov 2006 02:22 GMT

I may be very sleepy, but try this and tell me why I get "OFF", instead of
"OK":

1. Open a new workbook
2. Enter the following:
Cell A1 =11813.21
B1 =5375.65
C1 =6437.56
D1 =IF(A1-B1-C1=0,"OK","OFF")

3. My result in D1 is "OFF" and it should be "OK"
4. Get a calculator or run it in your smart brain:
11813.21 minus 5375.65 minus 6437.56 = 0 (Big fat Zero!!)

If you play with the first three cells, sometimes it works, sometimes not.
I cannot see why or why not.

If the TRUE statement is performed in a seperate cell, and the IF is done in
another, I get "OK" when I should.

Is this a flaw in Excel 2003?

Reply to this Message

Teethless mama - 07 Nov 2006 02:36 GMT

Try this:

=IF(A1-SUM(B1:C1)=0,"OK","OFF")

> I may be very sleepy, but try this and tell me why I get "OFF", instead of
> "OK":
[quoted text clipped - 17 lines]
>
> Is this a flaw in Excel 2003?

Reply to this Message

Teethless mama - 07 Nov 2006 02:40 GMT

Another way,

=IF(ROUND(A1-B1-C1,2)=0,"OK","OFF")

> I may be very sleepy, but try this and tell me why I get "OFF", instead of
> "OK":
[quoted text clipped - 17 lines]
>
> Is this a flaw in Excel 2003?

Reply to this Message

Duke Carey - 07 Nov 2006 02:42 GMT

The issue is rounding. One or more of the cells probably shows a value that
carries ou to many decimals past the 2 you have entered, but displays to,
say, 11813.21.

Use this formula instead

=IF(round(A1-B1-C1,2)=0,"OK","OFF")

> I may be very sleepy, but try this and tell me why I get "OFF", instead of
> "OK":
[quoted text clipped - 17 lines]
>
> Is this a flaw in Excel 2003?

Reply to this Message

David Biddulph - 07 Nov 2006 07:32 GMT

The problem arises because the numbers cannot be represented precisely in
binary. The only decimal numbers that can be represented precisely in
binary are 0.5, 0.25, 0.125, etc., and multiples thereof. That's why you
may need to round the result of the calculation.

Signature

David Biddulph

>I may be very sleepy, but try this and tell me why I get "OFF", instead of
> "OK":
[quoted text clipped - 18 lines]
>
> Is this a flaw in Excel 2003?

Reply to this Message

Scott - 07 Nov 2006 08:15 GMT

All the above formulas work, but what is wrong with mine? Try mine for
yourself; there are no extra fractions past two decimal places.

David's answer makes sense, but it is simple math that = zero. Why can't
Excel do that? It seems to be a flaw to me.

Thanks for the input.

> The problem arises because the numbers cannot be represented precisely in
> binary. The only decimal numbers that can be represented precisely in
[quoted text clipped - 22 lines]
> >
> > Is this a flaw in Excel 2003?

Reply to this Message

David Biddulph - 07 Nov 2006 08:26 GMT

You'll find some more of the background at
http://www.cpearson.com/excel/rounding.htm
http://support.microsoft.com/kb/214118
http://support.microsoft.com/kb/78113

Signature

David Biddulph

> All the above formulas work, but what is wrong with mine? Try mine for
> yourself; there are no extra fractions past two decimal places.
[quoted text clipped - 33 lines]
>> >
>> > Is this a flaw in Excel 2003?

Reply to this Message

Jerry W. Lewis - 07 Nov 2006 13:03 GMT

> All the above formulas work, but what is wrong with mine? Try mine for
> yourself; there are no extra fractions past two decimal places.

=(A1-B1-C1)
evaluates to -9.09494701772928E-13, which is why your If function fails.

> David's answer makes sense, but it is simple math that = zero. Why can't
> Excel do that? It seems to be a flaw to me.

As David has pointed out, all of your decimal fractions are non-terminating
binary fractions that cannot be exactly represented in binary (just as 1/3
cannot be exactly represented as a decimal fraction). Excel (and almost all
other computer software) uses IEEE double precision binary representation of
floating point numbers. The decimal equivalents of the binary approximations
to your numbers are

11813.2099999999991268850862979888916015625
5375.649999999999636202119290828704833984375
6437.5600000000004001776687800884246826171875

Do the math yourself; Excel's answer is the correct result given the
unavoidable approximations to your initial numbers.

The reason that rounding solves the problem should be obvious. The
alternate proposal of =IF(A1-SUM(B1:C1)=0,"OK","OFF") is a less robust
solution that will not work for all numbers.

Jerry

Reply to this Message

Scott - 07 Nov 2006 17:34 GMT

Thanks guys. Fascinating. Us accountants cannot comprehend things that do
not balance.

> > All the above formulas work, but what is wrong with mine? Try mine for
> > yourself; there are no extra fractions past two decimal places.
[quoted text clipped - 24 lines]
>
> Jerry

Reply to this Message

Dana DeLouis - 07 Nov 2006 18:13 GMT

Just for discussion, I like to have a custom function that adds Financial
data as a Currency data type.
In a program such as Microsoft's Access, one usually assigns monetary values
the Currency data type also.

For your example:
=A1-Total(B1:C1)=0
returns True.

A custom function is usually more sophisticated then this one, as it will
work on multiple areas, etc.

Function Total(rng As Range)
Dim T As Currency
Dim Cell As Range

T = 0
For Each Cell In rng.Cells
T = T + Cell
Next Cell
Total = T
End Function

Again, just an idea if you want to go this route.

Signature

HTH :>)
Dana DeLouis
Windows XP & Office 2003

> Thanks guys. Fascinating. Us accountants cannot comprehend things that
> do
[quoted text clipped - 34 lines]
>>
>> Jerry

Reply to this Message