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

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?

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

Try this:

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



"Scott" wrote:

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?

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

Another way,

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



"Scott" wrote:

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?

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

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")

"Scott" wrote:

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?

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

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.
--
David Biddulph

"Scott" wrote in message
...
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?

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

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.


"David Biddulph" wrote:

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.
--
David Biddulph

"Scott" wrote in message
...
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?

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

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

--
David Biddulph

"Scott" wrote in message
...
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.


"David Biddulph" wrote:

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.
--
David Biddulph

"Scott" wrote in message
...
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?

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

"Scott" wrote:

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

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

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

"Jerry W. Lewis" wrote:

"Scott" wrote:

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

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

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.
--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Scott" wrote in message
...
Thanks guys. Fascinating. Us accountants cannot comprehend things that
do
not balance.

"Jerry W. Lewis" wrote:

"Scott" wrote:

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