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

Phomo

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

Mike Wilday

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

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

=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

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

Mike Wilday

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

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

@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

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

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

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

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

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

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

=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

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

Mike Wilday