Formula trouble with IF or IFS

Bill Lafferty

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?

 

Thanks in advance.

Charles DESCOTES

Try to simply translate 90% into .9

Bill Lafferty

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.

 

Charles DESCOTES

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

J. Craig Williams

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

 

Bill Lafferty

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

J. Craig Williams

Glad I could help.

 

Craig