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

Bill Lafferty
Bill Lafferty ✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

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.

Comments

  • Try to simply translate 90% into .9

  • Bill Lafferty
    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.

     

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

  • J. Craig Williams
    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
    Bill Lafferty ✭✭✭✭
    edited 12/03/16

    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
    J. Craig Williams ✭✭✭✭✭✭

    Glad I could help.

     

    Craig

This discussion has been closed.