Excel IF Formula: How to Use It, Nesting, and Applications

Excel

Monday, July 12, 2021

The IF formula found in spreadsheet software like Microsoft Excel, Google Sheets, LibreOffice, and OpenOffice, is one of the basic ways to test conditions and display values based on whether that value is true or false.

*An example of using an IF formula in Excel to check whether or not a cell’s text is “Blue”.*

The IF keyword is pretty fundamental for conditional functions that work differently based on values, or for mass calculating a large column of text or numbers. It can be used as simply as in the screenshot above, displaying a single value on one condition such as the value of an adjacent cell. However, the use of cell referencing and nested IFs can prove IF very powerful. Similar formulas include:

IFS: Make logical comparisons with multiple conditions.
SUMIF: Sum a range of numbers based on a condition applied to a tested range.
SUMIFS: Like SUMIF, but with multiple possible conditions.
COUNTIF: Count a range of numbers based on a condition.

Syntax

The syntax is pretty simple. As with any formula, you will have to preface IF with an equals sign, but a plus sign works equally as well.

=IF(condition, [true], [false])

There are three arguments total, with only condition being required. However, true and false are nearly always filled when used.

condition: the condition which will be tested.

[true]: Optional – the value to be displayed if the condition is deemed to be true.

[false]: Optional – the value to be displayed if the condition is deemed to be false.

While both the true and false segments are optional, you are required to have one or the other, or else Excel will tell you that the formula is problematic. If only one of either true or false is filled, there will be a default placeholder for the non-filled value if the condition equates to it. For example:

Cells C4 (5) and C5 (6) both don’t satisfy the condition of being greater than 6, so their corresponding cells display FALSE, as the formula does not provide a value for if the cell is false.

Possible Conditions

The above example only utilizes two possible conditions- checking equivalence between a cell and a string of text, and comparing the value of numbers. There are many more possible conditions, as noted below:

A1>B1: Greater than

Will return true if A1 is larger than B1. As with all arithmetic operators, > is capable of numeric comparison and text comparison. As an example, 5 is larger than 3, and “balloon” is greater than “apple”.

A1>B1: Greater than or equal to

Will return true if A1 is larger than or equal to B1. As with all arithmetic operators, >= is capable of numeric comparison and text comparison.

A1<B1: Less than

Will return true if A1 is lesser than B1. As with all arithmetic operators, < is capable of numeric comparison and text comparison. As an example, 2 is lesser than 7, and “caramel” is lesser than “tomato”.

A1>B1: Greater than or equal to

Will return true if A1 is lesser than or equal to B1. As with all arithmetic operators, <= is capable of numeric comparison and text comparison.

A1="Orange": Equality

Will return true if values on both sides are equivalent. As with all arithmetic operators, = is capable of numeric comparison and text comparison- it is not case sensitive. For example, 5 = 5 and “Robot” = “Robot”, but also “robot” = “Robot” and “umBrelLa” = “UMBRELLa”.

C4<>5: Inequality

Will return true if both sides consist of different values. As with all arithmetic operators, <> is capable of numeric comparison and text comparison. For example, 5 <> 3 and “banana” <> “strawberry”.

The above list contains common ways to use IF, but is not an exhausive list.

Nested IFs

Nested IFs can be very powerful, allowing the testing of multiple conditions. Essentially, they are IF statements that are inside other ones, with practically no limit, allowing condition testing to scale well. For a lot of applications, nested IFs can be super useful if trying to get a certain value based off of another one, with more possibilities than just a simple IF statement.

Excel limits the amount of nested IFs to 64, and while it is possible to create complex formulas that test for many conditions, it is not advisable to do so. Each statement makes the entire formula more complex and less easy to read. A lot of the time, using AND or OR can simplify multiple-condition checking, and VLOOKUP, HLOOKUP, or XLOOKUP can replace mapping values to other ones.

Nonetheless, nested IFs can be great for simple applications such as the examples shown below. Let’s see a the two basic ways to nest IFs.

Translating a cell to a value on a scale

Nested IFs can be used to get a value on a scale from a value of another cell. For example, a popular example is the A-F grading system. Five letters are assigned to ranges between 0 and 100. Logically, this would translate to an IF statement assessing if the number grade is over 90, giving an A if true, and returning another IF statement if false, which would then assess if the number is over 80, and the cycle would continue until the last IF statement. General syntax would look like this:

=IF(condition,trueValue,
                        IF(condition,trueValue,
                                               IF(condition,trueValue,
                                                                      IF(condition,trueValue,
                                                                                             falseValue))))

Note that I did put each IF statement on a different line for ease of reading’s sake.

Excel goes to the first statement in the top left, and tries the condition. If it returns true, the entire formula ends there and trueValue is displayed. If false, it goes to the next IF statement and continues the chain until there are no more IF statements, in which case, it returns the falseValue.

Testing for multiple conditions

Nested IFs can also be used to test a value for multiple conditions; for example, checking that a value is over 5, under 50, and divisible by 3. Instead of having the next IF statement in the chain in place of the false value, testing for multiple conditions requires all IF statements to be in the place of the true value, as it will check to see if all the conditions are true. If any conditions return false, whatever value is in place of false will be displayed and the chain will end. Basic syntax will look like this:

=IF(condition,                                                              falseValue)
              IF(condition,                                     falseValue),
                           IF(condition, trueValue, falseValue),

Instead of looking like a staircase, the formula resembles more of a V-shape.

Again, Excel looks from the first statement and tests the condition. If it’s true, then it will go to the next IF statement and repeat the process. If false is returned at any point in the formula, the falseValue for the IF statement that is currently running will be returned.

Examples

Grading a Test via Pass/Fail

*An example of four tests being graded by a pass/fail system.*

Quite a common use for IF statements is checking if a value in a cell is over a certain number. Pass/fail tests or quizzes can be very efficiently graded using the formula above.

The formula checks to see if the value on the left is equal or larger than 0.6, or 60%- a common border between a passing and failing score. If the value on the left suffices, like in the first three cells, “Pass” will be displayed on the right. If it doesn’t suffice, then “Fail” will be displayed in the cell.

This is one of the simpler applications of the IF formula, but can make grading tests a whole lot more efficient, especially if the amount of tests to grade is in the hundreds or thousands. Due to the nature of the autofill feature present in most spreadsheet processing software, dragging down and auto-populating the cells will let thousands of cells compute in mere seconds.

Grading on an A-F letter scale is a little more complicated than this, requiring either the use of nested IFs or the IFS formula.

Basic Transcription

*An example of written-out numbers being transcribed to usable digits.*

Basic-level transcription is also possible with the IF formula by checking the value of a cell and displaying a number value for it, allowing it to be used by other cells more effectively.

The formula checks if the cell on the left is equal to “One”, and will display 1 if true. If not, it will go onto the next if statement, checking to see if the text is equal to “Two”, displaying 2 if true, and so on. This is an simple example of a nested if, checking the value of the cell against multiple conditions. Of course, we only have a simple chain of three conditions which will only return a number value if the cell’s text is specifically “One”, “Two”, or “Three”. However, this can be extended as long as possible, although at that point the formula will get quite long.

Grading a Test via the A-F Letter Scale

*An example of 8 students’ tests being graded on an A-F scale.*

As said before, grading on an A-F scale, or any scale that has more than two outcomes, requires the use of either a nested IF or the IFS formula (which is just a slightly simpler construct for nested IFs).

The syntax above might seem a little bit complicated at first, but once you look into it, it is pretty simple as it follows a simple pattern.

=IF(N4>=0.9,"A",
                IF(N4>=0.8,"B",
                               IF(N4>=0.7,"C",
                                              IF(N4>=0.6,"D",
                                                             "F"))))

The typical A-F scale has five possible outcomes- A, B, C, D, and F. Each of these letters cover 10% of the score spectrum, with A’s being 90% – 100%, B’s being 80% to 90% exclusive, C’s being 70% to 80% exclusive, et cetera. Using this information, the first IF formula’s condition checks if the cell on the left- in the example, it is 0.79 or 79%- to see if it is above 0.9. In this case, it is not, so the false value is returned, prompting the second IF formula to trigger. Its condition is checking whether or not the cell on the left is above 0.8, which fails, once again returning the false value, prompting the third IF formula to trigger. This time, it checks to see if the value is above 0.7- it is! The nested if chain stops at this point and the value C is returned.

Note that although we have five possible outcomes, we only have four IF formulas. Since F‘s possible values range from 0% through 60% exclusive, we can safely put F at the end of the formula, as it will cover every other valid value.

Summary

Excel’s IF formula is powerful and is the basis of many logic-based Excel models. It allows a cell to test for a condition and will return a certain value if it is true, and another value if it is false. Not only can text or numbers be returned if true or false, but also formulas, allowing complex logic with nested IF statements and other gates, along with other formulas that compute something else.

While being very powerful, it’s always advisable to keep them to a manageable amount to keep formulas readable and mantainable. Other formulas like AND and OR for logic checking and VLOOKUP or XLOOKUP for value mapping will likely be more readable and efficient, as they are built for those functions specifically.

Nonetheless, in many applications, IF can prove to be a very useful tool. While it may not be optimized for some uses in Excel, a lot of formulas are impossible without IF- it’s a simple way to compare logic that can be used by both entry-level and master Excel users alike.