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!

• Widen the columns so they are just wide enough for the widest data in the column.
• Fill in the column Hits per Bat with formulas for all players. These formulas should figure the hits per bat by dividing Number of Hits by the Times at Bat. Format this column so that it shows a percent sign and two decimal places.
• Fill in the column Rating with formulas for all players using the IF function. This function should display "Good" if the Hits per Bat value is larger than the value in the cell under "Good Average", and "Poor" otherwise. Use absolute addressing in these formulas for the cell reference for "Good Average" (right now, A 12). 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.

• * Create a clustered column chart of Hits per Bat, using column A as labels on the X axis.

  Put a title on the chart, "Hits Per Bat". Place the title either on the chart or above it, your choice. Put the chart on the same sheet, towards the bottom of the screen.
• Save the sheet as YourName1.xlsx.
• Now do a "Save As" as YourName2.xlsx.
• Add one more row at the bottom of the list with the info: Sanderson, Paul, 40, 55 and copy the formulas into the Hits per Bat and Rating cells.

• * Include the new row of data in the chart.

• Make a 2-d pie chart of the data in the "Number of Hits" column using the names in column A as labels for the slices of the pie.
• Put a title on the chart "Hits". Place the chart so its upper left corner is in cell A18. Size it so that it covers the columns A through F.

• * Delete one row, the one with Johnson in it.

  That is, totally remove the entire row from the sheet, not just make the cells blank. Make the row go away.
• Put a formula to show the current date in cell H4. Use a function for this. Format it so that it shows only the month, day and year.
• Display the sheet "with formulas". Turn the mode back to normal.
• Save the sheet as YourName2.xlsx.
• NOTE: because you are going to put a macro in the sheet now, you must do a "Save As" with the file type xlsm, not xlsx. Do a "Save As" as YourName3.xlsm.
• Spellcheck the sheet and correct any true misspellings.
• Format all the labels at the top as bold and centered in their cells. You may have to resize the columns because the letters are a little bigger.
• Record a macro which copies all the data in the second column (B) and pastes it into column J. The name of the macro should be Macro1. Record the macro with "Uses Relative Reference" turned on so that it will work anywhere on the sheet.
• Save the sheet with the name YourName3.xlsm.

Close all Excel files before zipping. Files should be zipped as usual, and submitted with Canvas as usual. Delete your folder and all your files from the Desktop.

The lines marked with * are the ones that most often cause people problems.