[Year 12 IT Apps] Some things do sink in after all

[Russo, Frank R]

Hmmm I find just the lookup to be much easier than vlookup or hlookup

Frank Russo
ICT ~ Teaching & Learning
Monterey Secondary College
-----Original Message-----
From: itapps-bounces at edulists.com.au
[mailto:itapps-bounces at edulists.com.au] On Behalf Of Russell Edwards
Sent: Tuesday, 11 December 2007 6:57 PM
To: Year 12 IT Applications Teachers' Mailing List
Subject: Re: [Year 12 IT Apps] Some things do sink in after all


On 11/12/2007, at 10:30 AM, Mark Kelly wrote:
> http://www.mckinnonsc.vic.edu.au/vceit/howto/nestedif.htm

I believe there is a simple proof that any nested IF statement can be  
replaced with a single VLOOKUP.

The procedure would be:

1) Draw the IF statement as a binary decision tree
2) Assign values of 1 to the bottom-level decisions, 2 to next up, 4  
next up and so on in multiples of 2
3) Assign values to each leaf as the sum of the values of all TRUE  
decisions leading to it.
4) Make a lookup table mapping values  to leaf results
5) Use a VLOOKUP, to get to the leaves

A simple example so I can avoid trying to do step 1 in an email

if (x, if(y,a,b),if(z,c,d))

then you have x = 2 , y=z=1, so then your table looks like this

0 d
1 c
2 b
3 a

and you can replace the if statement with VLOOKUP(x*2+y+z, table, 2)

Of course, here x and y are conditional expressions and we're relying  
on the fact that in Excel, false and true evaluate to 0 and 1  
respectively. The lookup table is simply a tree traversal only  
including leaf nodes.

If I'm not mistaken this can be extended to any nested IF statement.

Russell Edwards
Whittlesea Secondary College


_______________________________________________
http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
IT Applications Mailing List kindly supported by
http://www.vcaa.vic.edu.auvce/studies/infotech/itapplications3-4.html -
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 and Early Childhood Development.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Russo, Frank R.vcf
Type: text/x-vcard
Size: 524 bytes
Desc: Russo, Frank R.vcf
Url : http://www.edulists.com.au/pipermail/itapps/attachments/20071212/dd2c1ea3/RussoFrankR.vcf