All right sir realistically your test may not be quite as simple and straightforward as the example
we just walk through.
In many cases you have tests that include a number of different criteria and a number of different potential
values.
When that's the case you're going to want to use nested if statements.
The good news is that Excel makes it really easy to do this and you can include multiple logical tests
within a single formula.
And if you know the IF formula syntax then you know how to use nested if statements.
So let's look at the example before we dive into it.
So basically we have another logical test here based on the temperature field in column B but instead
of just saying is the temperature less than 32 yes or no we're asking two questions we're saying is
it less than 40.
If not let's see if it's greater than 80.
If not return this otherwise value a false error.
So in this case we have three potential results as opposed to two.
And our first instance.
So basically the syntax should look familiar.
You start with your logical test B2 is less than 40.
Your value if true.
So in this case if your test is true if your temperature is less than 40 climate equals cold.
Now here's the catch when you get to the value if false peace Here's where you start your next F-test
.
And basically what that does is it tells Excel.
It logically work your way from left to right through this formula and continue until you either find
a test that evaluates as true or you get to the end.
Where are your values of false value lives.
So in this case it's saying all right is be to less than 40 yes or no.
If it is a call it cold and stop there if it's not we're going to move to our next test which is Is
there a temperature greater than 80 yes or no.
If it is cold climate hot and stop there.
Otherwise move on.
And in this case you know if you get to the end if you have a temperature between 40 and 80 55 for instance
both of these are going to evaluate false and yourselves going to return the value of false error or
the value of false value of mild.
So let's dive into Excel and get our hands dirty.
We're going to be working with Column J.
The climate column.
And basically I want to do exactly what I just described so I want to create a nested IF statement to
categorize climate as cold if the temperature is less than 40 as hot if the temperature is greater than
80 and mild otherwise.
So I'll start with my equal sign.
If I open the princess logical test just like before G-2 it's less than 40 value if true.
It's going to be cold.
Now value false rather than just saying hot or other here.
I'm going to start another if statement.
If the temperature is good and then 80 value if true in quotes because it's a text string that I want
to return and then I get to my final value of false.
So if I'm done incorporating all of my tests here and I'm ready to kind of populate my catch all value
a false message or value I'm just going to answer that here.
So it's going to be mild.
And now here's a really important note.
When I add the next parenthesis you'll see that it's orange and it matches with the orange parenthesis
which is part of my second if statement.
What that's telling me is that I'm not done with this formula.
I haven't closed out all of my parentheses.
So when I add the second you'll see that it will be old and black and it matches with the first parenthesis
at the beginning of my formula which tells me that I'm done.
And I can press enter.
So again I'm going to leave the references relative so that I want to populate this formula down.
My references will change from row to row.
So let's see if this worked.
Got a bunch of cold values.
Temp is below 40 in all those instances.
Here's a mild value 43.
That's correct.
And you can see that this is ordered by month where one is January so it may have to scroll a bit for
a start getting into the heart values.
So here's one in August.
Eighty five degrees equals hot 75 was mild.
So it looks like it's working properly.
There you go.
Basic example of using a nested IF statement to add a second criteria to my test