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.
——————————–
Excel Nested IF Formula Horror Stories
——————————
Debra:
Here’s my horror story: An If formula designed to replicate the US Federal Income tax table for 2019:
=IF(BU9=2,IF(Y9>0,(MIN(19400,Y9)-0)*0.1,0)+IF(Y9 > 19400,(MIN(78950,Y9) – 19400) * 0.12,0)+IF(Y9 > 78950,(MIN(168400, Y9) – 78950) * 0.22,0) + IF(Y9 > 168400,(MIN(321450, Y9) – 168400) * 0.24,0) + IF(Y9 > 321450,(MIN(408200,Y9) – 321450) * 0.32,0)+IF(Y9 > 408200,(MIN(612350,Y9) – 408200) *0.35,0)+IF(Y9 > 612350,(Y9 – 612350) * 0.37,0),IF(BU9=1,IF(Y9>0,(MIN(19400,Y9)-0)*0.1,0)+IF(Y9 > 19400,(MIN(78950,Y9) – 19400) * 0.12,0)+IF(Y9 > 78950,(MIN(168400, Y9) – 78950) * 0.22,0) + IF(Y9 > 168400,(MIN(321450, Y9) – 168400) * 0.24,0) + IF(Y9 > 321450,(MIN(408200,Y9) – 321450) * 0.32,0)+IF(Y9 > 408200,(MIN(612350,Y9) – 408200) *0.35,0)+IF(Y9 > 612350,(Y9 – 612350) * 0.37,0),0))
This could likely be replaced by a lookup, but not without some manipulation, since the income levels in the formula are marginal rates, so the total tax is the sum of the max tax for each of the lower levels plus the marginal tax above the highest rate threshold. At the time the formula was constructed, it was more expedient to just do that than to develop a table to address the calculation.
Here’s the table it’s emulating:
SglRate Single AGI MFJRate MFJ AGI
10% $0 10% $0
12% $9,700 12% $19,400
22% $39,475 22% $78,950
24% $84,200 24% $168,400
32% $160,725 32% $321,450
35% $204,100 35% $408,200
37% $510,300 37% $612,350
Yikes! You were lucky to survive that 😉
My horror story is calculating an electrical cost from an electric utility rate:
=IF(C10>0,IF(AND(MONTH(A10)>5,MONTH(A10)<9),(INDIRECT(ADDRESS(ROW(R_PNM_On_Peak_KWH),ROW()-4,,,"Report"))*summerPeakKWhChg+(C10-INDIRECT(ADDRESS(ROW(R_PNM_On_Peak_KWH),ROW()-4,,,"Report")))*summerOffPeakKWhChg+INDIRECT(ADDRESS(ROW(R_PNM_Billable_Demand_KW),ROW()-4,,,"Report"))*summerKWChg+C10*(secondFPPCACChg+secondRERChg)+secondEECChg+secondReservChg)*(1+secondFranchiseFee),(INDIRECT(ADDRESS(ROW(R_PNM_On_Peak_KWH),ROW()-4,,,"Report"))*otherPeakKWhChg+(C10-INDIRECT(ADDRESS(ROW(R_PNM_On_Peak_KWH),ROW()-4,,,"Report")*otherOffPeakKWhChg)+INDIRECT(ADDRESS(ROW(R_PNM_Billable_Demand_KW),ROW()-4,,,"Report"))*otherKWChg))+C10*(secondFPPCACChg+secondRERChg)+secondEECChg+secondReservChg)*(1+secondFranchiseFee),"")
And here's the rate table that it's using:
Demand Charges (per kW)
Jun, Jul, Aug Other Mo's.
Maximum Peak Period Demand $20.63 $12.48
Customer Charge $3,666 $3,666
Energy Charges (per kWh)
Peak Period kWh Charge $0.020992 $0.016407
Off-Peak Period kWh Charge $0.00838 $0.00838
FY 1st half FY 2nd half
Non-Renewable Fuel Charge $/kWh / FPPCAC $/kWh $0.03 $0.0255102
Renewable Energy Rider $/kWh $0.0070504 $0.0070504
Energy Efficiency Charge $6,250 $6,250
Reservation Charge $4,213 $4,213
franchise fee 2% 2%