My Simple Nested IF() is not Working

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?

Screen Shot 2020-10-19 at 6.33.03 AM.png

Find more posts tagged with

Comments

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".

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.