starryeyed223
New Member
 Joined
 Sep 30, 2020
 Messages
 7
 Office Version

 365
 Platform

 Windows
So I've run into a strange issue with using IF to compare whether one calculated value is less than or equal to another. The function works correctly in most situations, but in some cases it's incorrectly showing that the second calculated value is greater than the first when they are actually equal.
Here is how I had my spreadsheet set up (formulas/notes in green text):
I don't understand why this formula would work correctly in some situations but not others. To test what could be going on, I split the calculation out into more steps  this made it work correctly.
I would really prefer to have my calculations all done in one column instead of adding two additional columns. What could be causing this issue? I hoped using "Set precision as displayed" would solve the issue, but it did not. Any guidance would be much appreciated!
Here is how I had my spreadsheet set up (formulas/notes in green text):
BU  BV  BW 
GUARANTEE  FOUND  RESULT 
10  9.6  SUSTAINED =IF(ABS(BV8BU8)<=((20/BU8+2)/10), "SUSTAINED", "VIOLATION") =IF(ABS(0.4)<=(0.4), "SUSTAINED", "VIOLATION") The SUSTAINED result is correct 
20  20.3  VIOLATION =IF(ABS(BV9BU9)<=((20/BU9+2)/10), "SUSTAINED", "VIOLATION") =IF(ABS(0.3)<=(0.3), "SUSTAINED", "VIOLATION") The VIOLATION result is incorrect 
I don't understand why this formula would work correctly in some situations but not others. To test what could be going on, I split the calculation out into more steps  this made it work correctly.
BU  BV  BW  BX  BY 
GUARANTEE  FOUND  ABS VAL FOUND  GUARANTEE  AV%  RESULT 
10  9.6  0.4 =ABS(BV13BU13) =ABS(9.610)  0.4 =(20/BU13+2)/10 =(20/10+2)/10  SUSTAINED =IF(BW13<=BX13 ,"SUSTAINED", "VIOLATION") =IF(0.4)<=(0.4), "SUSTAINED", "VIOLATION") The SUSTAINED result is correct 
20  20.3  0.3 =ABS(BV14BU14) =ABS(20.320)  0.3 =(20/BU14+2)/10 =(20/20+2)/10  SUSTAINED =IF(BW14<=BX14 ,"SUSTAINED", "VIOLATION") =IF(0.3)<=(0.3), "SUSTAINED", "VIOLATION") This is now correctly showing as SUSTAINED 
I would really prefer to have my calculations all done in one column instead of adding two additional columns. What could be causing this issue? I hoped using "Set precision as displayed" would solve the issue, but it did not. Any guidance would be much appreciated!