Written by Debra Dalgleish from Contextures Blog
Is the Excel IF function underused, overused or too often abused? Or some combination of all three? This short video shows how to make a simple IF formula. It checks if a cell is empty, then returns a result, based on that test. Too often though, the IF function is nested about 15 layers deep. Those formulas are confusing, and could usually be replaced by a much simpler solution. What are your nested IF Formula horror stories?
Video: Simple IF Formula in Excel
This video shows the steps to create a simple IF formula in Excel.
Video Timeline
- 0:00 Introduction
- 0:13 Do a Test
- 0:39 Order Form
- 0:55 Simple IF Formula
- 1:39 Test the Formula
- 1:55 Get the Workbook
What’s in an IF Formula?
The IF function has 3 arguments:
- TEST something, such as the value in a cell.
- Specify what should happen if the test result is TRUE.
- Specify what should happen if the test result is FALSE.
Simple IF Formula for Total Price
In the video, the IF formula, in cell E7, calculates the total price, if the Quantity cell is NOT empty.
- =IF(D7=””,””,C7*D7)
These are the 3 parts (arguments) for the Total Price IF formula:
- TEST cell D7, to see if it is empty (D7=””),.
- If D7 is empty (TRUE), the cell with the Total formula will show nothing (“”).
- If D7 is not empty (FALSE), the cell with the Total formula will multiply Price x Quantity (C7*D7)
In the screen shot below, cell D7 is NOT empty (FALSE), so the IF formula in cell E7 shows the total price.
IF Formula Horror Stories
Here’s the overly ambitious IF formula that I saw last week. Yes, there are eight IF functions nested in it – my eyes are still burning!
This is a desperate cry for a lookup table, right?
- =IF(OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(D2)),,
IF(D2=1,B2,
IF(D2=2,B2*1.5,
IF(D2=3,B2*2.1,
IF(D2=4,B2*3.3,
IF(D2=5,B2*5.1,
IF(D2=6,B2*6.2,
IF(D2=7,B2*8,”Unknown”))))))))
And I’ve seen workbooks with worse IF formula horror stories – how about you?
Instead of Nested IF Formulas
Instead of an 8-level nested IF formula, what would you use?
- I’d make a nice little lookup table with all the multipliers. That way, they’re easy to see, and simple to change, when you want to do an update.
- Then, I’d use a lookup function, like VLOOKUP, XLOOKUP or INDEX / MATCH, to get the multiplier.
For example, here’s a formula that counts the entries, then uses VLOOKUP to get the multiplier. For errors, IFERROR returns “Unknown”)
- =IFERROR(IF(COUNTA(A2,B2,D2)<3,””,B2*VLOOKUP(D2,tblRank,2)),”Unknown”)
The lookup list is in this formatted Excel table, named tblRank.
Get the Workbook
To get the sample Excel workbook, go to the IF Function Examples page on my Contextures site. The zipped file is in xlsx format, and does not contain any macros.
And instead of creating long, complicated, multi-level nested IF formulas, considering using a lookup formula instead.
INDEX / MATCH
VLOOKUP
Lookup Functions Compared
——————————–
Excel Nested IF Formula Horror Stories
——————————
Excel Nested IF Formula Horror Stories is a post from Contextures Blog and is not allowed to be copied to other sites