Nested - IF Then Else - Order of Operations

Question

I am trying to figure out the order of operations for a nested If Then Else statement, as my code is returning the incorrect answer. I have attached the workbook / example of what I am trying to achieve, but the detract_from_Savings is taking Savings Column 2 instead of Savings Column 1 ($20) - It should perform the If statement in bold, however it hits the statement before that and returns answer based on that. Any help greatly appreciated. If Contains([Detract_Savings_5], "Yes") and [Dollars] < [Savings_5] then [Dollars] elseif [Dollars] >= [Savings_5] then [Savings_5] Else ( If Contains([Detract_Savings_5], 'No') and [Detract_Savings_4] = "Yes" and [Dollars] < [Savings_4] then [Dollars] elseif [Dollars] >= [Savings_4] then [Savings_4] Else ( If Contains([Detract_Savings_5], 'No') and [Detract_Savings_4] = "No" and [Detract_Savings_3] = "Yes" and [Dollars] < [Savings_3] then [Dollars] elseif [Dollars] >= [Savings_3] then [Savings_3] Else ( If Contains([Detract_Savings_5], 'No') and [Detract_Savings_4] = "No" and [Detract_Savings_3] = "No" and [Detract_Savings_2] = "Yes" and [Dollars] < [Savings_2] then [Dollars] elseif [Dollars] >= [Savings_2] then [Savings_2] Else ( If Contains([Detract_Savings_5], 'No') and [Detract_Savings_4] = "No" and [Detract_Savings_3] = "No" and [Detract_Savings_2] = "No" and [Detract_Savings_1] = "Yes" and [Dollars] < [Savings_1] then [Dollars] elseif [Dollars] >= [Savings_1] then [Savings_1] Else 0 Endif) EndIf) EndIF) endif) endif NestedIF_Then_OrderOfOperations.yxmd

echuong1 · Accepted Answer

To start, some of your savings fields contain null values. This will cause incorrect comparisons.

I replaced the nulls with 0 values and followed the logic written in the formula tool and the end result is 0, which matches the workflow result. Can you provide clarification on the criteria you'd like to use? The formula needs to be adjusted.

I believe the attached workflow achieves your purpose, but I want to check with you to ensure it's calculating correctly.

RolandSchubert · Accepted Answer

Hi @laurasharvin ,

I've modified the statement a bit - if I understand the logic, it has to be checked, which one of the "Detract_Savings_n" is "Yes" (starting with highest number, first counts). If the related "Savings_n" is <= Dollars, then Dollars, else Savings_n. The attached fie contains two different conditional constructions, the second one is even more "simplified". Hope this is helpful.

Best

Roland

laurasharvin · Accepted Answer

Thank you, the updated nested if statement does solve the issue and thanks for tip on Nulls, I have cleaned this up in the main workflow.