Lab Test 2 MS Excel CS 101 PRACTICE 2
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.
The time penalty for the actual test is as usual. Every minute
past the deadline for the test will cost you 10% of the possible 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!
-
Fill in the column Estimated
with formulas that assume each experimental subject
grows at a rate given in the Growth Rate cell.
That means that much increase should be added to the value
in the First Reading column.
Use absolute cell reference for the Growth Rate cell value.
-
Format the Estimated column so that it shows three
decimal places.
-
Format the Growth Rate cell (A13) so that it shows as a percentage.
-
Put formulas into the Warning column that
use a function to determine if the Second Reading value
is greater than the Estimated column. If it is,
the cell should have the value "too fast", if not, it should
say "ok".
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.
- Enter formulas at the bottom of the two columns
of data "First Reading" and "Second Reading" that give
the averages of each of the columns. You must use a statistical
function and a range for this. They should be in row 10,
just below the end of the data.
-
* Create a line chart of First Reading and Second Reading data
(two lines), using column A, Subject, as labels
on the X axis.
Put a title on the chart, "Experiment 1".
Put it on the same sheet, towards the bottom of the screen.
-
Save the sheet as YourName1.xlsx.
- Save the sheet again as YourName2.xlsx.
-
Inset one row at the bottom of the list
with the info: Type E, 45 (First Reading), 50 (Second Reading)
and copy the formulas into the Estimated
and Warning
cells. This data should be included in the formulas for averages.
- Note how this insertion has changed the formulas for
averages. Do you need to correct the ranges? if you need
to, do it.
-
* Include the new row of data in the chart.
- Create a 2-d pie chart for the data
in "First Reading" using the data in column A as the labels
on the slices of the pie.
- Put a title on the chart, "First Readings".
- Put the chart on the same sheet,
putting the upper left corner of the chart in cell A18,
sizing it so that it covers the columns from A through F.
- Save the sheet as YourName2.xlsx.
- NOTE: because you are going to put a macro
in the sheet now, you will have to
save it as an "xlsm" type sheet.
Save the sheet again as YourName3.xlsm.
You have to use "Save As".
- Format the headings, "Subject", "First Reading", etc. as
bold and center them in their cells.
- Change the font size of the main header, "Experimental
Data" to 22 points.
- Display the sheet "with formulas". Turn the mode back to normal.
- Spellcheck the sheet and correct any true misspellings.
- Record a macro that copies all the data in row 7 and
pastes it to row 17. The macro name should be Macro1.
Record it with "Uses Relative Reference" turned on
so that it will work anywhere in the sheet.
- Save the sheet again as YourName3.xlsm.
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.
The lines marked with * are the ones that most
often cause people problems.