Nested IF Formula Troubles

Tina Rustvold

We are trying to use a nested IF formula to use two different sets of criteria to return two different results. I feel we are close because we get an Incorrect Argument error, rather than the dreaded Unparsable, but I am not seeing what we are missing. We also tried adding a "" as a return if False value but that didn't have any impact.

=IF(Status@row, OR(@cell = "Active", @cell = "Pre-Go Live", @cell = "Closing", @cell = "Pipeline - Active"), "Active", IF(Status@row, OR(@cell = "Pipeline - 0 thru 2", @cell = "Pipeline - 3. Finalist", @cell = "Pipeline - 4. Partner of Choice", @cell = "Pipeline - 5. Selected"), "Forecast"))

We want to use this as a column formula in a Capacity Status column to show a project as Active or Forecast based on what is listed in our Status column. The Status column is a single select column so we shouldn't need HAS(@cell. Do we need to check our column properties too? Thank you!

Kelly Moore

Hey @Tina Rustvold

The syntax of your OR statements aren't what smartsheet is expecting

=IF(OR(Status@row = "Active", Status@row = "Pre-Go Live", Status@row = "Closing", Status@row = "Pipeline - Active"), "Active", IF( OR(Status@row= "Pipeline - 0 thru 2", Status@row= "Pipeline - 3. Finalist", Status@row = "Pipeline - 4. Partner of Choice", Status@row = "Pipeline - 5. Selected"), "Forecast"))

will this work for you?

Kelly

Kelly Moore

Hey @Tina Rustvold

The syntax of your OR statements aren't what smartsheet is expecting

=IF(OR(Status@row = "Active", Status@row = "Pre-Go Live", Status@row = "Closing", Status@row = "Pipeline - Active"), "Active", IF( OR(Status@row= "Pipeline - 0 thru 2", Status@row= "Pipeline - 3. Finalist", Status@row = "Pipeline - 4. Partner of Choice", Status@row = "Pipeline - 5. Selected"), "Forecast"))

will this work for you?

Kelly

heyjay

=IF(OR
Status@row = "Active", 
Status@row = "Pre-Go Live", 
Status@row = "Closing", 
Status@row = "Pipeline - Active"), 
"Active", 

IF(OR(
Status@row = "Pipeline - 0 thru 2", 
Status@row = "Pipeline - 3. Finalist", 
Status@row = "Pipeline - 4. Partner of Choice", 
Status@row = "Pipeline - 5. Selected"), 
"Forecast"))

Tina Rustvold

Thanks so much @Kelly Moore and @heyjay--that did the trick. I wondered if that was where the glitch was, but Friday brain power wasn't getting there.

Tina Rustvold

Since you all were such a help with this, I am coming back for how to add an additional IF. We only want something returned if it is for an active project so I tried adding a just an IF, then an IF(OR, then an IF(AND but still get an incorrect argument error. Additionally, I have tried IM Done = 0 and not included the "" and even puting this criteria a the beginning of the formula. Suggestions?

=IF(OR(Status@row = "Active", Status@row = "Pre-Go Live", Status@row = "Closing", Status@row = "Pipeline Active", "Active", IF(OR(Status@row = "Pipeline - 0 thru 2", Status@row = "Pipeline - 3. Finalist", Status@row = "Pipeline - 4. Partner of Choice", Status@row = "Pipeline - 5. Selected", IF(AND([IM Done]@row, 0, ""))))))

BTW the IM Done is a checkbox column--and Thank you!