September 15, PRR 475 LAB Week 3: Excel Basics
In lab today we will introduce basic spreadsheet procedures and get you started on two exercises (3.1 and 3.2.) First, I'll demonstrate some commands on the computer skills data that you submitted.
Computer skills data file
The data you entered in the on-line computer skills form were submitted to an Access database. I then copied this table into an Excel file, Stripped off names (confidentiality), and Reordered fields (columns) to put them in declining skill order. You may examine resulting file : prr475compskills0.xls in PRR 475 course labs99 subdirectory.
1. In explorer navigate the U drive to msu/course/prr/475/labs99/prr475compskills0.xls and double click it. OR Start Excel and open this file.
Spreadsheet skills: here's a more specific definition of the self-ratings for spreadsheets. Today we'll cover the skills listed under fair and good except for charts which is next week. In two weeks we will cover database procedures.
Fair : Moving around and entering information, formatting and standard toolbar procedures
Good: Solid grasp of absolute and relative addresses, using & copying formulas, simple functions, charts
Strong: Database procedures, pivot tables, more functions, converting tables to web, macros
Structure of the Data
The Excel file is a table with the variables (fields) running across columns - variable names at top in first row
Students are the rows - each row is response of a single student. Refer to a cell by its row and column address (A3)
Note that most variables have a numeric coding so you need a "codebook" or scorecard here to interpret it.
2. Let's add an identifier column since I stripped off names
2.1. Select the entire first column by left clicking mouse over the A column label - highlights the entire column
2.2. Right click mouse over this selection, choose INSERT - will create new blank column in A and shift rest right
2.3. Enter name for column - say ID in cell A1 - always label columns and rows
2.4. Now label the rows consecutively starting with SN1 for student number 1, SN2 for student number 2, etc. - demonstrate drag and fill - select cell with SN1, grab small box in lower right corner (becomes a +) and drag down.
To use spreadsheet for this evaluation study
First, ask yourself what kinds of questions can be answered with the data. Note that the intended uses of the information should be considered when designing the information being gathered on the form.
Then must know how to carry out procedures in Excel to answer the questions.
General suggestions
Create summary measures for the whole class - useful for comparing classes
Compute aggregate scores for individual students - to compare individuals, maybe rank them by skill
Relationships - which subgroups of students have stronger or weaker skills? - market segments?
Compare subgroup averages or distributions
3. Demonstrate a few procedures to carry out these analyses
3.1. Averaging scores down each column. At bottom of a nge). Copy formula across to quickly get all the averages.
3.2. Naming ranges - select the range to name, on menu bar, choose Insert, Name, Define - enter a name. This name will now appear in list in Name box on formula bar and can be used in any formula or cell reference. E.g. lets call the column of spreadsheet ratings (E2:E44) - "spread"
3.3. Frequency tables - how many students rate none vs fair vs good for spreadsheet skills - COUNTIF function. (=COUNTIF(range, value to count) . Enter the first formula to count zeros in spreadsheet column. =COUNTIF(E2:E44, 0) OR COUNTIF(E$2:E$44,0) OR COUNTIF(spread,0). Repeat or copy formula to count 1's, 2's, 3's and 4's too. Now compute the percentage for spreadsheet ratings from this frequency table. (Relative address revisited)
3.4. Compute a computer skills score for each student as an average or total across the 8 items (use of simple functions). Insert a new column between stat and statk columns. Label this SCORE. In row 2 enter formula =SUM(B2:I2). Copy this formula down the column to get scores for each student.
3.5. Speed sorts to rank students. Let's rank students on SCORE. Select a single cell in SCORE column and click speed sort button on toolbar (Z to A to put highest scores at top).
3.6. Relationships between variables - Pivot tables for "crosstabulations". Example - do women or men have higher computer skills based on these self-ratings). Database commands - First be sure you have a blank row between the last student in list and any averages or counts that you added. Also there should be no blank columns in the middle of your data. Now select a single cell inside the data area. On menu, choose Data, Pivot table and see if you can complete the Wizard. Choose Next to move ahead, then drag variable names to Pivot table row, column or data to create the table. E.g., Drag gender to row; drag spread to column and drag ID to data area. Then right click data and choose "count of ID". Click finish to complete your table. This is a crosstabulation showing the number of men and women with each rating.
You can also generate average scores for men and women as follows: drag gender to row and drag spread to data section, right click and choose average. Then finish. Here you are comparing means of two subgroups.
Now try some of this yourself:
Complete Exercises 3.1 and 3.2 on your own (ask for help as needed). Supporting files are accessible from the course lab page. They include worked out solutions and tips, but first try this yourself.
Exercise 3.1. - ex3grade.xls
Exercise 3.2 - ex3hcma.xls.
More Excel help if needed in word file labex3.doc
EXERCISE # 3.1 .Spreadsheet Basics. This is a simple exercise to practice the spreadsheet basics. I've also thrown in a small twist at the end requiring some familiarity with weighted averages. You may find a worked out solution with notes in file ex3grade.xls.
Grades for a class of ten students are to be based on 3 exams. The table below gives grades for each student on the exams. Maximum points are: Exam 1 = 50 pts, Exam 2: 25 points; Exam 3: 100 points.
Number / Student / Exam 1 / Exam 2 / Exam 31 / Bristor / 35 / 20 / 65
2 / Koufax / 45 / 18 / 90
3 / Jones / 43 / 14 / 85
4 / Smith / 32 / 24 / 76
5 / Puckett / 47 / 21 / 87
6 / Mahoney / 22 / 16 / 56
7 / Clinton / 34 / 18 / 80
8 / Bush / 32 / 23 / 80
9 / Perles / 40 / 20 / 67
10 / Fergy / 48 / 17 / 95
______
1)Set the information up on a spreadsheet (Moving around and entering information). Add a title at top of table. If you didn't leave room, select the first row, right mouse click over the selection and choose insert row.
2)For each student, calculate the sum of their raw scores on the exams (formula, = SUM function), putting this in a new column labeled TOTAL. Use the Edit, Copy procedure or drag fill handle to copy formula down the column after entering the first formula. Try the Autosum feature.
3)Compute the class average on each exam, also the standard deviation (= STDEVP) of scores. Put these in new rows at bottom and label appropriately.
4)Save your spreadsheet ( File, SaveAs, save to your AFS space as EXER31 - Excel will add an XLS extension for Excel worksheet files). Now Close the spreadsheet in memory (FILE CLOSE).
5Retrieve the file (FILE OPEN point to EXER31.XLS). Note Excel keeps track of last four files you worked with and you can simply click on one of these at bottom of list.
6Print the table (Click on Printer in Toolbar or File, Print , check Print Preview. Try some formatting procedures to polish up the look of the table if needed.
7)Clinton argued successfully for 5 more points on last exam. Recompute his grade. (This should be automatic - simply edit his raw score).
8)Set up another table below this one on the spreadsheet to put percentage scores into. In this table, calculate the percentage score for each student on each exam. Also compute percentages for the total column. Format these cells as percents. Copy the row and column labels to this table. (TIPS: Quick way is to copy whole table and then delete entries in middle or replace these with formulas. The percentage score is simply the raw score divided by maximum points on the test. Best approach is to put the maximum point totals for each exam in a new row and then divide by these cells to compute percentage scores for each student. Be careful in copying formulas - you will need to use absolute addressing (add $ to row or column label in cell you copy from). Print out just the percentages table (Select the cells in this table, then in menus choose File Print, Selection).
9)Weighted averages.(If you need help with weighted averages, look at the wtavg.xls file). Figure out grades if the exams are weighted equally vs if final exam is worth 50% and the other two 25% each. (HINT: Since scores in percentage table are all based on 100%, tests will be weighted equally if you simply compute the average of three scores for each student. To weight exams differently compute a weighted average,
i.e. SCORE= .25 *TEST1 +.25*TEST2 + .50* TEST3.Insert this as a formula. As above, good spreadsheet practice is to add a row with the weights and refer to these cells in your formula, i.e. if weights are in row 40, the first student test scores in row 15, and test scores in columns B, C and D, the formula for first student is SCORE= B15*B40+C15*C40+D15*D40. If you want to copy this formula down the column, you will need to add a $ to fix row address for weights in row 40, SCORE= B15*B$40+C15*C$40+D15*D$40.
Exercise 3.2.Formatting tables. Load the file ex3hcma.xls containing park use data for Huron Clinton Metroparks for 1994 by month and park. Use the formatting toolbar to polish up table and add row and column totals and percentages. Guidelines for formatting and sample answers are included on separate pages of this workbook. Fix up table on first page and check it against the sample answer on second page. Print out the table to fit on a single page (use landscape).
Next week turn in : a printout of the two tables for Exercise 3.1 (fit these on single page) and your reformatted table for Exercise 3.2 (fit on single page - print in landscape). The graphing elements on the sample spreadsheets are optional practice for next week. Before next week try the cbtraining Intermediate Excel module that covers charts. Then practice some charting on your own.