Trying to create an IF THEN function in smartsheet

Horace
Horace ✭✭✭
edited 12/09/19 in Formulas and Functions

My example is to select an material size value based on the material. Something like this:  

If [cell1]="4X8 Sheet" then [cell2]=32, if [cell1]="4X12 Sheet" then [cell2]=48, etc, etc. 

I have a dropdown list in the materials column to restrict the set of possible materials.

Subsequently, I would like to take those values to calculate productivity rates(averages) in another column using the values calculated for cell 2. Something like this:

If [cell2]="32" then [cell3]=32 * [cell4] / [cell5], if [cell2]="48" then [cell3]=48 * [cell4] / [cell5], etc. etc.

I tried using the LOOKUP formula using a lookup table from another help post, but it didn't work. I also tried a nested formula, but it didn't work either. Here's the post I'm referring to: https://community.smartsheet.com/discussion/i-want-utilise-if-then-function-smart-sheet

 

Comments

  • Hi Horace,

    If your material list is not to long it´s maybe easiest to enter something like this into [cell2]:

    =If([cell1]="4X8 Sheet";32; if([cell1]="4X12 Sheet";48; if(.....)))

    (the "32" is the "then" for the first if, everything after 32 is the "else" for the first if)

     

    Your [cell3] needs this:

    =[cell2] * [cell4] / [cell5]

     

    Hope this helps

    Andreas

     

  • just learned that non Germans (maybe not all?) have to use komma instead of semikolon, so if it doesn't work replace the Semikolon... 

  • Horace
    Horace ✭✭✭

    I had to try several things, but think I figured it out. A couple of corrections to the formulas you mention above. Would you please confirm that this is correct?

    1. Cell name does not need to be in [ ] brackets

    2. commas should replace the semi-colons in the above formula you mention...

    Is that right? 

    Here's what I used that seems to work: =IF(Material1 = "4X8 Drywall", 32, IF(Material1 = "4X9 Drywall", 36), if(....)))

    Thank you

     

     

     

  • That comma / semicolon thing is a nightmare, I have to use semicolon, if yours is happy with comma then that's what has to be in your region.

    The [ brackets should always be OK but not always needed.

    You will need one If for each of your products. 

     

  • Horace
    Horace ✭✭✭
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How many different materials do you have? There are a few other ways this could be set up to provide more efficiency and flexibility if you have a longer list of materials. My general rule of thumb is 5 or more get converted into a table instead of a nested IF statement.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!