Troubleshooting A Nested If Statement That Includes The Or function

Excel function tutorials

I have to say that I’m thrilled because we are receiving quite a few questions these days both in comments and by email. As always, we are more than happy to help and in most cases, the answer can help quite a few others so we publish the answer as a new post. We’d be very happy to receive questions from you so be sure to contact us with any problems that you have. Today, we received the following question, which initially appeared as a comment on our excel nested if statements tutorial. I’ve translated the question to make it easier to understand here:

I am just trying to get a Y or N when applying this formula:
=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”))
Below are a couple of sets of data and the results that I got when the formula was applied:

As you can see, once the data does not meet the first part of the formula, it gives you a FALSE. If it meets the first part but does not meet the second, it gives you an N. If it meets both parts, it gives you a Y. I only want the formula to produce 2 results, Y or N. Let me know if you have any further questions. Otherwise, let me know if you can help me solve this problem.

There are a few different things to discuss here. First off, let’s translate this into an easier question to understand. Let’s imagine the question is. I want to determine small or large schools that have very poor or very good results. A small school would have less than 450 students, a large one over 550, and very poor results are under 50% while a very good one is over 70%.

So what am I looking for?

Schools that have under 450 or over 550 students that have a score under 50% or over 70%

The first thing that I notice in the statement is that it is incomplete:

=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”))

This statement initially does one thing. It checks if U2 is greater or equal to 500 or smaller or equal (of course, any number will fit this condition). If that condition is met, it checks for the 2nd part, which is similar. The problem of course is that it does not give any indication for what happens if the first condition is not met. I would thus start by changing the statement to:

=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”),”N”)

This fixes the problem of getting a “False”, see the new result:

In order to add the conditions that I discussed, I would change it to:

=IF(OR(U2>550,U2<450),IF(OR(V2>70%,V2<=50%),”Y”,”N”),”N”)

By changing the set, you can see the result:

Hopefully this helps. It’s important to always structure your arguments to not miss any parts. Doing one condition at a time is a great way to do get it done. We look forward to getting more questions from you!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

5 Feedbacks on "Troubleshooting A Nested If Statement That Includes The Or function"

sunil

v good



Mike in accounting

I’m trying to get a cell to respond with either a “0” or a “4”.

There’s a 12 hour guarantee for any time worked on one day, so if the total hours worked for the day equals “0” hours, I need the target cell to read “0”, but if any hours above “0” are worked, I need a “4” to appear in the target cell. It sounds so simple, but it’s a multiple condition, and I simply can’t decode the rules. Please help!



Mike in accounting

I think I just did it, but not sure… how’s this look?

=OR(IF(I17<12,4,0))*(IF(I17=0,0,4))



Mike in accounting

I think I figured it out…

=IF(I12>0,4,0)

I was an English major so I’m slow. Thanks for being here!



Marcia

I was wondering how to write the IF_OR or IF_AND statement to capture only those ee’s who might be eligible to retire without an “early retirement discount”. My nested statement is:

=IF(OR(B23=54,B23=55,B23=56,B23=57,B23=58,B23=59,B23=60,B23=61,B23>=62),IF(OR(C23>=30,C23>=28,C23>=26,C23>=24,C23>=22,C23>=20,C23>=20,C23>=20,C23>=5),C23,0),0)

But this statement is bringing back as a positive an employee who is 56 yrs old and has 7 years of service. Or someone who is 58 with only 13 years of service.

How do I write the statement to properly capture only those ee’s who can retire without penalty please?

SRVC YRS IF
EE AGE SRVC YRS NO PENALTY
Brad 49.00 10.00 0
Brent 52.00 30.00 0
Cyndy 57.00 16.00 16
Daniel 40.00 10.00 0
Daron 38.00 8.00 0
Dirks 59.00 25.00 25
Domc 49.00 6.00 0
Dick 60.00 29.00 29
Ernie 67.00 13.00 13
Gabe 58.00 13.00 13
Greg 56.00 24.00 24
Grieg 50.00 20.00 0
Jim 44.00 1.00 0
John 56.00 7.00 7
Jose 29.00 1.00 0
Kent 49.00 11.00 0
Kev 58.00 27.00 27
Mark 58.00 20.00 20
Nali 45.00 6.00 0
Otto 56.00 22.00 22
Paul 42.00 12.00 0
Peter 53.00 12.00 0
Pong 32.00 8.00 0
Rich 62.00 16.00 16
Ricky 55.00 8.00 8
Rob 58.00 27.00 27
Robbi 45.00 11.00 0
Roni 34.00 12.00 0
Ronni 60.00 31.00 31
Steph 56.00 8.00 8
Steve 47.00 16.00 0
Sue 64.00 6.00 6
Tim 61.00 26.00 26
Vic 46.00 13.00 0
Will 61.00 17.00 17

Our agency has a multiple descending year retirement formula, where, starting at 54, and going thru 62, you need fewer years of service to be eligible to retire at the full percentage (years x 2.6%) without a “discount” for early retirement.

The formula is:
TO RETIRE W/OUT “DISCOUNT”:
AGE SRVC YRS
54.0 30
54.5 29
55.0 28
55.5 27
56.0 26
56.5 25
57.0 24
57.5 23
58.0 22
58.5 21
59.0 20
59.5 20
60.0 20
60.5 20
61.0 20
61.5 20
62.0 5
>62 5