Options
kadam56146 ✭
07/30/18 edited 12/09/19 in Formulas and Functions
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:
- Formulas
0 · Share on FacebookShare on Twitter
Lee Joramo ✭✭✭✭✭✭
07/30/18 edited 07/30/18 Answer ✓
Options
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.
2 · Share on FacebookShare on Twitter
Answers
Lee Joramo ✭✭✭✭✭✭
07/30/18 edited 07/30/18 Answer ✓
Options
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.
2 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
07/30/18
Options
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.
2 · Share on FacebookShare on Twitter
kadam56146 ✭
Options
Thank you this was very helpful, it was the second "IF" that I was missing.
0 · Share on FacebookShare on Twitter
Elizabeth Jones ✭✭
09/10/20
Options
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?
0 · Share on FacebookShare on Twitter
Elizabeth Jones ✭✭
09/10/20
Options
@Mike Wilday any idea what I could be doing incorrectly? ^
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
09/10/20
Options
Hi @Elizabeth Jones
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
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
1 · Share on FacebookShare on Twitter
Elizabeth Jones ✭✭
09/10/20
Options
It did work! It appears the " from the text editor is causing the issue. Thanks for helping me solve this :)
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
09/10/20
Options
@Elizabeth Jones
You're more than welcome!
Yes, some text editors adds strange characters.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
Mike Wilday ✭✭✭✭✭✭
09/14/20
Options
@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.
0 · Share on FacebookShare on Twitter
Hardik Ranpara ✭
02/18/21
Options
Can someone please tell me how to use multipe IF function using more than 1 Smartsheet? Thank you in advance.
0 · Share on FacebookShare on Twitter
Sharad Jain ✭✭
09/07/21
Options
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")))
0 · Share on FacebookShare on Twitter
Genevieve P. Employee Admin
09/08/21
Options
Hi @Sharad Jain
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
0 · Share on FacebookShare on Twitter
william.johnson ✭✭
11/05/21
Options
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)))))
0 · Share on FacebookShare on Twitter
william.johnson ✭✭
11/05/21
Options
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)))))
0 · Share on FacebookShare on Twitter
Kelly Moore ✭✭✭✭✭✭
11/08/21
Options
Hey @william.johnson
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?
0 · Share on FacebookShare on Twitter
«1234»
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59.4K Get Help
- 90 Global Discussions
- 94 Industry Talk
- 400 Announcements
- 12 Community Corner Newsletter
- 71 Brandfolder
- 121 Just for fun
- 46 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 251 Events
- 13 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
