IF Statement Limitations?

Shawn R

I would like to write and IF formula in one column that involves 3 other columns. I'll do my best to explain what I am trying to do and the issue I am running in to.

The first column is called "Building" and this is where I am writing the IF formula. The second column is called "Work Center" and its a drop down list of 36 different possible values. The third column is called "C1" and is always populated with C1, the fourth column is called "C3" and is always populated with C3.

What I want to do is populate the "Building" column with C1 or C3 depending on which work center is chosen from the drop down list of the "Work Center" column. I started writing multiple IF formulas but it seems to have a limit on how many I can use. After about 7 it stopped working.

What I would like to do is write only two if formulas to tell the Building column to populate correctly. The best example of this I can give is this (IF work center = one of 7 different choices then populate Building with C1, if its the other set of 7 choices populate the building column with C3)

Like I said before I could accomplish this with 37 different IF statements, but there appears to be a limit. Is there a way to use the OR function within and IF statement to allow several different options to return the same value?

Paul Newcome

=IF(OR([Column Name]@row = "Criteria 1", [Column Name]@row = "Criteria 2", [Column Name]@row = "Criteria 3", etc, etc), "C1", IF(OR([Column Name]@row = "Criteria 10", [Column Name]@row = "Criteria 11", [Column Name]@row = "Criteria 12", etc, etc), "C3"))

 

Basically you need to break it down into small parts.

1. Open your IF statement and decide your logical statement: =IF(

2. Your logical statement opens with an OR statement: OR(

2.1. Input your OR criteria using commas to separate the different choices. Continue adding your OR criteria in this manner until you have each one listed for your first IF statement, then close your OR statement: [Column Name]@row = "Example 1", [Column Name]@row = "Example 2", [Column Name]@row = "Example 3"),

3. Now that you have specified the criteria for your IF statement, determine what the formula will populate if it is found to be true: "C1",

4. Decide what the formula will do if the statement is false. In this case you want it to look at a second set of criteria using another IF statement that has multiple choices that could make it true (OR). Simply repeat steps 1 - 3 (not including the "=" at the beginning) except update your criteria in step 2.1, and your true result in step 3. 

5. Because you have 2 IF statements open (both OR statements were closed after listing your criteria), you will need to finish it off with )) to close both of the IF statements are still open.

Step 5 is what I have noticed most people messing up on when writing complex formulas. The easiest way to ensure you have enough on the end is to count your ('s. Then count your )'s. You should have the same number of each. As long as the internal functions are written correctly, the difference should be what you put on the end.

I personally have found a lot of Smartsheet's limitations. One I have NOT found yet is the maximum count of IF statements in one formula. I had one that was 62 IF's long. The only reason it didn't work was because I had exceeded the limitation of 4,000 characters per cell.

Shawn R

Hey Paul,

 

Thanks for the very detailed response. I was able to resolve this a few hours ago using basically what you described. What I was doing wrong with the OR function was I wasn't separating it properly and trying to declare the OR variables without re-referencing the cell.

This is what my final formula looks like.

=IF(OR([Work Center]1 = "CUT3", [Work Center]1 = "F3CT", [Work Center]1 = "F3PR", [Work Center]1 = "F3PT", [Work Center]1 = "NTWD", [Work Center]1 = "SCM", [Work Center]1 = "SHO3", [Work Center]1 = "SHO4", [Work Center]1 = "SHP3", [Work Center]1 = "WRP3", [Work Center]1 = "WRP4", [Work Center]1 = "WRP5", [Work Center]1 = "WRP6"), [C3]3, [C1]3)

Again, I wasn't referencing the [Work Center]1 for each logical function. I also noticed that there is no limitations. I mistakenly thought that if the color coding quit working that the function quit working. That's not the case. I guess after 7 or so logical functions that it quits doing the color coding, but the function still works.

I also shortened it up by using the smaller list to populate the column with C3 and then just telling the formula to populate it as C1 if it were false. Rather than creating a larger function to specifically tell it to populate it as C1 with a secondary true statement. Made things quicker and the formula much smaller.

Thanks again!

Paul Newcome

Thanks for the very detailed response. I was able to resolve this a few hours ago using basically what you described. What I was doing wrong with the OR function was I wasn't separating it properly and trying to declare the OR variables without re-referencing the cell.

 

Happy to be of assistance, and great that you were able to figure out what was wrong.

 

 I mistakenly thought that if the color coding quit working that the function quit working. That's not the case. I guess after 7 or so logical functions that it quits doing the color coding, but the function still works.

 

I did the same thing the first few times I started trying to tackle more complex formulas.

 

I also shortened it up by using the smaller list to populate the column with C3 and then just telling the formula to populate it as C1 if it were false. 

 

Very good idea. I wish I had thought of that! Haha