All right.
So we talked about the statement briefly and Lecture 6.
I think it's worth revisiting here because it's especially relevant in this context.
Again it's really an excellent tool to eliminate annoying error messages and replacing those with a
value of your choosing.
There are a couple implications of doing that.
The first is in the context of just design and polish it can be nice as a front end formatting tool
or trick.
It can also be really useful on the data side and the back.
And so you may want to categorize your data consistently whether you're seeing an error message or not
or if it's a numerical field that you're working with and you want to be able to make statistical operations
on that column of data.
Sometimes having an end a value in there will prevent you from properly calculating those values so
you may want to replace the N.A. with something like 0 or another value.
So the syntax of the formula again is very simple just two pieces the value which is the formula or
value itself that may or may not result in an error.
And then the value if error.
This is what will replace the default error symbol with whatever you choose.
So again the tip here is just write your full formula first and then at the end your last step will
be to wrap it in an IF statement.
So let's jump to excel and if you recall with this weather data we're dealing with we've got some N.A.
values in our temperature column which is throwing off some of the formulas that we built out in columns
J.
K. so one by one I'm just going to wrap each of these in and if error and set a new value to replace
the N.A. and we'll just apply it down and watch those error messages update.
So in this case you know I could assign either a no or a yes.
In cases where I'm getting an error but I don't want to because I really don't know what the true underlying
temperature should have been.
So I don't want to just guess at it.
Instead I'm just going to define anything within error as other.
So I'm going to basically quit right after my equal sign em and sell I2 where my freeze formula lives
.
And I'm just going to type if error open a parenthesis and then the value as you can see is my entire
formula that is already written jumped to the end press comma.
Now I'm at my value if error component.
I'm just going to type other in quotes.
So I was blocking you and close it off with one parenthesis.
Hit enter.
So now I want to apply this down it'll overwrite all my formulas add the affair piece and if all goes
well replace any and A's or any other error messages with other boom.
There you go.
So and a in row 12.
You can see now it says either.
I've got one C and A's and rose to 26 and to 27 those of update the others.
Well looks good.
So let's do the exact same thing for the climate column type if error.
Open the parenthesis jump to the end comma other as the string.
Close it off.
Apply the formula down and there you go.
See you might have noticed that column K originally had error messages.
Now it doesn't.
And that's just because it was based on the errors that it was seeing in i and j which have been corrected
.
So therefore the formula in column K is now good to go.
So if we look at the filters you can see any values disappeared.
Now we just have yes no other climate we have hot cold mild or other.
So there you go using the affairs statement to replace Eneas with custom values.