Tutorial Details
Downloadable Files: Excel File
Getting Started in Excel
How to Enter, Manage, and Find Data in Excel
Introduction to Formatting in Excel
Introduction to Formulas and Functions in Excel
Creating Charts in Excel

Introducing Logic into Formulas and Functions in Excel

Add to Favorites
Author: don

In this tutorial I am going to introduce the idea of Logic in Formulas.

A Logic test is a test that evaluates either True or False based on the outcome of an equation.

There are only 2 outcomes to a logic test, so, for example, I have the Formula =B2 > C2 written in cell A2. This is a logic test that evaluates whether the value in cell B2 is greater than the value in cell C2. As the value in cell B2 is 3 and the value in cell C2 is 8, this logic evaluates as FALSE because 3 is in fact less than 8, not greater.

The same works for the equals (=) and less than (<) symbols.

In this logic test, I am evaluating =B3 = C3. As the values are the same in cell B3 and cell C3 this logic evaluates TRUE.

Logic with the IF Statement in Excel

Taking Formula logic further, I will now look at how it applies to the IF Function which can come in mighty handy with Excel work. The first part of the IF Function is a logic test, just like before, and, in fact, to make things simple, I am going to use the exact same logic test as the first example:

The difference with an IF Function is that it will display 1 of 2 outcomes based on the evaluation of the logic test. If the last 2 arguments of the IF Function are left blank then the IF Function will perform just like a normal logic test and display either TRUE or FALSE.

The real power of the IF Function comes from changing these outcomes. The second Argument will be evaluated if the logic test is TRUE or the third Argument will be evaluated if the logic test is FALSE.

So looking at my example, I have B2 > C2 as the logic test. As we already know B2 is not greater than C2 so this logic evaluates FALSE. This means the third Argument of my IF Function is evaluated which is just to display the text string Value 2. Had my logic evaluated TRUE then the text string Value 1 would have been displayed.

Using Logic and IF Functions can be very useful for examining data, particularly if you are evaluating a group of numbers in a similar way. If I copy the Formula from the previous example down my list of numbers:

I can quickly see which column contains the larger number for each row.

Nesting Functions for Logic Tests

IF Functions become more powerful as you nest Functions within it. You can even put Functions into the logic test of the IF Function like so:

In this example, my logic test is evaluating whether the MIN value of cells B2:B4 are less than the MIN value of the cells in C2:C4. As the MIN value of B2:B4 is 3 and the MIN value of the C2:C4 is 2 then the IF Function evaluates 3 < 2. As 3 is in fact larger than 2, this logic evaluates FALSE and displays the string Value 2 in cell B6.

Logic tests like these can allow you to compare values across massive tables of data. You can even compare whole columns/rows to each other, all in a single Formula. Logic testing can help you evaluate data far quicker, cutting down minutes of work into seconds.

I hope you enjoyed this tutorial! :)

Question? Ask it in our Excel Forum


Downloadable Files: Excel File