## 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**

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()](./rewrite-1-lookup)** | GBP | UK Pound Serling | | **[CHOOSE-MATCH](./rewrite-2-choose-match)** | CAD | Canadian Dollar | | **[INDEX-MATCH](./rewrite-3-index-match)** | EUR | Euro | | **[VLOOKUP-CHOOSE](./rewrite-4-vlookup-choose)** | CHF | Swiss Franc | | **[SUMPRODUCT()](./rewrite-5-sumproduct)** | AUD | Australian Dollar | | **[SUMIF()](./rewrite-6-sumif)** | XCD | East Caribbean Dollar | | **[Boolean Logic](./rewrite-7-boolean-logic)** | INR | Indian Rupee | | **[REPT()](./rewrite-8-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](https://mcxlsc.com/8-ways-to-rewrite-nested-if-functions/) at [Magna Carta XLS Communications](https://mcxlsc.com/). 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.

Topics