#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formula trouble with IF or IFS

Options
✭✭✭✭
edited 12/09/19

I have a sheet being used as a project schedule in which I want to auto-indicate the project lifecycle stage in a cell on the sheet's meta-data line.  To do so, I envisioned using an IF AND statement or something similar.  Below is a mock up of the way it might look in excel.

Project Lifecycle Phase = XXXXXX

COLUMN

B                          C

Row     % Complete        Project LifeCycle Phase

8               0%                 01 - Initiation

9               0%                 02 - Discovery

10              0%                 03 - Planning

11               0%                 04 - Execution

12              0%                 05 - Monitor & Control

13              0%                 06 - Close down

The formula I built but failed on is noted below.

=IF(AND(B8>=90%,B9>=90%),"03 - Planning",IF(AND(B9>=90%,B10>=90%),"04 - Execution",IF(AND(B10>=90%,B11>=90%),"05 - Monitor & Control",IF(AND(B11>=90%,B12>=75%),"06 - Close Down","01 - Initiation"))))

I was successful writing a formula (see below) n excel however cannot replicate in SmartSheet where I get a #UNPARSEABLE error.

=IFS(B8<100%,"01",IFS(B9>0%,B9>100%),"02",IFS(B10>0%,B10<100%),"03",IFS(B11>0%,B11<100%),"04",IFS(B12>0%,B12<100%),"05",IFS(B12>0%,B11<=100%),"06")

Does anyone know a way to do this with a formula in SmartSheet?

• Options

Try to simply translate 90% into .9

• ✭✭✭✭
Options

Charles - the percentage values are coming from a percent complete roll-up in a gantt chart. Let me see if I can add a calculated column to convert those values to a decimal and try as you suggest.

• Options

Or multiply by 1 maybe, sometimes (not sure in SmSh) it does make sure figures are actal figures ready for calculations.

• ✭✭✭✭✭✭
Options

William,

Smartsheet does not have an IFS function - instead you must build the nested if yourself.

The columns have names, not letters or numbers like in Excel, so unless you named your columns B and C (psst - don't do that please), you'll reference cells like this

[columnname]rownumber

The [] is required when you end the column name with a number, have a space, or in some cases a special character. I always use them and let Smartsheet remove them if it doesn't need them (it will do that automatically)

If I'm reading between the lines correctly, your 6 phases are likely not consecutive in the sheet - but maybe headers for sections of the sheet and you want to look at them to determine what phase you are in? The formula goes in one location.

This is how I build a nest:

If Initiation and Discovery are both more than 90% complete, then we are in Planning, otherwise ... we aren't.

=IF(AND([% Complete]8 > 0.9, [% Complete]9 > 0.9), "03 - Planning", "other")

If Discovery and Planning are both more than 90% complete, then we are in Execution, otherwise ... we aren't

I would replace "other" (including the quote) with

IF(AND([% Complete]9 > 0.9, [% Complete]10 > 0.9), "04 - Planning", "other")

or

=IF(AND([% Complete]8 > 0.9, [% Complete]9 > 0.9), "03 - Planning",

IF(AND([% Complete]9 > 0.9, [% Complete]10 > 0.9), "04 - Planning", "other"))

and so on.

Now, what you may find at the end is that if

Initiation is 50% done and Discovery & Planning are 95% done, you'll be in Execution.

That may be correct or not, depending on how you think about it.

Rearrange the order of the nest and you can get what you are looking for.

Hope that helps.

Craig

• ✭✭✭✭
edited 12/03/16
Options

Craig - I think we have a winner!  Here is what I used.

=IF(AND([% Complete]398 > 0.8, [% Complete]431 > 0), "06 - CLOSEDOWN", IF(AND([% Complete]188 > 0.8, [% Complete]398 > 0), "05 - MONITORING & CONTROL", IF(AND([% Complete]82 > 0.8, [% Complete]188 > 0), "04 - EXECUTION", IF(AND([% Complete]53 > 0.8, [% Complete]82 > 0), "03 - PLANNING", IF(AND([% Complete]39 > 0.8, [% Complete]53 > 0), "02 - DISCOVERY", "01 - INITIATION")))))

It seems to be operating as hoped.  Thanks for the tips.  Through some trial and error I was able to get this to work.  Thanks.

Bill

• ✭✭✭✭✭✭
Options