Forum Discussion
Excel ignoring part of formula
=IF(F3="","",IF(F3-G3=0.01,1,IF(F3>G3,3,IF(F3=G3,1,IF(F3<G3,0)))))
My cells F3 & G3 refer to a match score, with the Result showing (in Cell AM3) being 3pts for a win, 1 point for a Draw, and 0 for losing.
Therefore if F3 and G3 are equal (45-45) it does show 1 in AM3
Due to the vagaries of our sport, I also want a score of 45.01- 45 to show as a draw an AM3, hence my formula attempt in AM3:-
=IF(F3="","",IF(F3-G3=0.01,1,IF(F3>G3,3,IF(F3=G3,1,IF(F3<G3,0))))) but AM3 shows the result as a Win (3pts) as if it is ignoring my initial F3 statement F3-G3=0.01.
Can anybody help?
Thanks
Excellent! Thanks! After many attempts to solve it myself, <= makes the difference. Will also try the <=0.011 to see if that helps.
Thanks once again!😁👍
3 Replies
Hello,
See
Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps | Microsoft Learn
A rounding function must be used for F3-G3
You want to use <=0.01 not =0.01 but if that 0.01 could be in either direction (45.01-45 OR 45-45.01) then you want ABS(F3-G3)<=0.01 and then you also don't need that additional F3=G3 statement.
You could also benefit from using IFS:
=IFS(F3="","",ABS(F3-G3)<=0.01,1,F3>G3,3,F3<G3,0)
by the way, the reason =0.01 probably didn't work is probably due to round off issues so although it shows as 0.01 that actual value may be 0.00999999999999 due to how decimals have to be represented. In fact you might need to use <=0.011 just to be sure in case the roundoff is the other direction and it is 0.0100000000001 (BTW the number of decimal places I am showing was random and not adjusted to that actual resolution that excel stores)
Excellent! Thanks! After many attempts to solve it myself, <= makes the difference. Will also try the <=0.011 to see if that helps.
Thanks once again!😁👍
