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

[Kevork Krozian]

Hi Folks,

 The binary search/decision tree is a very powerful database structure especially for the fact that each comparison removes half of the data and narrows the search down dramatically. The key is to have ordered data in a balanced tree.  Maximum search comparisons is the height of the tree in number of nodes. 

 All of which takes me back to second year Computer Science where mathematical proofs were required for the behaviour of these database structures and algorithms on how to balance or rebalance a tree once a data insertion or removal had taken place.

 A great book on this and other related topics is "Data Structures in Pascal" by Aaron M Tenenbaum and Moshe J Tenenbaum who are probably related... 
It was my second year text book and I still have it on my desk.

 All of which makes me almost teary eyed recalling all those powerful Computer Science units we had to tackle back in the last millennium ....  he says to the strains of Charles Aznavour's "Yesterday when I was young .... " :)))

Best Wishes

Kevork 

>>> "Russo, Frank R" <Russo.Frank.R at edumail.vic.gov.au> 12/12/2007 4:37 pm >>>
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.
_______________________________________________
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