objective:

The objective is to learn how to use Excel as a grade sheet and to use it more effectively and efficiently.  Included in this basic goal is learning how to set up Excel for different grading systems so that no matter how you grade, Excel is doing the work.

** These materials -- which are updated as I learn about new developments -- are available at http://faculty.eicc.edu/golson/excel.htm.  If you have something to add, please send me a note and I will be happy to include it.  If you need additional help contact me at golson@eicc.edu or 563-336-5263.

agenda:

1. sample grade sheets
2. starting with the basics -- sums, averages, removing low grades
3. calculating grades using lookup and other methods including rounding
4. weighting grades for analysis
5. creating a printable student report using transpose
6.  
7.  
8.  Questions?  Contact me at golson@eicc.edu or 563-336-5263

sample grade sheets

Here are some sample grade sheets that you can download and use for your class.  To obtain the grade sheet, right click the sample you wish to use:

Sample A -- Uses inline processing of grades and transpose function for student report. Includes the use of multiple sheets, weighted grades, and automatic assigning of letter grades.

Sample B -- Provides charts that are used for processing grades and includes weighted grades, assigned letter grades, percentiles, rankings, and more. (This comes from the University of Calgary but has been modified for 100 point scale.)

Sample C -- Provides a chart to track attendance, grades, and exams. (Thanks to Red Mayo for this example.)

adding or averaging scores

question:  How do I add (or average) together scores in my grade sheet?

Here's how you create a total of all the scores for your grade sheet.

1. Click into the cell where you want the total to appear when you are done.
2. Next click into the function entry box at the top of screen (under the menu bar and buttons).  Type in =sum(  .
3. Select the cells that you want added together into the total.
4. Click Enter.  Excel will complete the formula for you automatically.  It will end up being "=sum(A26:A42)" with the items in parentheses varying depending on what cells you selected.

To create an average do the same except type in =average( before you select the cells that you wish to average together.

dropping low grades

question: How do I drop one, two, three, or more of the grades out of a list of several grades for my students and then average the remaining grades?

This is a more complicated formula to do but once you understand what is going on, you will find it useful and easy to use.

1. First off, place ten scores in ten cells in one row.  Let's say that the scores are 88, 67, 94, 86, 66, 83, 99, 79, 78, and 92 and they are in cells A3 to J3.  Now the average of this whole list is 83.2.
2. To drop the lowest score and average the remaining nine scores, type this formula into cell K3:  =(SUM(A3:J3)-SMALL(A3:J3,1))/9 and then hit enter.
3. This will give you the new average of nine scores excluding the lowest score of 66.  The average is now 85.11.  To show how this impacts the grade change the score 66 to 76 which then makes 67 the new low score.  The average will now be 86.11. 
4. What is happening here is that the the SMALL function examines the list finds the lowest score which is then subtracted from the total which is then averaged by dividing by 9.
5. If you wish to drop the lowest two scores, use this formula in K3: =(SUM(A3:J3)-SMALL(A3:J3,1)-SMALL(A3:J3,2))/8 and then hit enter.  The second SMALL function finds the second lowest score in the list.  The average of the original list will be 87.375. 

If you wish to drop more than two grades just subtract using the SMALL function with the list and then the appropriate number.

absolute and relative references in excel

question:   I am working on a spreadsheet with thousands of cells. When I'm summing, I'm able to replicate formulas by using the Fill | Right function from the Edit menu. But when I use the same method to replicate a formula for calculating proportions, I get an error message. How can I replicate the formula calculating proportions throughout the numerous worksheets I'm working with?

The key here lies in understanding absolute and relative cell references. Here's your opportunity to learn by doing. Launch Excel and follow along as we illustrate the concept with an example.

1. In a new workbook, enter the column headings North, South, East, West, and Total, starting in cell B1. Enter the row headings Spring, Summer, Fall, Winter, Total, and Proportion, starting in cell A2. Enter some random numbers in the rectangle B2:E5.
2. In cell B6, enter the formula =SUM(B2:B5) or just click in that cell and press Alt-<Equal sign> to insert the formula automatically.
3. Highlight the cells from B6 to E6 and choose Edit | Fill | Right. Note that Excel did not copy the formula exactly; it modified the cell references relative to the column. For example, the formula in cell E6 is =SUM(E2:E5). The same thing happens when you copy and paste a formula or copy it by clicking in the cell and dragging that cell's fill handle.
4. Now we'll add row totals. Click in cell F2 and press Alt- to insert the formula =SUM(B2:E2) automatically. Highlight the range from F2 to F6 and choose Edit | Fill | Down from the menu. You now have row totals, with a grand total in cell F6.
5. The next step is to determine what proportion each row total is from the grand total. Click on cell B7 and enter the simple formula =B6/F6. Now, as before, use Edit | Fill | Right to copy that formula into the other columns. This time, it doesn't work! All the other columns display a #DIV/0! error message. When you click on the formula for one of those columns, you'll see why. With each move to the right, Excel adjusts both cell references, so the formula in column C is =C6/G6. But G6 and the cells to the right of it are empty, so dividing by them naturally causes an error.
6. To fix the problem, go back to cell B7 and change the formula to =B6/$F6. The dollar sign here tells Excel that the column in the reference F6 is absolute, meaning it should not be changed when the formula is copied. Fill the remaining columns with the modified formula and they'll all correctly display the proportions of the grand total they represent.

Use the $ character in front of the column letter or the row number or both to make that portion of any reference absolute.

copying only values

question:  I have a spreadsheet that has formulas. I need to merge data from another spreadsheet into it, without changing the formulas. How do I do this? I tried to cut and paste, but this erased all of the formulas. Is there any way to transfer the data without having to reenter everything manually?

First, make a copy of the entire worksheet from which you want to transfer the new data. In the copy, select Go To… from the Edit menu, click on the Special button, check the Formulas option, and click on OK. Now all formulas in the copy are highlighted. Press the Del key to delete all the formulas. If there are any other cells that you do not want transferred to the worksheet holding the formulas, delete those, too. You should wind up with a worksheet that contains nothing at all except the data you want to transfer. Press Ctrl+A and then Ctrl+C to copy this sheet's contents to the clipboard.

Now switch to the worksheet that contains the formulas and needs new data. Click in cell A1, select Paste Special from the Edit menu, check the Skip blanks box, and click on OK. Only the data will be pasted into this worksheet, leaving the formulas unchanged. Again, this can work only if the layout of the two sheets is identical.

inserting blank rows between data

question:  I have an Excel report that fills about 1,000 rows. I want to insert a blank row between each of these rows. Obviously, I know how to insert a row one at a time, but is there a quicker way to do this?

You bet. You can create the blank rows separately and then interleave them with the existing rows by sorting.

1. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data.
2. From the Edit menu select Fill | Series and click on OK.
3. Column A should now contain numbers from 1 to the total number of rows.
4. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste.
5. Now highlight the entire data area, including the new rows with just a number in column A.
6. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK.
7. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows.

Because blank lines can often cause problems with charts or calculations, you may want a quick way to remove them. You can use a similar technique.

Again, insert a new column to the left of column A and fill a series from 1 to the end of the data. Highlight the entire data area and sort by column B (the first column of real data). This will group all of the blank rows together. Next, highlight the data area again, and re-sort by column A. The blank lines are gone, and your data is restored to its original order. Finally, remove column A.

rounding issues in excel

question:  I have an Excel spreadsheet for my small business that I use to figure employee payroll. I enter the hours worked, and Excel calculates gross wages, tax deductions, and net payroll. Every now and then the net payroll doesn't add up correctly; it will be a penny off. I know it's because of rounding, but I can't find the right combination in a formula to correct it. What formula can I use so that Excel will always round across the columns so they add correctly?

As you note, the problem involves rounding. Suppose I get $99.75 per hour and work for 7.5 hours. Excel calculates my wages as $748.125, but you can't write a check for that amount. You've probably got Excel configured to use Currency formatting, in which case that value would display as $748.13. That is the amount you'll write on my check. Now suppose my three coworkers get the same. We're each owed $748.125, and we each get 1/2 cent more. If you sum our wages within your spreadsheet, you'll get a total that's 2 cents less than the sum of the values on the checks you wrote.

The solution is simple. When a value in your spreadsheet represents a real amount of money that must paid or collected in actual dollars and cents, change your formula to incorporate the rounding. For example, instead of =B2*C2, use the formula =ROUND(B2*C2,2). That will actually round the result to two decimal places. The value displayed and the value used in calculations will be the same, so you'll have no more discrepancies.

formatting date within other text

question: I want to calculate a date from some cells and then combine it with literal text in another cell. If I enter something like ="Totals thru " & MAX(C21:C100), the result doesn't display correctly. I get something like Totals thru 38463, when what I really want is something like Totals thru April 21, 2005. How can I do this?


First, you'll need to determine the format mask corresponding to the date format you want. The easiest way is to start with a cell that contains a date that's formatted that way. Right-click the cell, choose Format Cells, click the Number tab, and select Custom in the Category list at left. Now highlight the text in the box titled Type and copy it to the clipboard. It will look something like mmmm d, yyyy.

Now go back to the cell where you want to display a date combined with literal text. The TEXT() function will convert that date into text using whatever format mask you specify. In the example shown above, it might look like this: ="Totals thru " & TEXT(MAX(C21:C100), "mmmm d, yyyy"). Of course, you can use this technique to format any type of data in combination with literal text.

format a row based on one cell's value

To base of row of attributes on a cell's value, use the conditional formatting feature. Suppose you have a range of cells starting at A1, and you want all cells in a row to be red if the value in column B is over 90. Select cell A1, and choose Conditional Formatting from the Format menu. Set the condition to Formula Is, and enter =$B1>90. Click the Format... button, set the font color to red, and click OK, OK. With A1 still selected, click the Format Painter button on the toolbar, then select the entire range. The $ character preceding the column address in the formula marks it as absolute, so each cell's formatting will be based on the cell in column B in the same row. (Note: although you can specify up to three conditions for conditional formatting, the first condition that evaluates to TRUE will control the formatting.)

averages without extremes in excel

question: I have a column in Excel that contains account balances. I would like to find the average balance after throwing out the one or two highest and one or two lowest values. I have not found a clean way to keep track of the count while averaging only the values that meet my criteria. Is there a way to choose how many values to trim from the top and bottom before calculating the average?

Suppose your balances are in a range named Bal that starts in cell A1 and extends down column A. If you want to discard only the highest and lowest values, you could do that with a formula like this: =(SUM(Bal)-SMALL (Bal,1)LARGE(Bal,1))/(COUNT(Bal)-2). But we've found no easy way to go from this to a formula that lets you discard a variable number of items from the high and low ends. Instead, we worked up a second range of cells that indicate whether the adjacent cells should be included in the average.

Suppose the number of low values to be dropped is in cell D1, the number of high values to be dropped is in cell D2, and column B is empty. In cell B1, enter this formula: =AND(A1>SMALL(Bal,D$1),A1

Unfortunately, that system breaks down when the value in D1 or D2 is 0 (that is, if you cut off no items at the high or low end). If your spreadsheet must handle that eventuality, change cell B1 to this lengthy formula: =AND(IF(D$1>=0,A1>SMALL(Bal,D$1),TRUE),IF(D$2>=0,A1<LARGE(Bal,D$2),TRUE)). Then copy the formula throughout the remainder of the Temp range. You'll still get a #DIV/0 error if you cut off so many that none remain, but that's not unreasonable.

If you want to omit the same number of items from the top and bottom, or omit a certain percentage from top and bottom, you can use the TRIMMEAN() function. TRIMMEAN() takes as its arguments a range of values and a percentage between 0 and 1. If you specify 0.1, ten percent of the values will be omitted, taken equally from the top and bottom. In the example, you could omit two values, one each from the top and bottom, using the simple formula TRIMMEAN(Bal, 2/COUNT(Bal)). To omit two each from top and bottom, you'd use TRIMMEAN(Bal,4/COUNT(Bal)).

useful links

• http://www.broward.edu/HR/FacultyStaff/ProfDevTraining/ProfessionalDevelopment/page18836.html   Broward Community College has created a series of Microsoft Word documents that provide an excellent course in using Excel.  Start with "Basic Formulas" if you are a beginner and then work your way through the others.  These will take you step-by-step through many different aspects of using Excel.  (These are large documents and take some time to download but they are worth it.)
• http://learningcenter.clayton.edu/cid/tutorials.htm  Clayton State University has put together another set of good tutorials on Microsoft Office including Excel.  Click on Microsoft Office and then Excel in their list of online tutorials.  One of the tutorials is on using Excel as a grade sheet and provides instructions for using functions like VLOOKUP and so on.

freeze columns/rows

question:  How do I get column headings to remain visible when I scroll down my chart?  Or how do I keep row labels on the left visible when I scroll to the right in a table?

This is feature is put in a rather strange place in Excel.   You'd think it would be in the column or row or cell formatting but this feature actually happens as part of the Excel window not as part of the formatting for any cell, row, or column.  So that's where you look to find this feature.  It's in Window, then Freeze Pane.  Here's how to do it:

1. Select the row below or the column to the right of where you want the "freeze" to be in your spreadsheet.
2. From the menu bar, click on Windows, then Freeze Pane.
3. If you wish to freeze both rows and columns, select the one cell below and to the right of where you want the freeze to be and then click on Windows, then Freeze Pane.
4. To "unfreeze" something simply from the menu bar, click on Windows, then Unfreeze Pane.

That's all there is to do it. 

Gary's address: Kahl Education Center - EICC 326 West 3rd Street Davenport, Iowa 52801-1219 Phone:  563-336-5263 Help Desk: 563-336-3456 Email:  golson@eicc.edu

Send mail to Gary Olson with questions about this web site. Copyright © 2009 Eastern Iowa Community College District EICCD | EICCD Intranet | Faculty Pages | CCC | MCC | SCC