[Year 12 IPM] Nested IFs
Sheard, Brian B
sheard.brian.b at edumail.vic.gov.au
Fri May 20 14:10:07 EST 2005
Hi,
Re the nested IF's bit. I agree that in lots of cases it is simpler to use a vlookup but there are cases (e.g. the reporting package we use where there are both Alphabetic and Numeric spreadsheets according to staff preference) where it makes sense to use nested IF's for grades because you can hiode the formulae - lock part of the sheet and it is easy to pick up any formula change from the one and import it into the other. IF (pardon the pun) nested IF's are to be taught (and they should be) there are a number of good examples out there.
Brian
________________________________
From: ipm-bounces at edulists.com.au on behalf of Mark Kelly
Sent: Fri 20-May-2005 12:29 PM
To: Year 12 Information Technology Processing and Management Teachers'Mailing List
Subject: [Year 12 IPM] Nested IFs
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
_______________________________________________
http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
IPM Mailing List kindly supported by
http://www.vcaa.vic.edu.au - Victorian Curriculum and Assessment Authority and
http://www.vitta.org.au - VITTA Victorian Information Technology Teachers Association Inc
Important -
This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education & Training.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/ms-tnef
Size: 10057 bytes
Desc: not available
Url : http://www.edulists.com.au/pipermail/ipm/attachments/20050520/ba7f68bd/attachment-0001.bin
More information about the ipm
mailing list