So by this point you're probably a master of the nested if statements.
Which is fantastic.
The thing is those statements were still based on pretty simple.
If This Then That statements.
What if we need to create a logical test that's based on a number of different fields or variables as
opposed to just temperature for instance.
One great example of this is if we wanted to create a field called precipitation type and precipitation
type can take a few different values.
Let's say we want to categorize it as snow rain or none.
If you just think about it for a minute there's no way to define snow rain or non based on just a single
field because it's a function of two things that temperature and the amount of precipitation.
So if nothing's falling out of the sky precipitation type will always be none.
If precipitation is greater than zero meaning something is coming out of the sky we need to know if
the temperature is above or below freezing.
To understand whether that is rain coming down or snow coming down.
So that's where the and and or statements come into play.
It's when you need to include multiple logical tests that are based on a number of different criteria
or variables.
So just continuing on with the precipitation type example you'll see that the syntax is a little bit
funky.
It can be a little tricky to get used to from a readability standpoint.
It might seem to make more sense to say if Condition 1 and condition 2 then in this case Excel kind
of flips it and it always starts with if and or if or or if not and then lists your criteria within
parentheses.
So reading through this example here this end statement basically what we're saying is logical test
one is if these two things are true.
So if and D2 equals Yes which that's are freezing column which tells me that the temperature is freezing
it's below 32 degrees and C 2 is greater than zero.
So precipitation is greater than zero then precipitation type equals snow.
Now if you remember the nested if syntax instead of the value of false move right on into our second
logical test which is another if and statement this case we're saying F and D two equals no.
So it's not freezing temperatures above 32 degrees and also precipitation is greater than zero then
precipitation type equals rain then you're always going to wrap it up at the end with your catch all
value with false value.
In this case it's just going to be none.
So in order for this test to be true both of these conditions need to be true because it's an and statement
.
And then if none of these are true it's going to return the final value which is none.
And now what if we wanted to create an additional field called conditions that are either equal to wet
or dry and the conditions are just based on precipitation.
So if precipitation is none.
We know that the conditions are going to be dry no matter what.
If the precipitation type is rain conditions are wet but if the precipitation type is snow the conditions
will also be wet.
So we could say if precipitation type is rain then wet if precipitation type snow than wet or otherwise
dry and do it like a traditional nested IF.
Or we can get a little bit more elegant and introduce an OR statement to do the same thing.
So in this or statement we're saying if or when listing our two conditions basically this is saying
if either one of these is true then or we're going to return the value of truth which is wet.
So if the precipitation type equals rain or it equals snow then conditions are wet.
And then again your catchall value of false is going to be dry.
So turn those two values.
Quick tip pier when you're writing nested functions especially if you have a number of different logical
tests all stacked together you can just copy and paste pieces of your formula to save yourself some
time.
So rather than rewriting if and and if.
Or statements from scratch just copy and paste and build your formulas that way.
So let's bump over to excel and actually walk through these examples hands on.
So again I mean use the exact same conditions that I just laid out.
So precipitation type is going to be the and statement that's based on freezing and precipitation so
columns I and H start with equals f parenthesis and parenthesis and then list my two logicals that must
both be true.
So freezing equals.
Start with the S and precipitation is greater than zero.
Close the parenthesis because that's my full and statement and then comma to my value if true so if
both of these are true what's the deal.
It's freezing.
There's stuff falling from the sky.
So the precipitation type is snow.
And now I'm gonna start with my second one again.
I could go up here into the formula bar and copy this chunk and paste it.
I'm going to type this one out just to kind of walk you through it one more time and then later in the
sections are going to start to do some short cuts like that.
So value false.
Remember I have one more condition to test before I do my catch all value.
So I mean do another if and statement in this case I to the freezing field is going to be equal to no
.
So temperatures above 32 and precipitation is greater than zero.
Close that out Khama over to value if true for this test that's going to be called rain.
Now my last comma gets me to my final value if false.
So if none of these tests are true what I want to return and I just want none here.
And then again need to close it out with two parentheses.
I can tell that I'm done because I got the bold black parenthesis closing out my entire formula.
Hit enter.
You know just like before a used relative references so I can just double click and apply this down
and it will update automatically and then you know one way to check my values I can just look at the
filter and see OK all three of my potential values have populated none.
Rain and snow.
I'm also getting an error of value because as we talked about there are some blank and missing values
which will address momentarily.
Now let's quickly do the conditions field.
This is the OR statement.
And again the conditions that must be true are that either the precipitation type is snow or the precipitation
type is rain in which case I'm going to call conditions wet otherwise dry so equals if or two conditions
are K-2 equals snow.
Remember in quotes because I'm dealing with the text string comma to my second criteria which is K-2
equals rain closedown or statement.
Comma over.
If either of these are true what value do I want to return that value is wet.
And then again if none of those are true my value of false is going to be dry.
Put it in quotes close it off it enter and apply that down.
Just like it did in the last example.
And as you can see I'm getting both dry and white values.
If I just do a quick spot check any time column K is none.
I'm seeing a drive value and then snow equals wet.
Rain equals wet.
Everything looks good.
So there you go.
That's a quick example of how to use and or statements to develop more complicated and anemic logical
tests