BUSA 3110
Excel 1 - Homework
The following data is from my car. The data represent the 15 tanks of gas that I purchased and the
miles that I traveled with that gas.
Date Miles Gallons
Oct. 1 243 11.201
Oct. 8 211 9.334
Oct. 15 339 14.434
Oct. 22 315 13.000
Oct. 29 266 12.310
Nov. 5 273 12.305
Nov. 12 306 14.121
Nov. 19 220 10.574
Nov. 26 219 9.764
Dec. 3 260 11.727
Dec. 10 266 11.821
Dec. 17 250 11.478
Dec. 24 268 12.342
Dec. 31 266 11.996
Jan. 7 245 11.324
1. Open an Excel Spreadsheet and label
Columns A-C: Date, Miles, Gallons
2. Enter the data into the Excel Spreadsheet
so that the first column contains the date, the
second column contains the miles traveled on
that tank of gas, and the third column
contains the gallons consumed.
3. Right justify all entries.
For example, if the first tank had been
used to travel 284 miles and I had
used 9.579 gallons, the first two rows
of your spreadsheet would look like:
Date Miles Gallons
Oct. 1 284 9.579
4. Label Column D MPG. Use a formula to
convert the miles and gallons data into miles
per gallon data.
5. In Row 18, use a function to determine
the total number of miles driven and the total
number of gallons consumed.
6. Use Descriptive Statistics from the Data
Analysis Tools to determine some summary
statistics that describe the number of miles
that I tend to travel between gas purchases.
7. Have Excel calculate the average MPG.
Do this by using a formula that takes the
total miles driven and divides by the total
gallons consumed. Put the result in cell
D18.
8. Label Column E Average. Enter the
average MPG in each row of the column.
9. Use Chart Wizard to create a Run Chart
including a center line (at the height of the
average). Get rid of any chartjunk. Have
the date of purchase show on the horizontal
axis.
10. Use Custom headers to put your name
on the spreadsheet.
11. Arrange the data and graph so that they
will fit on a single page. Get a printout of
this page.
12. Using a pencil (or pen), write the Excel
commands that you used to calculate the
first MPG, the total miles traveled, the total
gas consumed, and the average MPG.
Write these on the printout next to the
corresponding values.
13. In cell D19, use a function to average
the 15 MPG values. Write the command
that you used next to the result. Note that
the result is different from the result in D18.
Explain when each approach to calculating
average MPG would be appropriate. (i.e.,
under what conditions should you use total
miles divided by total gallons, and under
what conditions should you use the average
of the mpg for each tank?) Write (legibly)
your answer on the Excel spreadsheet.