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

[Mark Kelly]

O...K... I'll take your word for it.

I was - am - hopeless at maths.  I argued passionately with my year 8 
maths teacher that negative numbers were impossible...  And in the past 
few decades I have come up with some new and *convincing* reasons I 
could give her if I met her again.  I think I'd win this time.

But then again, after a bottle of Cab Sav one day I proved that 
Weierstrass's final theorem of arithmetic was not final. That was an odd 
afternoon - I attribute it to the influence of Hungarian salami and the 
exotic dancers in my kitchen.

Anyway...

Only a few days until VCE results come out... (gulp)
Mark

Russell Edwards wrote:
> true
> X-Safemail-Anti-Spam-Result: AgAAAC/UXUfKLW7an2dsb2JhbACBWo4NAgEBBwQGCQgY
> AgAAACLRXUc7p4iK/2dsb2JhbAAI
> X-Safemail-AV: E=Sophos;i="4.24,150,1196602200"; d="scan'208";a="15017649"
> 
> 
> 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
> 

-- 
Mark Kelly
Manager - Information Systems
Christmas Secondary College
ho.ho.h at edumail.vic.gov.au