Using+Spreadsheets

= = flat = = =__**Learning Intentions:**__= Today you will use the **spreadsheets** you created to **analys**e the **data** you collected. In doing so you will revisit some **basic formulas**, create a **chart**, learn how to make different **formatting** adjustments to the sheet.

__Play Beat the Sheet!__ (Students to use calculators, computers and mental maths to try and beat the spreadsheet at calculating sums.)
 * Starter:**

=Uploading and/or Creating the Spreadsheet=

We will be using Google Sheets for this activity. If you did not use Google Sheets to create your spreadsheet, then the first thing you will need to do is upload your spreadsheet to Drive and open it with Sheets.

Make sure you are in your **ICT Folder** when doing so! That way the file will be created in the right place and you won't have to move it afterward.




 * IMPORTANT TO KNOW**: You can also upload files by dragging them and dropping them into folders.[[image:Screen Shot 2014-12-17 at 8.29.53 AM.png width="659" height="319"]]

You will see a **progress bar** at the bottom of the **window**. To open it in Google Sheets, simply click on the file (once it has finished uploading) and select **Open in Google Sheets** from the **top menu**.



If you haven't got a **spreadsheet** for the project, you will need to **create** one in your **ICT folder**, **rename** it and **share** it with your partner and your teacher.

Task 1:
Upload your sheet and open in in Sheets and edit your current sheet sharing settings.

=Data and Formatting=
 * Now the fun begins! **

You should have two columns in your spread sheet C**olumn A** and C**olumn B** One for expenses and one for earnings. Make sure they are labeled with headings in **Row 1** and then drag the blue **header line** down to separate the headings from the rest of the table.

You may also want to format the text in your headings.

I have chosen bold for my example.

Some other useful formatting tools to try out are the:
 * **Borders** || **Horizontal**
 * alignment** || **Vertical**
 * alignment** || **Text Wrap** ||
 * [[image:abcict/Screen Shot 2014-12-17 at 8.47.01 AM.png]] || [[image:Screen Shot 2014-12-17 at 8.47.12 AM.png]] || [[image:Screen Shot 2014-12-17 at 8.47.22 AM.png]] || [[image:Screen Shot 2014-12-17 at 8.47.31 AM.png]] ||

In many cases you will want to add two more columns to your table, one for expense descriptions and one for earning descriptions. We are going to do so and learn how to insert columns as we do. There are different ways of doing this. Here are two of them.

First select **Column A**. and click on the small arrow on the right hand side of the **column heading**. Notice all the options available. Select **Insert 1 left.**
 * Method One**



Select **Column A** and use the **Insert drop down menu** to select **Column left.** Now do the same for **Column B. Label** the new **column headings.**
 * Method Two**

You should now have a total of four columns.
 * Input** all of your expenses and descriptions as well as earnings and descriptions.

Depending on the type of records you kept during the project you will need to add a few more columns and possibly rename the columns you have created. Remember to use the two insert column options in doing so.

Have a look at the example below, but ideally you will have the original cost of each item and the resale cost with your markup. You will also need columns for the quantities you supplied and the quantities you sold.
 * Example**

Here is an example of a rather detailed table in the works. As you can see it is still incomplete and there are formatting issues.

The table is now slightly more complete. The next example demonstrates how to drag the column to the right to make it wider.

I have decided I want all the coffees at the top in the Item Sold Column. By selecting them sugar cookie and brownie cells using my **mouse pointer** and the **shift key**, I am able to grab them from the top and drag them to a new position.

Task 2:
Format your sheet headings, add the necessary columns and rows for the data you collected and input the data you have. Do not worry about the totals at this point. Leave them off of the sheet!

=Let the Spreadsheet do the Maths!=

We are now going to use the spreadsheet to work out our earnings per product. In doing so you can type formulas directly into the cell or use the formula bar. What you do in one is reflected in the other. You are able to select then cells you want included in the formula simply by clicking on them or selecting multiple cells called a **range**. In this case you will select cells in the price and quantity sold columns.

I have used the **asterisk** to tell the program that I want to perform a multiplication operation. Notice the opening and closing brackets and the equal sign. The **equal sign** tells the spreadsheet that I would like to enter a formula or function. The **brackets** are important separating out the **operations** you want to perform. Please remember when doing so the work with **order of operations** you have done in Maths.

You can now see I have my first item calculated.

If you look closely at the cell in **Column G** you will see a blue outline and a small box in the bottom right corner. You can use this blue box to drag the formula down to the cells below thereby assigning the formula to those cells too. This is called **autofill**. **What a time saver!!!**

There is something missing still! Two things really....the first is the currency!

To add a currency you use the **format menu**. First, you will need to select the column you would like to format. Next, you will need to select **number** from the menu. Next, select the currency you would like to **format** your **data** with from the menu. In this case you will use US dollars.



The next thing we need to do is work out our total earnings! Do so by adding inputing the equal sign followed by an opening bracket into the the cell below the last value. Next select all the values in the range.
 * Don't forget the brackets!!!**



Notice that in doing so the spreadsheet has added the entire range of cells for you.

You can perform a few more functions by summing the cost of all your coffee items and the earnings from all of your coffee items and comparing them.

You can place the total in any cell you like.




 * More formatting**

I have decide I want to format my totals. I have bolded the **headings** and **centred the cells.**

We have now used formulas for multiplying, and adding. Now we will perform a subtraction! Notice the total for earnings does not include our expenses. These are our Gross Earnings (also called **Gross Income**). We will work out our **Net Earnings** (also called **Net Income**) or earnings after expenses!

To do so we will subtract our **expenses** from our **gross earnings**. Notice that this time there is a **minus sign** in the formula!



We can also work out an average based on different collections of data. In the example below I have worked out the average earnings across all coffee groups. I have done this in two different ways. The first is using the average function, the second by using the forward slash as a division operator.In both cases the answer is the same.



Task 3:

 * Input the formulas to calculate **total expenses** and **total earnings** per item.
 * Use a sum formula to calculate the **Gross** earnings.
 * Use subtraction in a formula to calculate **Net** earnings.
 * Use either the **average function** or **division** in a formula to calculate the average earnings per group.

= = =Charts=

Our final step is to create a chart for our data. First you will need to identify what information you would like to chart.



Next select the Chart option either using the menu icon

or

the insert menu from the menu bar.

The Chart Editor will appear and recommend a chart for you.



You can click on the different options to modify your chart.

The **Chart Editor** offers even more options.



Once you have clicked the **Insert button**, you can move your chart around by dragging it from the top. **Resize** it using the corner **handles** and edit it, view it, delete it and more using the different edit options in the chart.

Here I have selected the option to edit the title.

Task 4:
Create a relevant chart which demonstrates price, quantity sold and earnings for each product. Format it and ensure that the information it shows is relevant and accurate. Add it to the sheet and share the sheet with your teacher if you have not already done so.