Introducing Logic into Formulas and Functions in Excel

Add to Favorites
Author:

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! :)


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Understanding Formulas and Functions in Excel
Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...
How to Input, Edit, and manage Formulas and Functions in Excel
Tutorial: In this tutorial I am going to introduce how to input, edit and manage excel formulas. To ...
Manually Calculate Formulas and Functions in Excel
Tutorial: How to force Excel to recalculate all formulas and functions without editing or entering ...
Guide to Referencing Other Excel Files with Formulas and Functions
Tutorial: Your guide to making cross-workbook formulas and functions. This includes an overview of p...
Prevent Errors From Appearing in Excel
Tutorial: How to prevent errors from appearing in formulas in Excel. This is especially helpful for...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course