# IF function not working correctly with calculated value

#### starryeyed223

##### New Member
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!

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

##### Well-known Member
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")`

##### New Member
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

##### New Member
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

##### New Member
=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!

Replies
2
Views
109
Replies
0
Views
396
Replies
0
Views
82
Replies
1
Views
85
Replies
3
Views
2K