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)