Lab Test 1 Windows / MS Excel CS 101 PRACTICE #1

This is practice for the first Lab Test. The instructions on the lab test will be similar. You should practice these tasks until you can do them in roughly half an hour - you will have 50 minutes to do the actual test and zip it and submit it. 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 during the actual test. (Of course during the practice, you can talk to your neighbor or your TA as desired.) If you get stuck on a task, you should not spend all your time on it. You can still get points for doing the other tasks.

Practice with Excel

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! A formula like =B5+17 is ok, if that is what the problem specifies (in other words, constants are ok to use, but not JUST constants).

Create on your Desktop a path that would look like "\practice\mytest\YourName\firstone\". Use it to store the files created below. You will have to create a folder nested inside a folder.
Load in the spreadsheet from the link here, and save it with the name YourName1.xlsx in your firstone folder. Then open it with MS Excel. Make sure you have the sheet open in Excel, not in a browser.
Make all columns 13 columns wide. Make column G 20 columns wide.
Fill in the column Bases per Time at Bat with formulas for all players. These formulas should figure the bases earned per bat. Each Single is one base, each Double is 2 bases, each Triple is 3 bases and each homerun is 4 bases. This sum needs to be calculated and then divided by the Times at Bat. Format this column so that it shows two decimal places. This number is called a "Slugging Average". For example, for the first player "Johnson", his slugging average would be (10 + 11 *2 + 17 * 3 + 5 * 4) divided by 48. (2.15) This formula would be =(C5 + D5*2 + E5 * 3 + F5 * 4)/B5
In the Bonus Earned column, put formulas that will give each player a bonus payment for every homerun they hit. The amount of the bonus is in cell J4. Format this column as currency. You must use absolute reference in these formulas for exactly one cell in each formula. Write the formula so that it can easily be copied down the column without having to be edited.
Put a label in cell A13 that says "Best Hitter"
Use a statistical function in a formula in cell B13 that reports the highest "Bases per Time at Bat" value of all the players.
Put a label in cell A14 that says "Worst Hitter"
Use a statistical function in a formula in cell B14 that reports the lowest "Bases per Time at Bat" value of all the players.
Use a statistical function in a formula in cell B15 that finds the average "Bases per Time at Bat" value.
Insert two rows at the very top of the sheet. Put your name into cell A1.
Change the name of the Sheet1 worksheet (on the tab at the bottom) to "The Team".
Save the sheet as YourName1.xlsx.
Do a "Save As" on the sheet as YourName2.xlsx (This is done so that your work up to this time is saved in a sheet, YourName1.xlsx, and the next steps will be saved in the next sheet, YourName2.xlsx. Each sheet is cumulative).
Insert one more row at the bottom of the list of players with the info: "Sanderson, Paul", 40, 13, 15, 5, 6 and make sure the correct formulas are filled in in his row.
*(difficult) Delete one row, the one with Samuel in it. That is, totally remove the entire row from the sheet, not just make the cells blank. Make the row go away. Make sure the formulas still have the correct ranges in them.
Sort the player data by the Doubles column (ascending) (from smallest to largest). You should not move the headers at the tops of the columns (the labels). All the data pertaining to one player should stay with that player's name.
Center and merge the name of the team ("The Screaming Pigeons") over the columns B through H. Make the name of the team bold and 18 points tall.
Put a formula using a date function to show the current date in cell B1. Format it so that it shows only the month, day and year.
Freeze the sheet at cell B6. Freeze both rows and columns. Show that it is frozen by scrolling.
Save the sheet with all changes as YourName2.xlsx. Close your sheet but don't close Excel.
Create a new sheet and create the following errors:
- a #DIV/0! error (in cell A1)
- a #NUM! error (in cell B1)
- a NAME? error (in cell C1)
- a cell that displays #### (in cell D1)
- a circular reference (using cells E1 and F1)
Save this file with the name YourName3.xlsx in the same folder as the other sheets. Close Excel.

Practice with File manipulations in Windows

Make a copy of the file just saved, and give it the name "YourName4.xlsx", in the same folder. Copy it by click-and-dragging (drag-and-drop) the file icon. Hint: use the Ctrl key while dragging.
Copy the file again, by right-clicking the icon and using the menu to Copy and Paste, and change its name to "YourName5.xlsx". Put it in the same folder as the other files.
Now you should have five files: YourName1.xlsx, YourName2.xlsx, YourName3.xlsx, YourName4.xlsx, YourName5.xlsx. Make sure all filenames have the correct extension, .xlsx. They should not have names like "JohnSmith.xlsx.xlsx" or "JohnSmith..xlsx" or "JohnSmith.xlsx.docx"!
Create a zip file that contains the folder that contains the folder that contains the folder that contains all these files. Name the zip file YourName.zip. This zip file should be on the Desktop. Note: On the first lab test, the instructor will be allowed to help you if you have trouble with zipping or submitting. After the first lab test, you are expected to be able to use both programs without assistance. The instructor will not be able to help you zip or submit during lab tests after the first one without costing you a substantial penalty.
At this point you would use the Canvas link to submit the file during the actual test. If you get a message about submitting a file that is very small, make sure you have the correct, complete Path to your file in the appropriate blank in the form, and try again. Also make sure you have indeed created a zip file with the xlsx files in it. You can submit a file as many times as you wish. The last one submitted is the one that will be graded.
Delete (erase, remove) your folder and all your files from the Desktop. Also empty the recycle bin. If you saved work on any other drives (you are not supposed to!) erase it there too.
Using the Help system of Windows, find the help on "how to create a new folder".
You have one window open with these instructions in it. Open a browser window and enter the URL of the class web page. Can you find your TA's email address? Switch back and forth between the windows. This can be a useful skill on a lab test. You can keep the test instructions open in one window and a reference page open in another.