My Simple Nested IF() is not Working

Jim Hook

I have done thousands of nested IF() functions much more complex than the example below but for some reason I cannot get this one to work. I'm using this formula in an archive sheet that determines what rows of the archive should appear in a report, just those that result in "Yes" in the Result column. Basically, if the value in column B is zero, I say "Yes" if the value in LastMo is equal to the value in CurMo. That works fine.

If the value in column B is non-zero, I want to get a result of "Yes" if the value in LastMo is greater than or equal to the value in B. When this condition is met I expect to get "Yes" but I end up getting a blank result.

The formula shown in row 7 in the Result column is identical to the formulas in rows 1 and 4 and again I expect it to result of "Yes". If none of the conditions in the nested IF() functions are met I should get "No" as a result.

Anyone see what I'm doing wrong? Where is the blank result coming from?

Jenna Bailey

Should it potentially be

=IF(B@row >= 0, IF(LastMo@row = CurMo@row, "Yes", IF(LastMo@row >= B@row, "Yes", "No")))

I am getting a value of "Yes" with that formula.

Jim Hook

Thanks Jenna! I had to change the logic conditions in your formula to get the logic the way I wanted and it worked. The working formula with the right conditions is shown below. There's something odd going on but at least it works if you reverse the logic from my earlier formula.

=IF(B@row >= 0, IF(LastMo@row >= CurMo@row, "Yes", IF(LastMo@row = B@row, "Yes", "No")))

Jim Hook

Jenna's formula and my reply to her both had errors in that the condition in the first IF function would always be true so the formula never got to the second IF function.

I think I have found out what's going on though I haven't been able to get confirmation from Smartsheet Support or other resources. I think that the condition if true value in an IF function cannot be another IF function. It can be a formula that calculates a result, just not an IF function. IF functions are just fine in the if the condition is false argument. I figured this out by entering the same formula in an Excel sheet and instead of blank results I got "FALSE".