Excel Spread Sheet Assignment

Chemistry 1151 Laboratory

Access the file called “Excel File Report Sheet” and open it.

Use the “Save As” feature and save it as another file to preserve the original.

Change the word “Science” in the Title to “Chemistry.”

Correct the name of the institution.

Insert both a date and time function in the appropriate spot and format the cell to see both. Use the NOW function and then format the cell so the date and time will be updated every time you open the document. Do not erase the words “date” and “time.”

Enter values for Lab 3. The lab is worth 10 points so insert this value on the top line and be sure your made-up values are all 10 or less.

For the first student, enter a formula for each of the following, and copy these formulas to all students.

Total Lab CreditWhich should be a sum of all three labs

Total Exam CreditWhich should be a sum of all three exams

Total PointsWhich should be a sum of Ex Cred, Tot Lab, Tot Exm and Final. (Notice that my Total Points do not include Ex Cred! You’ll have to fix the equation.)

Select all of the student data and sort so the students are listed alphabetically. Be sure to select (highlight) all the student data before sorting otherwise the data will become jumbled.

Insert a new column in the A position. Title this column as “Student #” and fill (use the autofill feature) in numbers for all students.

Insert a new row at the appropriate position and insert your name (last name first). Complete all the necessary data for yourself. Be honest. Don’t forget to include your IQ.

Now you will need to refill the student numbers in column A. You will also need to copy all formulas (look at copying columns K, L, N, O, P and Q) to your data row.

Determine and label the highest class average. Do this by using a MAX function. Place this function three rows below the last entry in the % column. Put the label immediately to its left.

Below the student names you will see the word “Averages”. Compute these for Lab1, Lab2, Lab3 (labs), Exm1, Exm2, Exm3 (exams), the final and the student %. (look at averaging columns D, E, F, G, H, I, M, & O). When computing averages, it might be necessary to format the cells to show a certain number of decimal places. This can be done by selecting the cells you wish to change, going to the Font selection from the Home tool bar ribbon and clicking on the small arrow at the bottom right of the Font box. There you will find the number tab and can then chose the number of decimal places that you would like displayed for the selected area.

Create a graph by plotting % vs. IQ. (Make % the x axis and IQ the y axis.) This graph should be, roughly, a straight line. To construct this graph, select the data for the % for the students by highlighting those values, then depress and hold the control key while you select and highlight the IQ values for the students. Once these two sections are highlighted, select a Scatter Chart under the Insert tab on the menu bar to construct the graph. The next step should then be to “move the chart” to its own page. Follow the instructions of “Directions for Graphing (Excel 2007)”. These can be found on the Chemistry Lab Website: http://www.clayton.edu/chemistry-physics/1151L (click on the link and then look at the bottom of the page). Include a computer drawn trendline, include the line equation and make maximum use of the graph paper. Complete the graph with the details discussed in class and with as much embellishment as you wish.

15. Be sure the page is formatted so it will print on one page. This is done by selecting Page Layout from the menu bar and clicking on the bottom right arrow on the Page Setup section of the Page Layout ribbon. Once there look for “scaling” and select a Fit to One Page option. The graph (chart) should also be its own separate page. Be sure you selected this as part of the Chart creation process.

16. Print a copy of both the graph and the spread sheet. You should be submitting only two sheets of paper. Be sure to label each with your name using a text box to enter your name in the upper right hand corner of both sheets.