10. Making Decisions in Spreadsheets

Conditional Function (IF)

A conditional formula is used in a spreadsheet when it is required to display one result IF a condition is true and another result if the condition is false.

Introducing the IF Conditional Function!

Introducing the IF Conditional Function! Click to Go Larger Screen

1. Download the file exam marks.xls.
   
   
   
   
2. Watch Movie 12 and using the methods shown fill in all the blank cells using conditional function where appropriate.
3. Save your file.

More Practice with the IF Condition

1. Calculating Commission

1. Download the file sales bonus.xls, and,
   
   
   
   
2. Enter the correct formulae in column E to calculate commission (this is 10% of the sales).
3. Column G should display a bonus showing either the contents of cell C2 (currently £400) or a value of £0. This is dependant on whether the salesman's sales are greater than £15000 or not. If they are, they get the C2 bonus, otherwise they get nothing. (You will have to use absolute referencing to correct replicate this formula down the column).
4. Save your file.

1. Download the file computing grades.xls, and,
   
   
   
   
2. Enter t the correct formulae in the shaded cells. (Averages should be displayed with no decimal places shown). Column G should display a 1 if the average is greater than 70. Otherwise a 2 should be displayed.
3. Save your file.

3. Using the Rank Function

1. Download the file competition.xls, and,
   
   
   
   
2. Enter the formulae in column G to calculate total scores.
3. Explore the RANK function to display the position each competitor's finishing position in column I. Absolute referencing will be required when replicating.
4. Points in column K are found by subtracting 6 from the competitor's finishing position. However, this only works for the first 5 place. If a competitor finishes lower than 5th, they should receive 0 points.
5. Column M should display the qualifiers by showing a "Q". "NQ" means the competitor did not qualify. To qualify a competitor must have a total points score of greater than 31.
6. This is difficult and you may need to use the Help options.

Mark off Exercise 15, 16 and 17 in your Progress Grids

What you should now be able to do!

• confidently use the IF function in Spreadsheets to display different results according to the comparison made in the IF Statement. eg. IF(G4 >F5, etc)

• Use the RANK function to display the position of a value in a list of numbers..

Continue to Section 11: Case Studies