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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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")
 
Upvote 0
Solution
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
 
Upvote 0
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!
 
Upvote 0
=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!
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top