MATH IN AN "IF" FORMULA

F

FLOYD III

When I use an IF statement such as =IF((A1*1.5)-B2=0, "OK", "CHECK VALUE") ---- It works fine if the value is a whole number or has a 0.5 decimal. All other conditionals with decimal values exibit the same result. If I calculate the value into cell B2, it works fine. What did I miss here???

This is a pain as our specification is that the value of X must be 1.5 times that of Y --- which has lots of decimal values. Tried times 10 and other tricks to no avail.

Anyone got a cure or direction about what I may have over looked?
 
B

Bob Phillips

Would this do what you want

=IF(ROUND((A1*1.5)-B2,1)=0, "OK", "CHECK VALUE")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

FLOYD III said:
When I use an IF statement such as =IF((A1*1.5)-B2=0, "OK", "CHECK
Click to expand...
VALUE") ---- It works fine if the value is a whole number or has a 0.5
decimal. All other conditionals with decimal values exibit the same
result. If I calculate the value into cell B2, it works fine. What did I
miss here????
This is a pain as our specification is that the value of X must be 1.5
Click to expand...
times that of Y --- which has lots of decimal values. Tried times 10 and
other tricks to no avail.
 
J

Jerry W. Lewis

You already have the workarounds, here is the explanation:

Excel (and almost all other computer software) follows the IEEE
specification for double precision binary representation of numbers.
Most numbers with fractional parts cannot be represented exactly in
binary, even if they can be represented exactly in decimal (much as 1/3
has no exact decimal representation). Since you necessarily are working
with approximations to your conceptial numbers, you have to be prepared
for the possibility that you may only have approximate equality.

If I understand you correctly, for some inputs
=(A1*1.5)-B2
returns zero but
=IF((A1*1.5)-B2=0, "OK", "CHECK VALUE")
returns "CHECK VALUE". That is because MS programmers were trying to be
helpful in a way that has probably increased rather than decreased
confusion. Instead, look at
=((A1*1.5)-B2)
the formulas are mathematically the same but there is a practical
difference. IF A1*1.5 equals B2 to 15 figures, then
=(A1*1.5)-B2
will return zero, while
=((A1*1.5)-B2)
will return the actual difference between the two binary
representations. IF() results are based on the latter.

Jerry
 
Top