If / Then Statements in Excel: Using Logical Formulas

If you are not using logical formulas in Excel, Then your spreadsheets could be better.  Makes sense, right?  And at the root of it, logic statments are that simple.  The complete syntax would be If something, Then something, Else something different:

If the lightswitch is up, Then the light is on, Else the light is off.

In that example there is a test, and only two possibilities.  In Excel, we would organize the question in this way. 

=if(the lightswitch is up, The light is on, The light is off)

So, if you are not currently using this funtionality in Excel, where could you?  Lots of places once you get comfortable with it.  For example, have you ever seen a spreadsheet that has an entire column of zeros because other columns have not yet been filled in?  Or even worse, a column of errors such as #Div/0! that is displayed when a formula divides by zero, a mathmatical no no. 

Errors and zero values are one of my pet peeves, because with a little more work we can get rid of them.  And since we are dealing with logic, just be logical.  The example above returns an error if numbers have not been filled in for column B and C.  So our statement in words would be:

If B3 and C3 are greater than zero, then calculate D3, otherwise show a blank cell. 

In practice, a company usually figures out sales faster than costs, so we could simplify and just check for a value in C3 as follows:

=if(C3>0,(B3-C3)/B3,"")

Notice that the 'else' in this case has empty quotes.  Any time you want the formula to return text instead of a calculated value, you need to put it in quotations.  If you want to return a blank cell, you still have to explicitly state that with "".

In another example, where we did need to confirm that both B3 and C3 had values, we would just need to change the test statement by adding AND:

=if(and(B2>0,C3>0),...

Taking the text example, we can use a cell to give a statement.  If we know that there is a goal of a profit margin of 35% then in E3 we can add an If statement:

=if(D3>35%,"Goal Reached","Below Goal")

Now if you are following along, or thinking it through in your head, you will have noticed that we have created a new problem.  Until you fill in March's numbers, it will tell you that you haven't reached your goal.  While this is technically true, if it is still February, then it isn't possible for you to have reached your goal.  This is just as bad as the zero values and errors.  So the way to solve it is to nest statments of logic within each other.  This is where a lot of people lose it. 

Think of the old decision flowcharts.  You come to a diamond that asks a question one path if yes, another path if no.  Each one of those paths will have additional questions with different outcomes.  If you need to diagram out the logic of your formula, there is no shame in it.  You are more likely to get it right the first time. 

When you build the formula consider only one section at a time and do not get confused by the commas and parentheses. What we will do is take the entire If statement above and put it in the Then portion of a new statement. 

=if(and(B3>0,C3>0),if(D3>35%,"Goal Reached","Below Goal"),"")

Notice that as you are typing the formula or using the cursor to move in it, that Excel will help you to keep clear on which section you are in by bolding the parentheses and give prompts below the formula bar.  Excel calls it value if true and value if false instead of then and else respectively.  

 

Now I admit that nesting multiple statements can get squirrely in a hurry.  And once you are done, you need to test to make sure everything is working as you expected.

One tip to make sure that your commas and parentheses don't get out of hand is that when you first type If to go ahead and type the framework of the entire statement:

=if(,,)

Once you have typed it out like that, use the cursor to back up to the correct part - if, then , else, and fill it in.  If you follow this method it is not possible to be missing a parentheses when you are done.  And trust me, when you start nesting complicated functions it is pretty easy to miss one otherwise.

There are many uses for this type of logical statement in Excel.  The complete list is:

And, False, If, Iferror, Not, Or, True

A comprehensive description of each of them would take more room than a reasonable post allows.  but I urge you to look them up and practice, because they are all useful and simpler to use than they may seem.  It is these touches which most users never even utilize that make the difference between your spreadsheets looking professional, or just thrown together. 

And if you need further assistance, please don't hesitate to contact me.

Comments

[Knitter]

I understand how to use if,then statements but I'm having trouble with one point. I want to compare two numbers, let's say cells B2 and C2. I want to know if b2 is greater than c2, and then I want to have the value of the greater to be entered at d2. How do I get the value of a cell to display as the answer?
=if(b2>c2, [the value of b2], [the value of c2])

[Kathy Layne]

That's it exactly. Just remove 'the value of' and you've got it, Knitter. However, there is an even easier way than using an IF statement. Just use the formula MAX: =max(B2:C2)

[Joshua Amann]

I have question. I have two columns which are drop down lists.

For Example:
Column 1
A
B
C
D
E

Column 2
F
G
H
I
J

I want each combination to equal as follows automatically in the third column.

Example:

If A and F are selected the value in the third column will equal 1

If A and G are selected, the value will be 2

If A and H are selected, the value will be 4

If A and I are selected, the value will be 7

If A and I are selected, the value will be 11

If B and F = 3
If B and G = 5
If B and H = 8
If B and I = 12
If B and J = 16

C and F = 6
C and G = 9
C and H = 13
C and I = 17
C and J = 20

D and F = 10
D and G = 14
D and H = 18
D and I = 21
D and J = 23

E and F = 15
E and G = 19
E and H = 22
E and I = 24
E and J = 25

Hope this makes sense. Not sure how I would set this up! Any help on this would be GREATLY appreciated!

[Mia Baum]

I had been looking for how to purge my spreadsheets of "#DIV/O!" so thank you for the "" tip.

So just so I make sure I don't purge all actual "O"s in my spreadsheet, what is the logical argument to replace "#DIV/O!" with "" for my current if, then statements in my spreadsheets?

Thanks.

[Kathy Layne]

Dear Joshua, great question. It ties in several importance Excel concepts. You need to understand:
Named Ranges,
Data Validation, and
the function VLookup.

Start by creating a worksheet called 'Names + Formulas'
In Row 1 type field names Column 1, Column 2, Combination, and Total. In Column 1 enter A-E and F-I in Column 2. In Combination fill in all of the combinations you identified in your question. In Total , match the combinations you specified.

Once you have the basics set, name the ranges. Select A1:A6 and select Formulas>Define Name. Excel will automatically use the name from the header but will select the entire range from A1:A6. Change the definition to A2:A6 before clicking OK. Do the same for column 2. For the Combination and Totals, select the entire range, but we will manually name this range, so select only the data C2:D26 and name the range Matrix.

Now that we have the data, and the names, let's move on to the data validation. Start a new worksheet and name the headers Column 1, Column 2, and total. Select the entire column A by clicking the header then selecting Data>Data Validation. Select list and in the source field type =Column_1. Do the same for Column 2.

Now for the total. We want to do two things, we need to combine column1 and 2 to create the combination, then we need to lookup the appropriate total from the Matrix range. But as I have also stressed in the blog it looks very unprofessional to have a column of errors or zeros before we fill in the data.

So our formula might start to look a little scary, but we are just doing these three things. To combine the values in column 1 and column 2 we use A2&B2. We place this into the lookup as follows:

vlookup(A2&B2,Matrix,2,0)

Now, to make sure that we don't get errors or zeros we place the vlookup into an if/then as:

=if(B2<>"",vlookup(A2&B2,Matrix,2,0),"")

Now just format the three columns and how ever many rows you need as a table, and drag the formula in Total down to the bottom.
Hope this helps!

[Kathy Layne]

Dear Mia,

If you can tell me what your exact formula is that is returning an error I can say for sure, but it is likely something like the example in the article where you have filled in the sales data, but have not yet figured out costs so your gross profit margin is dividing by zero/blank.

In this case, you can use an if statement to confirm that all of the necessary fields are filled in prior to calculation, or you can simply make sure that if the formula returns an error (which you have found is happening) that it gives you a blank instead. Excel has a built in function for just this purpose called IFERROR.
=IFERROR((A2-B2)/B2,"")
This built-in formula knows what you are trying to do and eliminates the need to put the formula in as both the test and the result. So in words it reads: if the test (A2-B2/B2) is an error, then display a blank, otherwise show the answer to the formula. Since dividing by zero returns the error value div/0! Excel would instead show a blank.

Let me know if this makes sense!