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:
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:
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:
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:
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!