Spreadsheet Functions in Higher Apps
The following Excel functions are used at Higher Apps:
- SUM
- PRODUCT
- IF
- AND
- OR
- ROUND
- ABS
- INT
- AVERAGE
- MIN
- MAX
- MEDIAN
- COUNTIF
- STDEV.S
- PEARSON
You also need to know how to perform a Goal Seek, sort data, and produce diagrams.
Tasks/Ideas
- Leckie Higher Applications Student Book – Chapter 4 – Exercises A & B
- A lot can be learnt by playing around in a blank Excel file. Consider asking students to answer the following questions:
- Find the sum of the first 100 integers.
- Find the product of the first 30 odd numbers.
- Create a spreadsheet with the names and ages of everybody in your class. Add a column named “Can Apply For a Drivers’ Licence” and use the IF function to say whether they can or can’t. Use the COUNTIF function to work out how many people in the class can apply for a licence.
- Have students download the Starbucks Menu Information Spreadsheet. (Thanks to Kaggle). Students can answer any questions that occur to them. Some examples:
- What is the average amount of Fat?
- How many items have less than 300 calories?
- What’s the standard deviation for Fiber?
- Is there a correlation between Fat and Protein?
- What is the range of Calories? (There is no range function- how could MAX and MIN help here?)
- Produce a Scatterplot and/or a Boxplot.
- How many items have fewer than 320 calories AND more than 5g fibre?
- How many items have more than 300 calories OR more than 70g of Carbs?
- To understand PEARSON, have students plot a scatterplot and comment on the correlation (if any). Then have them use the PEARSON function. I recommend using the Bee Aware task, from the late, great Don Steward. (PDF version here)
Recurrence Relations
Tasks/Ideas
- Leckie Higher Applications Student Book – Chapter 4 – Exercise C
- Here’s a worksheet I’ve made of questions to help students practice this important skill: Recurrence Relations.