IF function not working correctly with calculated value

starryeyed223

New Member
Joined
Sep 30, 2020
Messages
7
Office Version
  1. 365
Platform
  1. 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):

BUBVBW
GUARANTEEFOUNDRESULT
109.6SUSTAINED
=IF(ABS(BV8-BU8)<=((20/BU8+2)/10), "SUSTAINED", "VIOLATION")
=IF(ABS(0.4)<=(0.4), "SUSTAINED", "VIOLATION")
The SUSTAINED result is correct
2020.3VIOLATION
=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.

BUBVBWBXBY
GUARANTEEFOUNDABS VAL FOUND - GUARANTEEAV%RESULT
109.60.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
2020.30.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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,137
Office Version
  1. 2016
Platform
  1. Windows
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")
 
Solution

padapinto

New Member
Joined
Sep 21, 2020
Messages
42
Office Version
  1. 2019
  2. 2016
Platform
  1. 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):

BUBVBW
GUARANTEEFOUNDRESULT
109.6SUSTAINED
=IF(ABS(BV8-BU8)<=((20/BU8+2)/10), "SUSTAINED", "VIOLATION")
=IF(ABS(0.4)<=(0.4), "SUSTAINED", "VIOLATION")
The SUSTAINED result is correct
2020.3VIOLATION
=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.

BUBVBWBXBY
GUARANTEEFOUNDABS VAL FOUND - GUARANTEEAV%RESULT
109.60.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
2020.30.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
Joined
Sep 30, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 30, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,074
Messages
5,570,045
Members
412,308
Latest member
cornelb
Top