Lab Test 2 MS Excel CS 101 PRACTICE 3
You may use any on-line help or printed manuals you wish.
You may not communicate with anyone during the test.
NO TALKING IS ALLOWED.
It is assumed that you can zip files and submit files
by yourself now. If you must ask for assistance with these on the lab test,
it will cost you 25 points.
- Create a folder on the Desktop- you choose the name of it.
-
Load in the spreadsheet from the link here,
and save it with the name YourName1.xlsx in your folder on the Desktop.
Then open it with MS Excel.
Make sure you have the sheet open in Excel, not in a browser.
When you are asked to write formulas in the problems below,
make sure you use cell references, NOT just typing in numbers.
That is, use =B5+C5 instead of =17+15, or even 32. Use the power
of the spreadsheet!
-
Insert a row at the very top of the sheet. Put your name into cell A1.
- Widen or reduce all columns so they are just wide enough for the widest
entry in them.
-
Fill in the column labelled Miles per Gallon
with formulas for all vehicles.
These formulas should figure
miles per gallon by
subtracting Start Miles from End Miles and dividing by Gallons Used.
Use an absolute reference for the column part of the
Gallons Used cell references in your formulas in this column.
-
Fill in the Rating column for all vehicles
with formulas that use the IF function. This function should
display "Good" if the Miles Per Gallon value is
greater than 15 and "Poor" otherwise.
NOTE: these values must have quotes around them when entered
into the formula. Otherwise you get a NAME error because they
are not formulas nor the names of functions.
-
Save the sheet as YourName1.xlsx.
-
Create a clustered column chart of the values in
Miles per Gallon column
using the names in column A as Category labels on the X axis.
Put a title on the chart, "Miles Per Gallon"
-
Add one more row at the bottom of the list
with the info:
Jeep, 23839, 24182, 10.1
and copy the formulas into its Miles per Gallon and Rating cells.
-
Include the new row of data in the chart.
-
Format the Miles per Gallon column so that it has two decimals in each entry.
- Create a 2-D pie chart for the data in the Miles Per Gallon column
using the data in column A (Vehicles) as the labels on the slices
of the pie.
- Put a title on the chart, "MPG".
- Put the chart on a new sheet.
-
Save the sheet as YourName2.xlsx.
- Put a label in cell G3 that says "Largest MPG".
-
Put a formula in cell G4 that shows the largest value of the Miles per Gallon column.
- Put a label in cell G6 that says "Lowest MPG".
-
Put a formula in cell G7 that shows the smallest value of the Miles per Gallon column.
- Put a label in cell G9 that says "Average MPG".
-
Put a formula in cell G10 that shows the average value of the Miles per Gallon column.
- Spellcheck the sheet and correct any true misspellings.
- Record a macro that formats the cell B23 to have 5 decimals.
Record it with "Uses Relative References" turned on so that it will
work anywhere in the sheet.
- Display the sheet "with formulas". Turn the mode back to normal.
- Save the sheet as YourName3.xlsm.
- Close Excel.
Close all Excel files before zipping.
Files should be zipped as usual and submitted to Canvas.
Delete your folder and all your files from the Desktop.