Nested - IF Then Else - Order of Operations

[laurasharvin]

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

[echuong1 · Accepted solution]

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 solution]

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 solution]

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. 

[laurasharvin]

Thanks Roland,

Both Solutions you have given work a treat. Your assumption with the logic is correct, and I will use the more simplified logic in my workflow.