IF function not working correctly with calculated value

[starryeyed223]

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):

BU	BV	BW
GUARANTEE	FOUND	RESULT
10	9.6	SUSTAINED =IF(ABS(BV8-BU8)<=((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(BV9-BU9)<=((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(BV13-BU13) =ABS(9.6-10)	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(BV14-BU14) =ABS(20.3-20)	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!

 

[Toadstool]

Hi Starryeyed223,

You'll need to round the calculations to avoid the challenge that in the intermediary calculation 0.300000000000001 is not <= 0.300000000000000

=IF(ROUND(ABS(BV9-BU9),2)<=ROUND(((20/BU9+2)/10),2), "SUSTAINED", "VIOLATION")

 

[padapinto]

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):

BU	BV	BW
GUARANTEE	FOUND	RESULT
10	9.6	SUSTAINED =IF(ABS(BV8-BU8)<=((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(BV9-BU9)<=((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(BV13-BU13) =ABS(9.6-10)	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(BV14-BU14) =ABS(20.3-20)	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!

=IF(ABS(BV8-BU8)<=((20/BU8)+(2/10), "SUSTAINED", "VIOLATION") try this. structuring of the additional and subtraction could be the issue

 

[starryeyed223]

Hi Starryeyed223,

You'll need to round the calculations to avoid the challenge that in the intermediary calculation 0.300000000000001 is not <= 0.300000000000000

=IF(ROUND(ABS(BV9-BU9),2)<=ROUND(((20/BU9+2)/10),2), "SUSTAINED", "VIOLATION")

That works, thank you!

 

[starryeyed223]

=IF(ABS(BV8-BU8)<=((20/BU8)+(2/10), "SUSTAINED", "VIOLATION") try this. structuring of the additional and subtraction could be the issue

So that would change the order of the calculation and make the result incorrect - it has to be in this order (((20/BU8)+2)/10). Incorporating rounding fixed the issue, but thanks for your help!