[Year 12 IPM] Nested IFs

[Mark Kelly]

The nested IF issue grabbed my curiosity.  Went Googling to try and find 
a really good case of where a nested IF was essential.

My first Googling found...
http://www.cpearson.com/excel/nested.htm

It presents the case of...

> Suppose we wanted an nested IF formula to test:
> IF A4 = 1 Then 11
> Else If   A4 = 2 Then 22
> Else If   A4 = 3 Then 33
> Else If   A4 = 4 Then 44
> ...
> Else If   A4 = 13 Then 130  Else "Not Found"

The author then creates a massively complicated IF solution.

This is an example of where you just would not use nested IF at all.
You would use VLOOKUP.  Since the lookup values are simply 1 to 13, just
use:

IF A4=	RETURN VALUE
1	11
2	22
3	33
4	44
..	..
13	Not found

(I hope the tabs above survive MIME encoding and the numbers don't crash
into each other)

Name the above range "table" and use =VLOOKUP(A4,table,2)

Far better than twisting yourself up with IF...

-----------------------------------------------------------------------------

Hmmm, just found the CHOOSE() function which lets you easily look up 
values for an index value e.g.

=CHOOSE(daynumber,"Sunday","Monday","Tuesday"... etc)

That may be useful too!

-----------------------------------------------------------------------------

---------------------------------------------------------------------------

Second Google led to...
http://spreadsheets.about.com/cs/excelfunction1/a/nestediffunct.htm

It says...

> To illustrate, let's say you have a spreadsheet that you use to keep
> track of your sales force. The rate of commission each sales person
> receives is based on the amount of sales they have generated for that
> month. For example:

> From $1 to $10 earns 10% commission
> From $11 to $100 earns 15% commission
> Anything over $100 earns 20% commission

> Assuming the amount of sales is in column B, starting at row 4, and
> that the column containing the commission is formated for percentages,
>  this is what the nested IF function would look like:

> =IF(B4<=10,"10", if(b4<=100, "15", "20"))

Once again, a simple VLOOKUP is far superior.

FROM   TO    COMMISSION
1      10    10%
11     100   15%
101          20%

Name the range (excluding headings) "table" and then use
=VLOOKUP(sales,table,3)

-------------------------------------------------------------------

Note the method I now use for setting out lookup tables.  I used to get
mental cramps working out the cutoff values for categories.  Now I use
"From" and "To" columns to make it much easier to determine the numbers.
  The second column is actually never used by the lookup, but it helps
my brain.  Life is som much more pleasant now  :-)


---------------------------------------------------------------------

Example 3, found at
http://www.techonthenet.com/excel/formulas/if_nested.htm

Question:  In Excel, I need to write a formula that works this way:

 > If (cell A1) is less than 20, then times it by 1,
 > If it is greater than or equal to 20 but less than 50, then times it by 2
 > If its is greater than or equal to 50 and less than 100, then times 
it by 3
 > And if it is great or equal to than 100, then times it by 4

 > Answer:  You can write a nested IF statement to handle this.  For 
example:

 >  =IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))


Once again, it's far easier to use

 From    To    Multiplier
0       19    1
20	49    2
50      99    3
100           4

Name the range "table" (or whatever)

In the target cell just put =a1 * VLOOKUP(A1,table,3)

---------------------------------------------------------------
Example 4, found at
http://www.techonthenet.com/excel/macros/if_custom.htm

 > Question:  I have a formula in Excel that I am using to test for 7
 > conditions, and each condition if true will return a different value.
 > However, I now need to test a total of 12 possible values.  The
 > limitation of the nested IFs is that you can only nest up to 7.  Is
 > there an alternative to this formula to test so that I can test for 12
 > values instead of 7?

 > 
=IF(A1="10X12",120,IF(A1="8x8",64,IF(A1="6x6",36,IF(A1="8x10",80,IF(A1="14x16",224,IF(A1="9x9",81,IF(A1="4x3",12)))))))

 > Answer:  There is no built-in alternative formula in Excel, but you
 > could write your own function in VBA and then call this new function
 > instead.

Or you could (again) use vlookup...

10x12   120
14x16   224
4x3     12
6x6     36
8x10    80
8x8     64
9x9     81

Sort by the first column so they're sorted alphabetically.  Call it 
"table" and use =VLOOKUP(a1,table,2)

----------------------------------------------------------------------------

In short: I very rarely use nested IFs because often there are alternatives.

Does anyone know of a neat example of using nested IFs wisely?

-- 
Mark Kelly
Manager, Information Systems
McKinnon Secondary College