INR, the Indian Rupee, will be used in D10 to show Boolean Logic in action.
As mentioned in Rewrite #5 (SUMPRODUCT()), Boolean values are values which result in either a TRUE or FALSE value. As noted Rewrite #1 (VLOOKUP()), FALSE is equivalent to 0; that means that TRUE is equivalent to 1. As a reminder, this is how IF() is used:
Excel syntax for IF() |
|---|
=IF(logical_test,[value_if_true],[value_if_false]) |
logical_test involves some value being compared to some other value. The IF() given here has just 1 comparison or test, so it produces just one answer (TRUE/1 or FALSE/0). By itself, that’s not of much use. It’s almost as if it’s just a side effect.
Yet it’s precisely this side effect which makes Boolean Logic useful as a nested IF() rewrite.
Rather than determine which comparison is true before arriving at a result, Boolean Logic takes each logical_test and multiplies it by its corresponding value_if_true. One instance of this looks like this:
| // (comparison or test) x value |
|---|
=(logical_test) * value_if_true |
Boolean Logic is a nested IF() rewrite when a series of these building blocks are strung together to be added to end up with a final sum:
| A string of building blocks for Boolean Logic |
|---|
=(test1value1) + (test2value2) + (test3value3) + … + (test-nvalue-n) |
The tests can be as simple or as complex as needed; each test just has to resolve to TRUE or FALSE, and they need to be repeated through the chain of tests.
The first building block will compare B9 to the first item in the Exchange Rates table, the Australian Dollar (AUD):
| // (“INR”=”AUD”) * 0.771950 –> FALSE * 0.771950 –> 0 * 0.771950 –> 0 |
|---|
=(B10=$G$3) * $H$3 |
INR isn’t equal to AUD, so this comparison failed and the end result is 0. This happens 6 more times.
The winning comparison takes place with $G$8:
| // (“INR”=”INR”) * 0.013700 –> TRUE * 0.013700 –> 1 * 0.013700 –> 0.013700 |
|---|
=(B10=$G$8) * $H$8 |
This is what the rewritten nested IF() looks like using Boolean Logic:
| // More compact than the nested IF() used for ZAR |
|---|
=(B10=$G$3)$H$3 + (B10=$G$4)$H$4 + (B10=$G$5)$H$5 + (B10=$G$6)$H$6 + (B10=$G$7)$H$7 + (B10=$G$8)$H$8 + (B10=$G$9)$H$9 + (B10=$G$10)$H$10 |
While Boolean Logic isn’t as elegant as VLOOKUP() or as powerful as SUMPRODUCT(), it offers a good alternative to the nested IF() used to find the exchange rate for ZAR (South African Rand).
This 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.