🎯 Learning Objectives
- Analyse data
- Use the functions SUM, MAX, MIN, and COUNTA in a spreadsheet
- Create appropriate charts in a spreadsheet
💬 Key Vocabulary
- chart
- pie chart
- bar chart
- series
- axis/axes
- labels
- function
- maximum
- minimum
📖 Starter Activity – Cake!
Click the link below which will take you to a shared Excel spreadsheet. You need to put your initials into the column of your favourite cake, see what happens with the chart on the right as people vote.
What type of chart is it and how do you think it works?
📖 How do we make a chart?
- As the saying goes, “a picture is worth a thousand words”. Charts are a way of visually representing data so that is easy to understand.
- It is far easier to look at the lines or bars of chart to see what the data is showing than reading rows and columns of data.
- The pie chart from the starter activity is based on how many entries there are in each column, which is calculated automatically using something called a function.
- Charts and functions are both examples of the what’s involved in the process we call “analysing data”, which means making sense of data to find out information.
📖 Analysing Data
- To help you analyse data in Excel you can functions, like the one that made the pie chart.
- Each function is like a different superpower.
SUM
Superpower: superfast totals!
=SUM(B2:B15)
This means add up all the cells between B2 and B15.
MAX
Superpower: find the largest value even with your eyes closed!
=MAX(B2:B15)
This means find the maximum value in the range B2 to B15 and display it.
MIN
Superpower: find the smallest value even with your eyes closed!
=MIN(B2:B15)
This means find the minimum value in the range B2 to B15 and display it.
COUNTA
Superpower: count cells that are not blank.
=COUNTA(B2:B15)
This means find how many cells are not blank in the range B2 to B15 and display how many.
📝 Level 1/2 – Party Planning
Imagine that you are planning a party and need to figure out what ingredients you need for the cake and how many party supplies you’ll need. Download the spreadsheet below and follow the instructions for Level 1/2.
📝 Level 3 – Analysing your data
Last lesson you created a survey using Microsoft Forms and collected data from your classmates. Today you will analyse that data, so you need to go to the Forms website and open the attached Excel sheet by clicking “View Responses” in the top right.
If you were absent last lesson or did not manage to collect any useful data, then download the Excel file below which includes data you can use.
Once you have your data you need to use the functions listed above as well as charts to analyse and visualise your data. Your teacher will show you some examples of what you could do.
You must use the SUM, MAX, MIN, COUNTA functions and also create at least two charts based on your data.
In this lesson, you…
- Analysed data.
- Created charts.
- Used functions.
Next lesson, you will…
- Analyse more data.
- Use a spreadsheet to sort and filter data.
- Use the functions AVERAGE, COUNTIF, and IF in a spreadsheet.
🏅 Level up
🥇 Level 1
- You must complete the Level 1.1 – Recipe ingredients and Level 1.2 – Inside one cake spreadsheets in the Level 1/2 Excel file.
- Upload the completed file to the Teams assignment.
🥈 Level 2
- You must complete the Level 2 – Party shopping list spreadsheet in the Level 1/2 Excel file.
- Upload the completed file to the Teams assignment.
🥉 Level 3
- Complete the Level 3 task by analysing either the data you collected last lesson or the Sports Day data given to you.
- You must use the SUM, MAX, MIN, COUNTA functions and also create at least two charts based on your data.
- Upload the completed Excel spreadsheet to the Teams assignment.