[Year 12 IPM] Unit 4 Outcome 1

[Mark Kelly]

Attached is a task (Pigs Galore) I created to get the rust of my year 11 
IT kids' Excel skills.  Solution also enclosed (zipped).

I'll probably use it for U4O1 practice too.

re. nested IFs... I just avoid them whenever possible.  They're ugly and 
violent beasties better left alone.

Sometimes they can be designed around, e.g. you want to specify two 
conditions.

Instead of

=IF(age<18,if(sex="M","young man",""),"")

one can use the more elegant

=IF(AND(age<18,sex="M"),"young man","")

Similarly, VLOOKUP can be used to make some decisions, e.g.

Instead of

=IF(dept="Sales",100,if(dept="finance",200,if(dept="transport",300,"Invalid 
Department"),""),"")

(Don't tell me the formula's probably broken - that's the reason I hate 
nested IFs!)

You could use
=VLOOKUP(dept,deptcodes,2)

where "deptcodes" is defined as (in ascending alpha order, of course!)

Finance		200
Sales		100
Transport	300

Unfortunately Excel does not have the equivalent of SELECT CASE like 
Visual Basic and Filemaker Pro, so nested IFs can occasionally be a 
necessary evil.  Hmmm - trying to think of a good example that can't be 
done better another way...  Let's say people are paid different rates 
based on their sex and age...

SEX	YOUNG	OLD
Male	$10	$15
Female	$8	$12

You might need to use...

=if(sex="M",if(age="young",10,15),if(age="young",8,12))

HTH




Emma Coughlan wrote:
> Morning everyone,
> 
> Just wondering if anyone could help me. I am starting
> unit 4 outcome 1 and we are using Excel. Does anyone
> have any excel exercises? particularly nested If
> statements.
> 
> If anyone has anything it would be greatly
> appreciated.
> 
> Thank you :)
> 
> Kind Regards,
> 
> Emma Coughlan
> St Helena Secondary College
> Eltham
> 
> Enjoy your day as you never know whats around the corrner
> 


-- 
Mark Kelly
Manager, Information Systems
McKinnon Secondary College
McKinnon Rd, McKinnon Victoria Australia
9578 0844  Fax 9578 9253
Webmaster http://www.mckinnonsc.vic.edu.au
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pigsgalore.zip
Type: application/zip
Size: 14666 bytes
Desc: not available
Url : http://www.edulists.com.au/pipermail/ipm/attachments/20050520/8f899597/pigsgalore.zip