Formula to value cell based on 3 other cells

Mary Andersen

I'm struggling how to write a formula for a Job Status Column, which is based on whether or not 4 individual date columns have a value in them. If date Column 1 has a date, then the Job Status Column should automatically show as "25% Complete". If date Column 2 has a date, then the Job Status Column should automatically show as "50% Complete". If date Column 3 has a date, then the Job Status Column should automatically show as "75% Complete." If date Column 4 has a date, then the Job Status Column should automatically show as "100% Complete." I've been trying all sorts of IF, OR, and ISDATE functions but cannot seem to get anything to work.

Paul Newcome

Ah. Ok. Since the date columns are not next to each other, we can tweak the nested IF you have above...

=IF(ISDATE([Step 4 Date]@row), "100% Complete", IF(ISDATE([Step 3 Date]@row),"75% Complete", IF(ISDATE([Step 2 Date]@row),"50% Complete", IF(ISDATE([Step 1 Date]@row),"25% Complete"))))

Paul Newcome

Happy to help. 👍️

One way to remember it is that IF # 2 is the third argument in the first IF.

=IF(this is true, output this, otherwise that)

You would drop the next IF into the "otherwise that" portion keeping the parenthesis in place.

Here is something I do when building complex formulas that helps me keep parenthesis from getting too jumbled up. Put each portion in its own cell and use cell references to link them together. Once you have each portion working, you can replace the cell references with the formulas in those cells.

Example:

If "Primary@row" = 1, then I want to output "One", otherwise I want to say that if "Primary@row" = 2 then output "Two".

So in Column A I have

=IF(Primary@row = 1, "One")

In Column B I have

=IF(Primary@row = 2, "Two")

I know that if Primary@row does not equal 1, then I want to run the formula in Column B. So now I have:

=IF(Primary@row = 1, "One", [Column B]@row)

Then I can take the IF statement in Column B and drop it into the formula where it says "[Column B]@row".

=IF(Primary@row = 1, "One", [Column B]@row)

=IF(Primary@row = 1, "One", IF(Primary@row = 2, "Two"))

As you can see... When you nest IF statements like this, because the next one goes into the 3rd portion of the previous one, they all get closed out at the end. So if you have 3 nested IF statements, then they should all be left open until you finish it off with 3 closing parenthesis.

Paul Newcome

Try this...

=COUNTIFS([Column 1]@row:[Column 4]@row, @cell <> "") / 4

Mary Andersen

I should have clarified that my date columns are not next to each other. Here's what I've been trying to work with but keep getting a #UNPARSEABLE error. Note that I have ordered the formula from end to start as I understand that Smartsheet will read/apply the formula from left to right. Example: if the order has a date at Step 3 I want the field to be valued "75% Complete"; however, if the order has a date only at Step 1 I want the field to be valued "25% Complete".

=IF((ISDATE([Step 4 Date]@row)), "100% Complete")), IF((ISDATE([Step 3 Date]@row)),"75% Complete")), IF((ISDATE([Step 2 Date]@row)),"50% Complete")), IF((ISDATE([Step 1 Date]@row)),"25% Complete"))

Paul Newcome

Ah. Ok. Since the date columns are not next to each other, we can tweak the nested IF you have above...

=IF(ISDATE([Step 4 Date]@row), "100% Complete", IF(ISDATE([Step 3 Date]@row),"75% Complete", IF(ISDATE([Step 2 Date]@row),"50% Complete", IF(ISDATE([Step 1 Date]@row),"25% Complete"))))

Mary Andersen

Paul, I wanted to say thank you for the correction to my nested IF formula (darn parenthesis placement always confound me in formulas). It worked perfectly.

Paul Newcome

Happy to help. 👍️

One way to remember it is that IF # 2 is the third argument in the first IF.

=IF(this is true, output this, otherwise that)

You would drop the next IF into the "otherwise that" portion keeping the parenthesis in place.

Here is something I do when building complex formulas that helps me keep parenthesis from getting too jumbled up. Put each portion in its own cell and use cell references to link them together. Once you have each portion working, you can replace the cell references with the formulas in those cells.

Example:

If "Primary@row" = 1, then I want to output "One", otherwise I want to say that if "Primary@row" = 2 then output "Two".

So in Column A I have

=IF(Primary@row = 1, "One")

In Column B I have

=IF(Primary@row = 2, "Two")

I know that if Primary@row does not equal 1, then I want to run the formula in Column B. So now I have:

=IF(Primary@row = 1, "One", [Column B]@row)

Then I can take the IF statement in Column B and drop it into the formula where it says "[Column B]@row".

=IF(Primary@row = 1, "One", [Column B]@row)

=IF(Primary@row = 1, "One", IF(Primary@row = 2, "Two"))

As you can see... When you nest IF statements like this, because the next one goes into the 3rd portion of the previous one, they all get closed out at the end. So if you have 3 nested IF statements, then they should all be left open until you finish it off with 3 closing parenthesis.