I am rusty on IF Statements and I need help with building one with different conditions.

Phomo
Phomo ✭✭
edited 03/31/23 in Formulas and Functions

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    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"))))

  • Phomo
    Phomo ✭✭
    Answer ✓

    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
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    =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.

  • Phomo
    Phomo ✭✭
    Answer ✓

    THANK YOU!!!! It works!!!! You are a genius 😀

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    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"))))

  • Phomo
    Phomo ✭✭
    Answer ✓

    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.😀

  • Phomo
    Phomo ✭✭

    @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.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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.😀

  • Phomo
    Phomo ✭✭

    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?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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.

  • Phomo
    Phomo ✭✭

    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?


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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?

  • Phomo
    Phomo ✭✭
    edited 04/03/23

    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"

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    =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.

  • Phomo
    Phomo ✭✭
    Answer ✓

    THANK YOU!!!! It works!!!! You are a genius 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!