One of our most popular blog posts is Excel IF AND OR Functions Explained which has over 800 comments at time of writing.
The vast majority of questions are along the lines of
I want to multiply a value by a percentage. In some cases, we need to enter the word "Special" instead of the calculation. I am getting the #Value! error, I am not sure if this is correct: =IF(OR(D4*I8), "SPECIAL") So I want it to enter the total of the multiplication from D4*I18 in cell D13, or if the cell has the word "Special" in it, I want it to show Special in D13 instead.
This is a basic problem that is easily solved using IF and OR. And that's cool, I'm not here to make fun of a lack of knowledge. Quite the opposite. We try to help people to learn so I thought how can I make it easier for people to learn how to use the IF function?
So I came up with this IF Formula Builder.
By entering a test and the results you want for this test, my workbook builds the IF formula for you.
Download The Workbook
Enter your email address below to download the sample workbook.
Let's start with a basic IF and look at the syntax.
With some real values this looks like
What this means is IF A1>10 then return the Boolean value True. Otherwise return the Boolean value False.
IF A1>10 then return the value in cell A1. Otherwise return the value in cell A2.
IF the SUM of values in cells A1:A5 < 100 then return the string "Light". Otherwise return the string "Heavy".
You can mix the types of values returned by the IF function, they do not have to be the same. For example, you can have a number and a string
IF the value in A1>10 then return the number 5. Otherwise return the string "Cool".
Building an IF Formula
Open the IF Builder workbook and on the sheet you will see this
There are sections where you can enter your test, then specify the result you want when the test is true, and when it is false.
Your formula is then created for you.
You can choose the operator for the test from a data validation list, which is using mathematical comparison operators.
The test doesn't have to be something simple. Let's try the result of another function as our test, and I'll use strings as the True and False results of the test.
Why not go the whole hog and use functions as the test and the True and False results.
Getting Your Formula
Once you've built your formula, you can move it into your workbook using copy/paste special.
- Copy the formula
- Right click, Paste Special->Values
- With the new formula cell selected, press F2
- Press Enter
IF AND OR
We get a lot of questions about using IF with AND and OR. I think the problems I see people having with the functions AND and OR are because these functions do not follow the same logic/structure as an English sentence.
In English you would say IF (A1 > 10) AND (A2 > 50) but in Excel you must write IF (AND (A1 > 10, A2 > 50)).
Once you understand this and treat AND and OR as functions rather than a conjunction to join two parts (or more) of a sentence, it becomes easy.
On the sheet IF AND OR in my workbook you will find the IF AND OR function builder.
Nested IF Formulas
This is where it can get very messy. If you need to nest more than 3 IF's, you're probably better off using something like VLOOKUP.
But if you have just a few IF functions, nesting them is ok.
Where people go wrong here is getting confused with what to enter for the True and False results, and the way Excel displays the formula as you enter it does not help.
I get confused myself sometimes trying to work out how many closing parentheses I need.
What you are doing with a nested IF is saying, 'I have a number of different inputs, and for each one, I have a different output'.
If we use real values, we could represent different inputs and outputs by this table:
Or in psuedo-code
If (Ice Cream THEN Cold) ELSE If (Candy THEN Sweet) ELSE If (Pop THEN Fizzy) ELSE Healthy
Notice that we don't need to explicitly test for the last input. If we have already tested for 3 of the 4 possible inputs and haven't yet found a match, then the final input must be the only remaining one : Apple, so the output must be Healthy.
You could visualize this in a diagram like so
Nested IF Builder
Looking at the Nested IF Builder we can construct the IF formula like this
I've only written the Nested IF builder so that we are replacing the False results with another IF. There's nothing to stop you replacing the True result with an IF, or using some other function for the True or False results.
If you didn't already, hopefully now you'll be able to understand how to do this yourself.
I've used an old Excel 4 macro EVALUATE to calculate the result of the constructed formulae. I was trying to avoid using VBA. If you have any issues with EVALUATE not working for you, let me know and I'll work on a VBA version of the IF Builder workbook.