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.