nra

=Lesson Objectives =  By the end of this lesson Students will
 * Know how to create a formula/function
 * Know how to create a graph
 * Know how to sort data
 * Be able to use a range of spreadsheet functions
 * Understand basic spreadsheet terminology

==Starter == Look at the spreadsheet word cloud and think about which of these terms you know about. Circle each of the spreadsheet terms you are familiar with.



Task
 Click to open the spreadsheet below in Excel



Task
Type your name in cell E2

Starting in Cell E4 add the following labels


 * Total
 * Average
 * <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: pre-wrap;">Max
 * <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: pre-wrap;">Min

==<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Activity == <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">In cell F4 use the AutoSum button to calculate the total. You will need to highlight the range of numbers in cells C4 to C33. This creates a formula using the SUM function. The SUM function is used to add up the total for a RANGE of cells. Take a quick look at how the function is made up. <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"><span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> = <span style="background-color: transparent; color: #ff0000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">The equals sign tells the spreadsheet software you are starting a formula <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"> <span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">SUM <span style="background-color: transparent; color: #ff0000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">SUM is the function - it tells the spreadsheet software to add up <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"> <span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">( <span style="background-color: transparent; color: #ff0000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Opening bracket - you put brackets around a RANGE of cells  <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"> <span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">C4 <span style="background-color: transparent; color: #ff0000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">The first cell in your RANGE <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"> <span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">: <span style="background-color: transparent; color: #ff0000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Colon separates the two parts of your RANGE <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"> <span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">C33 <span style="background-color: transparent; color: #ff0000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">The last cell in your RANGE <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"> <span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">) <span style="background-color: transparent; color: #ff0000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Closing bracket - to show you have ended the RANGE of cells

<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> For the next 3 functions you are going to have to type in the formula. The first function is the AVERAGE function. Start the formula
 * <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">=AVERAGE **

<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">You have told the spreadsheet that you are starting a formula and want to find an average. Next you have to specify the range, it will be the same range you totalled. Remember it has to be enclosed in brackets. Add the range to your formula <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">**=AVERAGE(C4:C33)** <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> When you press Enter your spreadsheet will display the average, but it will give a decimal point and you can't have part of an egg! <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Tell Excel you want to round your number as follows


 * 1) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: 22px; white-space: pre-wrap;">Right click
 * 2) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: 22px; white-space: pre-wrap;">Format Cells
 * 3) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: 22px; white-space: pre-wrap;">Number
 * 4) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: 22px; white-space: pre-wrap;">Take decimal places down to zero
 * 5) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: 22px; white-space: pre-wrap;">Click OK

<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">The last two functions are done in exactly the same way - this time the function names are MAX and MIN. Again we are using the same range of cells. <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> Type in the function to calculate the maximum number of eggs
 * <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: 22px; white-space: pre-wrap;">=MAX(C4:C33) **

<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Now type in the function to calculate the minimum number of eggs
 * <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">=MIN(C4:C33) **

<span style="background-color: transparent; font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: normal; white-space: pre-wrap;">Activity
<span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; line-height: 22px; white-space: pre-wrap;">Of course - as you all know from your maths lessons there is more than one way to calculate an average. The AVERAGE function has calculated the MEAN. We are going to sort the data now to manually calculate the Median and the Mode.

<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> Look at your sorted data and type the Median and the Mode in the correct cells. <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"> Once you have done this, sort again - this time by column B to put the data back into date order
 * 1) <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; list-style-type: decimal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Using the mouse highlight all of the data in the range of cells B4:C33 (the dates and the numbers)
 * 2) <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; list-style-type: decimal; text-decoration: none; vertical-align: baseline;"><span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Pull down the <span style="background-color: transparent; color: #000000; font-style: normal; vertical-align: baseline; white-space: pre-wrap;">__D__ <span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">ata menu
 * 3) <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; list-style-type: decimal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Select Sort
 * 4) <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; list-style-type: decimal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Choose to sort by column C

==<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Task == <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Our final use of the data will be to create a graph - what kind of graph would best display this data?

> > > > >
 * 1) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: normal;"> Highlight the data range (B4 to C33)
 * 1) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: normal;"> click the chart wizard button
 * 1) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: normal;"> Choose the graph or chart you think will best display the data
 * 1) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: normal;"> Use the chart wizard to help you create your graph
 * 1) <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: normal;"> Choose to put it on a New Sheet.

//**<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Extension task **// <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">In cell G4 add a formula to calculate how many boxes of eggs you have collected. Each box holds 6 eggs. <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">To calculate this you will need to divide the total number of eggs by 6. <span style="font-family: Arial,Helvetica,sans-serif; font-size: 110%;"><span style="background-color: transparent; color: #000000; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">The total is in cell F4 You use a / <span style="background-color: transparent; color: #000000; font-style: normal; line-height: 19px; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">to divide

==<span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Plenary == <span style="background-color: transparent; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Look again at the spreadsheet word cloud. Circle all of the terms you are now familiar with.

<span style="background-color: transparent; font-family: Arial,Helvetica,sans-serif; font-size: 110%; white-space: normal;"><span style="background-color: #ffffff; color: #000000; font-family: Arial,Helvetica,sans-serif; font-size: 110%; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">How many have you learned this lesson?