Trying to create an IF THEN function in smartsheet

Horace

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

 

Andreas S.

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

 

Andreas S.

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

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

 

 

 

Andreas S.

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

Thank you!

 

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.