Objectives

Students will be able to:

  • use more complex functions to perform calculations
  • understand the idea of criteria
  • create a simple chart.

More complex functions

A B C
1 Country Item Cost
2 Somalia Clothes
3 Mozambique Baby Food
4 Bangladesh Office supplies
5 Somalia Office supplies
6 Rwanda Baby Food
7 Sri Lanka Personal care
8 Sri Lanka Clothes
9 Somalia Personal care
10 Summary Data
11 Item Total cost
12 Baby Food 0
13 Office supplies 0
14 Clothes 0
15 Personal Care 0

The spreadsheet above has been designed to allow users to add and edit data. It also has a Summary Data set which updates automatically.

For example, in cell c12 there is a function that does this:

  1. It looks in the item column in the range b2:b9
  2. if it finds the criteria Baby Food, it then finds the corresponding value in the Cost column.
  3. It then adds the value to the current sum.

In order for this function to work correctly it needs to know where to look (the range), what to look for (the criteria) and which range to find the corresponding sum value.

= sumif(range, criteria, sum_range)

In the above example, the function can be designed as:

= sumif(b2:b9,"Baby Food",c2:c9)

We could also do this:

= sumif(b2:b9,b12,c2:c9)

Thought: could we use copy down to complete c13:c15?



There are other spreadsheet functions that will do calculations based on criteria. If the criteria is found, the data will be processed.

Here is a starter video for today's  Task if you need it:

   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.It is data about orders from an international charity organisation based in Berlin, Germany.
Create a new column after Unit Price called Total Order Price.
Create a formula in cell J2 which will calculate the Unit Price multiplied by the Units sold. Copy down this formula for every cell in the range j3:j101
Create a summary data section to the right of the main data set. It should look like this:
At the top of the Total Price column, use a sumif function to calculate the total order price of all Baby Food orders in the spreadsheet.
Repeat this process for Cereal Food, Fruits, and Office Supplies. Double check your results are accurate!
At the top of the Units Sold column, use a sumif function to calculate the total units sold of all Baby Food orders in the spreadsheet.
Repeat this process for Cereal Food, Fruits, and Office Supplies. Double check your results are accurate!
At the top of the Average Unit Price column, use an averageif function to calculate the average unit price of all Baby Food orders in the spreadsheet.
Repeat this process for Cereal Food, Fruits, and Office Supplies. Double check your results are accurate!
At the top of the Number of transactions column, use a countif function to calculate how many times a Baby Food order was placed in the spreadsheet.
Repeat this process for Cereal Food, Fruits, and Office Supplies. Double check your results are accurate!
Throughout the model, format any currency data to be Eurodollars to 2 decimal places.
Create a Pie Chart which visualises the number of transactions per item in the summary data. Make sure each pie slice shows the item category and the value. Delete any legend from the chart.

Make sure all data in your spreadsheet model is visible.

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!!

Food For Thought

To be successful with a new concept in any technology, you should be comfortable with the vocbulary. Words like range, criteria and function will help you to describe what you want to say when talking about spreadsheet technology. Understanding these terms will also help you deal with instructions, too.

How would you describe what criteria means when discussing spreadsheet functions?

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.