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

[Russell Edwards]

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