EXCEL 2000 - IF function returns wrong result from logical test
 

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

EXCEL 2000 - IF function returns wrong result from logical test
 

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

--
Rick (MVP - Excel)


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

EXCEL 2000 - IF function returns wrong result from logical test
 

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.


--
John C


"Sean Duffy" wrote:

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

EXCEL 2000 - IF function returns wrong result from logical tes
 

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

"John C" wrote:

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.


--
John C


"Sean Duffy" wrote:

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

EXCEL 2000 - IF function returns wrong result from logical tes
 

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!!

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

EXCEL 2000 - IF function returns wrong result from logical tes
 

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

--
John C


"Sean Duffy" wrote:

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

"John C" wrote:

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.


--
John C


"Sean Duffy" wrote:

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

EXCEL 2000 - IF function returns wrong result from logical tes
 

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


"John C" wrote:

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

--
John C


"Sean Duffy" wrote:

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

"John C" wrote:

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.


--
John C


"Sean Duffy" wrote:

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

EXCEL 2000 - IF function returns wrong result from logical tes
 

You are welcome, and thanks for the feedback.
--
John C


"Sean Duffy" wrote:

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


"John C" wrote:

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

--
John C


"Sean Duffy" wrote:

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

"John C" wrote:

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.


--
John C


"Sean Duffy" wrote:

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

EXCEL 2000 - IF function returns wrong result from logical test
 

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.
--
Thanks,
Shane Devenshire


"Sean Duffy" wrote:

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