Formula behaves differently

[mtpsuresh]

Hi,

I have a peculiar problem. I display TRUE or FALSE in a cell and is
based on the below formula.
=(IF(ISNUMBER($B66),($AK66=($P66-$V66+$AA66+$AB66-$AF66)),""))

But the value displayed is FALSE. The value in AK66 is equal to 650.38
and the sum of ($P66-$V66+$AA66+$AB66-$AF66) is also equal to 650.38.

The same formula is copied to the entire column and it shows TRUE for
other cells and is as expected. I tried with several values.

Can anybody help me out pls.

 

[aidan.heritage]

Excel stores it's numbers to a 15 Decimal Place precision, so it's
probably an internal rounding problem - if you force excel to round by
using the round function, you will resolve this

=round(ak66,2)=round(etc etc etc,2)

 

[Guest]

Hi

Your formula looks a bit odd! It is saying that if B66 is a number, then
$AK66=($P66-$V66+$AA66+$AB66-$AF66).
Eh?
Are you trying to compare AK66 to $P66-$V66+$AA66+$AB66-$AF66? See if this
does what you want:
=IF(AND(ISNUMBER($B66),$AK66=$P66-$V66+$AA66+$AB66-$AF66),"Yes","No")
This is untested - and might not be what you want anyway!

Andy.

 

[Guest]

I know of no documented instance of basic arithmetic errors in Excel.

Excel and almost all other computer software does binary arithmetic. Most
terminating decimal fractions (including 650.38) are non-terminating binary
fractions that can only be approximated (just as 1/3 can only be approximated
as a decimal fraction).

Likely your calculation simply resulted in a different binary approximation
to 650.38 due to the binary approximations to the initial inputs. With
addition/subtraction, you can simply round the final result to smallest
decimal place of any of the inputs without doing violence to the calculation.

My D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
will allow you to more closely examine the approximations involved, if you
so desire.

Jerry

 

[mtpsuresh]

Thanks for your replies.

The problem was in the decimal fractions as all of you have pointed
out. I've corrected it.

Once again thanks for all your valuable time and effort for me.

Regards
Suresh