I am rusty on IF Statements and I need help with building one with different conditions.
My if statement works with I use one condition but I am getting an error when I try to add more conditions.
Based on OJT % column that has a number between 0 and 100 I want the IF statement column to reflect:
"Beginning Apprentice" if the number is between 0-25
"Intermediate Apprentice" if the number is between 26-50
"Advanced Apprentice" if the number is between 51-74
"Eligible for Completion" if the number is between 75-100
Can any one tell me how to add the other 3 conditions to the simple IF statement I have, corectly?
=IF([WH OJT %]@row < 26, "Beginning Apprentice")
Best Answers
-
Hi there, I hope this helps. You have to nest the IF statements. Here's a shot at it. You'll see I close all of the IF statements with end parenthesis' after the final statement.
=IF([WH OJT %]@row < 26, "Beginning Apprentice", IF([WH OJT %]@row <= 50, "Intermediate Apprentice", IF([WH OJT %]@row < 75, "Advanced Apprentice", IF([WH OJT %]@row <= 100, "Eligible for completion"))))
-
Thank you so much Mike. I think this works. One question. Does it matter if you use <= or just <. I see that you used both and I wanted to know. Thank you so much again.😀
-
=IF([WH OJT %]@row < 26, "Beginning Apprentice", IF([WH OJT %]@row <= 50, "Intermediate Apprentice", IF([WH OJT %]@row < 75, "Advanced Apprentice", IF(AND([WH OJT %]@row <= 100, [WH RSI %]@row = 100), "Eligible for completion", IF([WH OJT %]@row <100, "Advanced Apprentice")))))
That one should do the trick!!! I put the one requiring col 2 at 100 first because the last one would fire for both types, now it will only fire for the ones that don't have column 2 at 100.
-
THANK YOU!!!! It works!!!! You are a genius 😀
Answers
-
Hi there, I hope this helps. You have to nest the IF statements. Here's a shot at it. You'll see I close all of the IF statements with end parenthesis' after the final statement.
=IF([WH OJT %]@row < 26, "Beginning Apprentice", IF([WH OJT %]@row <= 50, "Intermediate Apprentice", IF([WH OJT %]@row < 75, "Advanced Apprentice", IF([WH OJT %]@row <= 100, "Eligible for completion"))))
-
Thank you so much Mike. I think this works. One question. Does it matter if you use <= or just <. I see that you used both and I wanted to know. Thank you so much again.😀
-
@Mike Wilday Sorry to bother you again. Is it ok to add and AND to the statement the would reference another column that would only apply to the "Eligible for completion" condition? I am looking for the "Eligible for completion only if 75-100 and column B is 100.
-
Hi @Phomo there is no difference. <= includes the number you mention < includes anything smaller than the number you mention.
To add your AND statement is possible.
=IF([WH OJT %]@row < 26, "Beginning Apprentice", IF([WH OJT %]@row <= 50, "Intermediate Apprentice", IF([WH OJT %]@row < 75, "Advanced Apprentice", IF(AND([WH OJT %]@row <= 100, [Name of Column B]@row = 100), "Eligible for completion"))))
Just replace the [Name of Column B row with your actual column name.😀
-
Thanks! I replaced the name of the column but something must be off because I don't get an error but I don't get a response either. 😕
=IF([WH OJT %]@row < 26, "Beginning Apprentice", IF([WH OJT %]@row <= 50, "Intermediate Apprentice", IF([WH OJT %]@row < 75, "Advanced Apprentice", IF(AND([WH OJT %]@row <= 100, [WH RSI %]@row = 100), "Eligible for completion"))))
Any ideas?
-
Did the WH RSI % text change to color to show that it connected to data? A screenshot with sensitive data blurred out would be helpful too.
-
You don't know how much I appreciate this. It did change color. It's what is in blue. What if we make the AND statement apply to all the conditions?
-
Ah... I see the issue. Thanks for the screenshot... that helps... you get nothing because there are no conditions that match those columns...
With the added AND statement we are requiring that the WH OJT % be 75 or greater AND the WH RSI percentage to = 100.
In your example row, the RSI % is only 50.
What would you want it to say if Column B isn't 100, but the First column is over 75?
-
I would like it to say that the apprentice is only "Eligible for Completion" if the conditions WH OJT % is 75 or higher AND WH RSI % is 100 are met.
I guess, if both of the conditions are not met, we could have it reflect "Advanced Apprentice"
-
=IF([WH OJT %]@row < 26, "Beginning Apprentice", IF([WH OJT %]@row <= 50, "Intermediate Apprentice", IF([WH OJT %]@row < 75, "Advanced Apprentice", IF(AND([WH OJT %]@row <= 100, [WH RSI %]@row = 100), "Eligible for completion", IF([WH OJT %]@row <100, "Advanced Apprentice")))))
That one should do the trick!!! I put the one requiring col 2 at 100 first because the last one would fire for both types, now it will only fire for the ones that don't have column 2 at 100.
-
THANK YOU!!!! It works!!!! You are a genius 😀
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!