or Explore Discussions

# Multiple IF Statements

edited 12/09/19
07/30/18 Edited 12/09/19

Can we do multiple IF statements? I've tried to use IF and also OR to complete the function I'm trying to do and nothing seems to work. I want my Status column, "Not Started", "In Progress", and "Completed" to be based off of the % indicated in the % Completed column so I don't have to remember to change the status when the % completed changes. I know I've done this before in Excel but not sure Smartsheet can handle an IF statement with multiple conditions.

Here is the logic...

IF % Completed = 0%, then return "Not Started"

IF % Completed = 100%, then return "Completed"

IF % Completed is between 1 - 99, then return "In Progress" OR this could also be IF the first two logics are FALSE then return "In Progress".

Thank you,

Kim

Tags:
Previous1

• ✭✭✭✭✭
edited 07/30/18

You chain together additional conditions within the IF function.

=IF([% Complete]2 = 0, "Not Started", IF([% Complete]2 = 1, "Complete", "In Progress"))

In this case, I have placed a second IF function in the False result of the first IF function. NOTE: you can put IF's or other functions in an IF functions TRUE or FALSE returns.

• ✭✭✭✭✭

It's important to note a couple things. Percentages, though displayed with a % sign, are actually decimals in the backend. 1 = 100%, .5 = 50%, etc. etc.

So if you wanted to add an additional status based on a lesser percentage, then you would use the decimal. .5, .6, etc.

• Thank you this was very helpful, it was the second "IF" that I was missing.

• I'm getting an unparseable error and can't figure out what the issue is. Here is my formula:

=

IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up”,

IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down”,

IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged”, “Fix Me”)))

The test lines all worked and I placed it into a text editor and made the suggestions from the video, but it just says unparseable?

• @Mike Wilday any idea what I could be doing incorrectly? ^

• ✭✭✭✭✭

You have characters that are wrong. See below.

### ” “

=

IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up,

IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down

IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged, Fix Me)))

Here's the fixed one.

=

IF([Q3 2020 Velocity Trend]1 > [Working Velocity]1, "Up",

IF([Q3 2020 Velocity Trend]1 < [Working Velocity]1, "Down",

IF([Q3 2020 Velocity Trend]1 = [Working Velocity]1, "Unchanged", "Fix Me")))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET PARTNER & CONSULTANT / EXPERT

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

• It did work! It appears the " from the text editor is causing the issue. Thanks for helping me solve this :)

• ✭✭✭✭✭

You're more than welcome!

Yes, some text editors adds strange characters.

SMARTSHEET PARTNER & CONSULTANT / EXPERT

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

• ✭✭✭✭✭

@Elizabeth Jones Yeah, if you use a word processor to develop your formulas, they frequently add "Smart Quotes" in place of standard quotations. It's always best practice to construct your formulas in wordpad, notepad ++ or a similar simple text-editor that strips out all formatting.

• Can someone please tell me how to use multipe IF function using more than 1 Smartsheet? Thank you in advance.

• I am trying to use multiple IF statements and it is not working. For example, I have a column called "HCSA Goal Alignment" which has three conditions that a user can choose. The three conditions are:

·       Does not meet (Low priority = 5 points)

·       Partially meets (Medium priority = 10 points)

·       Completely meets (High priority = 15 points)

I have created another column called "Score" where I am trying to create a formula with multiple IF statements to calculate the result (5, 10, or 15) based on the above three inputs. Here is my formula:

·       =IF([HCSA Goal Alignment]@row = "Does not meet (Low priority =5 points)", "5", IF([HCSA Goal Alignment]@row = "Partially meets (Medium priority =10 points)", "10", IF([HCSA Goal Alignment]@row = "Completely meets = (High priority = 15points)", "15")))

The formula is structured correctly! However your quotes around the desired output (ex. "5") turn the numbers into text values.

Try tis:

=IF([HCSA Goal Alignment]@row = "Does not meet (Low priority =5 points)", 5, IF([HCSA Goal Alignment]@row = "Partially meets (Medium priority =10 points)", 10, IF([HCSA Goal Alignment]@row = "Completely meets = (High priority = 15points)", 15)))

Keep in mind the values it's looking for in your "HCSA Goal Alignment" column will need to be spelled exactly the same in your formula.

If this doesn't work, can you post a screen capture of the values in the drop-down list, and the error or incorrect result you're seeing?

Cheers,

Genevieve

• Hi, I am trying to nest two IF statements that reference other sheets. Each statement alone works:

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0)))

=IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))

But if I try to nest them like this I get an incorrect argument set error. What am I doing wrong?

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0, =IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))))

• Removed the extra = and I get the same error

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0, IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0)))))

• ✭✭✭✭✭

It looks like a few parentheses were out of place.

=IF([New Time Product]@row = "Dimensions", INDEX({WFD/Ulti Master Roll Up Range 2}, MATCH([OpenAir Project]@row, {WFD/Ulti Master Roll Up Range 3}, 0)), IF([New Time Product]@row = "Ready", INDEX({WFR Master Roll Up Range 2}, MATCH([Solution ID]@row, {WFR Master Roll Up Range 1}, 0))))

Does this work for you?