[Year 12 IT Apps] Sample questions - again

[Mark Kelly]

Hi again, all. After a further look I've changed my view a little.  The 
formula is working the charge for rate 1 and other charges are added 
later, so I guess it's not so odd that the IF's true condition could 
well be invariant for many bills.  But it seems to be a rather 
inefficient way of doing it, I think.

More elegant would be something like =IF(G2>3900 , 3900*J6 , G2*J6) 
because the VLOOKUPs are achieving little. I'm not fond of building 
constants like 3900 into formulas, but never mind.

All in all, it's a rather atypical use of VLOOKUP for an exam question, 
I reckon. Anyway, all this is just my way of avoiding finishing my 
correction and writing reports  ;-)


Mark Kelly wrote:
> Had a longer look... and it's an odd thing indeed.
> 
> If my calculations are correct...
> 
> For one thing, the IF's true expression (3900*VLOOKUP(3900,$I$6:$J$8,2)
> is an invariant calculation - it gives 39.00!
> 
> So if the usage is any value over 3900, charge a flat rate of $39.00,
> and if it's a small usage, charge on a sliding scale that also uses an
> invariant row in the lookup table - the rate will always be .01!
> 
> So the formula actually resolves to:
>  =IF( $G$2>3900 , 39.00 , $G$2*.01 )
> 
> I wish MY gas company charged like that  :-)
> 
> One hopes such a question is not a true sample of what to expect at the 
> end of the year!
> 
> Mark Kelly wrote:
>> Has anyone had a close look at 10a?
>>
>> =IF($G$2>3900,3900*VLOOKUP(3900,$I$6:$J$8,2),$G$2*VLOOKUP($G$2,$I$6:$J$8,2,TRUE)) 
>>
>>
>> Does anyone reckon there's an error here?  I think the ">" should be 
>> "<" otherwise it makes no sense!
>>
>> Must think about it when time permits - some time in term 3 perha[s  :-(
>>
>> Mark
>>
>>
> 

-- 
Mark Kelly
Manager - Information Systems
McKinnon Secondary College
McKinnon Rd McKinnon 3204, Victoria, Australia
Direct line / Voicemail: 8520 9085
School Phone +613 8520 9000
School Fax +613 95789253
kel AT mckinnonsc.vic.edu.au

Webmaster - http://www.mckinnonsc.vic.edu.au
IT Lecture notes: http://vceit.com
Moderator: IT Applications Mailing List

There are 10 kinds of people in the world: those who understand binary
and those who don't.