A Building Block of Spreadsheets
One of the most commonly used functions in a spreadsheet app is IF(). It's also the most basic function used for testing conditions needed to make decisions. As a reminder, this is the format used by Excel (and many other spreadsheet apps) for IF():
Excel syntax for IF() |
|---|
=IF(logical_test,[value_if_true],[value_if_false]) |
IF() is a vital function, and formulas using nested IF()s are powerful tools. Just replace [value_if_false] with another complete IF() or 7 as shown below:
Unfortunately-- as shown by Cell D3-- it's very easy for nested IF()s to get out of control both visually and fuctionally:
| Cell D3 from the Foreign Currencies Purchased table |
|---|
=IF(B3=$G$3,$H$3, IF(B3=$G$4,$H$4, IF(B3=$G$5,$H$5, IF(B3=$G$6,$H$6, IF(B3=$G$7,$H$7, IF(B3=$G$8,$H$8, IF(B3=$G$9,$H$9, IF(B3=$G$10,$H$10)))))))) |
Cell D3, reformatted with traditional spacing for a programming language
=IF
(
B3=$G$3,
$H$3,
IF
(
B3=$G$4,
$H$4,
IF
(
B3=$G$5,
$H$5,
IF
(
B3=$G$6,
$H$6,
IF
(
B3=$G$7,
$H$7,
IF
(
B3=$G$8,
$H$8,
IF
(
B3=$G$9,
$H$9,
IF
(
B3=$G$10,
$H$10
)
)
)
)
)
)
)
)
In this case, the nested IF() works. Each version of value_if_false is replaced with an IF() until the final IF()-- IF(B3=$G$10,$H$10)-- is reached.
ZAR is the 8th currency listed in the table of 8 currencies, and the nested IF() needed to execute every test before reaching it.
Had it failed due to a typo or (even worse) a logic error, correcting this nested IF() would have been painful in more ways than one
On top of that, if the spreadsheet is of a decent size there are noticeable time penalties in processing nested IF()s like that one. Even if all cells containing nested IF()s like that are correct, time and (in this case) money are literally wasted.
The 8 Ways
Just as there are various ways of saying the same thing in spoken speech, there are various ways of rewriting nested IF()s. Below are at least 8 ways widely available across many versions of Excel and other spreadsheet apps to rewrite a nested IF() to get the same results:
Nested IF() Rewrite | Code | Example Currency |
|---|---|---|
VLOOKUP() | GBP | UK Pound Serling |
| CHOOSE-MATCH | CAD | Canadian Dollar |
| INDEX-MATCH | EUR | Euro |
| VLOOKUP-CHOOSE | CHF | Swiss Franc |
SUMPRODUCT() | AUD | Australian Dollar |
SUMIF() | XCD | East Caribbean Dollar |
| Boolean Logic | INR | Indian Rupee |
REPT() | GBP | UK Pound Sterling |
8 Ways To Rewrite Nested IF()s
The first 6 ways look more complex at first glance, but they are more manageable than the nested IF()s they replace. The last 2 ways rely on 1's and 0's being produced as side effects; these will be shown to be useful later. In the case of SUMPRODUCT(), Boolean Logic is built-in.
The 8 Examples
Each of the 8 ways to rewrite nested IF()s makes use of the two tables introducted at the top of this post: the larger Foreign Currencies table and the smaller Exchange Rates table.
The calculated values in Column E are just products of values of Column C and values from Column D.
Column D used by the Foreign Currencies table will contain one of the 7 nested IF() rewrites. Each refers to the 3-letter currency code found in Column B.
For our purposes, Column D is where the action is. In the case of VLOOKUP- CHOOSE, a 3rd column was added to the Exchange Rates table to show how VLOOKUP() can overcome its best known limitation.
This introductory excerpt is taken from 8 Ways To Rewrite Nested IF() Functions at Magna Carta XLS Communications. Each of the 8 nested IF() rewrites from that post will be featured in its own post here.
Other posts will be migrated to this blog before I begin writing posts natively here.