Lab 1 Instructions-2

MATH 15 - LAB 1

Introduction to (PC) Excel 2016

Below are two data sets, one for the average retail price of gasoline in the State of California for each month in 2005, and the other for the average retail price of gasoline in the State of Texas for each month in 2005. This data is from the U.S. Department of Energy (units are dollars per gallon).

	California	Texas	Calif. + 3
Jan	2.016	1.773
Feb	2.163	1.841
Mar	2.346	2.008
Apr	2.596	2.169
May	2.52	2.088
Jun	2.41	2.101
Jul	2.559	2.227
Aug	2.721	2.446
Sept	3.032	2.843
Oct	2.926	2.72
Nov	2.57	2.204
Dec	2.319	2.161
SUM
MEAN
STDEV
MEDIAN
RANGE

1. Set up an Excel spreadsheet which will look like the rows and columns below.  You can type in the headings, the months, and the data or you can copy and paste the data in.  Make sure the top left corner of the table above is in the A1 cell of the spreadsheet.
2. Using the functions, SUM, AVERAGE, STDEV, MEDIAN, have Excel compute the sum, the mean, the standard deviation, the median, and the range of each of the columns of data.
   • To compute the sum of the California data, you need =SUM(B2:B13) in the B15 cell, if the "2.016" is in the B2 cell and "2.319" is in the B13 cell.
   • To compute the Mean, click on the B16 cell and enter =AVERAGE(B2:B13),
   • For the Standard Deviation click on B17 and enter =STDEV.S(B2:B13),
   • For the Median click on B18 and enter =MEDIAN(B2:B13),
   • For the Range click on B19 and enter =MAX(B2:B13)-MIN(B2:B13).
   • Copy the formulas in the B column and paste it into the C column to calculate the same values for the Texas data.
3. Create a 3rd data set in column D by adding 3 to each value in the California data set.  To do this in Excel:
   • click in cell D2 and type, "=B2+3" and click ENTER.  Then click once in cell D2 again and select Edit -> Copy.  Then drag your mouse over cells D3 down to cell D13 and select Edit -> Paste.
   • Compute the sum, the mean, the standard deviation, the median, and the range of this new data set. Copy the formulas in the B column and paste it into the D column to calculate those same values for the California + 3 data.
4. Compare the sum, the mean, the standard deviation, the median and the range of DATA SET B and DATA SET D.  What happened to these summary statistics when you added 3 to each observation from the California data set?  Explain and be specific.
5. In general, what happens to the mean, the median, the standard deviation, and the range of a data set when a constant value is added to each value of any data set?  Explain your reasoning.
6. Follow the instructions below to create a time-series graph of the California and Texas data.
7. Based on your graph, did the gas prices for the last few months of the year for both states appear to be increasing or decreasing?  Explain your reasoning.

What To Turn In:

• Prepare you answers to the three questions above.
• Copy and paste the Excel document including all data, the summary statistics, and the graph into a Word file or word processing file. 
• In this module there is a quiz entitled Lab 1 Answers.  There, you will submit the answers to questions 4, 5, and 7 of this lab.  You will also upload the file you prepared into this quiz. 

How to Create a Graph Using (PC) Excel 2016:

• Highlight the California and Texas data, including the column and row labels. Click on the INSERT menu tab at the top of the window, then click on the LINE CHART image. Click on the LINE WITH MARKERS chart type.

• Turn on the Axis titles by clicking on the “plus” symbol to the right of the graph.  Click in the axis titles and the main title fields to edit them appropriately:
  • Main Title: Retail Gas Prices
  • Horizontal Axis: Month
  • Vertical Axis: Dollars Per Gallon