Objectives

Students will be able to:

  • use basic functions to perform calculations
  • understand the concept of copy down
  • possibly create a simple bar chart.

Basic functions

A B C
1 First Name Last Name Points
2 Jill Smith
3 Eve Jackson
4 Adam Johnson
5 Total Points 68
6 Average 22.67
7 Max 32

The spreadsheet above performs 3 calculations. As each player's points is updated, the total, average and maximum number updates automatically. This is because cells C5, C6 and C7 contain basic functions. For example, cell C5 contains

= sum(c2:c4)

where sum() is the function and c2:c4 is the range of cells used by the function to produce a result.

*note: the function starts with an equals sign!

   Task

If you are using Google Sheets, use this sheet.

If you require a csv file to get started, download this one.

Follow these instructions to develop the spreadsheet model:

Open the spreadsheet model. There should be 6 columns and 37 rows of data (not including column headers). There should also be a summary data section.
Insert a new column between the Pay and Pay-Tax columns. Enter Tax as the column header in cell F1.
Enter 0.17 in cell F2. Format this as a percentage to zero decimal places.
Drag the handle in the bottom right corner of cell F2 to fill all of the other cells in the range F2:F38. This is called Copy Down.
Create a calculation in cell E2 to multiply the Hours Worked by the Pay rate/Hour. Copy down the calculation for all cells in the range E3:E38.
Create a calculation in cell G2 which is Pay - (Pay*Tax). Copy down the calculation for all cells in the range G2:G38.
In cell J3, use the count or counta function to calculate the number of employees in the spreadsheet data set.
In cell J4, use the sum function to calculate the total pay after tax in the spreadsheet data set.
In cell J5, use the average function to calculate the average pay after tax in the spreadsheet data set.
In cell J6, use the max function to determine the maximum pay after tax amount in the spreadsheet data set.
In cell J7, use the min function to determine the minimum pay after tax amount in the spreadsheet data set.
Format cells in the range A1:G1 to be bold, italic and red font color.
Format any currency data to be Euros (€) to zero decimal places.
Insert a new row at the top of the spreadsheet. Merge cells in the range A1:G1 and enter a title Adventure Holidays Paysheet, centre-aligned.
Format the summary data section so that borders are visible on all of the summary data and title cells.

Make sure all data in your spreadsheet model is visible.

Reproduce this chart. Note: your data will be different but the chart title and color, axis titles and color and bar colors should be the same.

Submit your final spreadsheet file as instructed. BEWARE of saving your work in a CSV file - all of your formatting, functions and chart will be lost!!

Extension activity

This letter needs to be completed by you.

Read it carefully and see if you can figure out what needs to be done.

You will need to create an image of your signature. Choose whatever software application you like to do this eg MS Paint, or an online drawing tool.

document specification

  • orientation: landscape
  • text: justified, size 14, double line spacing
  • today's date: italic
  • your name: bold, italic

Food For Thought

Popular spreadsheet software has functions that we can use to help us do calculations. sum, count, counta, max, min are all common functions. Remember to start with = and make sure you know what the range is!

Homework

Tags

spreadsheet data csv model merge insert row format currency column range decimal places cell reference

SUBSCRIBE

Join my mailing list to receive updates on the latest blog posts and other things.