Welcome

In the following few pages, we will show you how to use Excel to edit, compile, and manage student marks that are exported from OWL. There will be screenshots along the way to guide you.

If you use OpenOffice instead of Excel, the steps and formulas will be very similar. However, in OpenOffice formulas, you will need to use semicolons instead of commas.

This page will teach you:

How to use Excel

We will be working with a file like the one below. This will be similar to the data you export from your OWL grade book. You can follow along with an exported file from your course, or use our sample file.

A cell is referenced in Excel first with the column (A, B, C, D, etc.), then with the row (1, 2, 3, 4, etc.). In the example below, the first grade in Lab 2 is in cell C2. All formulae in Excel must start with the equal sign (=).

Sample spreadsheet

There are two things to note in the layout. One is the horizontal formula bar just above the spreadsheet area, labeled fx. This is where you will be typing or editing formulae. The formula of any active cell you select will also show up automatically in the formula bar. If you want to exit the formula without making any changes, press ESC.

The second thing to note is the little black square at the corner of the active cell. If you hold down on the black square and drag vertically down or horizontally across, it will copy the formula of the active cell into the other cells that are covered in the drag, and will change the reference cells relative to the original. If you do not want reference cells to change, you need to add $ signs. Adding a $ sign in front of the column letter (i.e. $E2) will maintain the same column but the row may change. Adding a $ sign in front of the row number (i.e. E$2) will maintain the same row but the column may change. Adding a $ sign in front of both (i.e. $E$2) will make the reference to that cell absolute.

These functions will come in very handy when you are working with formulae in Excel.

return to top ↑

How to Set Up the Data

Before we can proceed with our calculations, we need to input what the perfect score and weight (between 0 and 1) of each grade column is. Insert two new rows in Row 2 and Row 3 and label them "Perfect Score" and "Weight" respectively in the first column. See what we have done to our sample file:

Sample Spreadsheet Edited

Notice that in our sample file, the total weight does not add up to 1. That is ok. You can use these calculations at any point during the course, before all evaluations have been conducted.

return to top ↑

How to Produce Letter Grades

This function will convert numeric grades into letter grades.

Assume you want to change the Midterm grades into letter grades based on the following marking structure:

  • Everything greater and including 90 is A
  • Between 80 and 90 is B
  • Between 70 and 80 is C
  • Between 60 and 70 is D
  • Below 60 is F

Copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=IF(E4>=90,"A",IF(E4>=80,"B",IF(E4>=70,"C",IF(E4>=60,"D","F")))) OpenOffice:=IF(E4>=90;"A";IF(E4>=80;"B";IF(E4>=70;"C";IF(E4>=60;"D";"F")))) Copy and paste the same formula into all cells of the new column.

Midterm grade converted to letter grade in column J

return to top ↑

How to Produce Percentages

This function will reproduce numeric grades as a percentage.

In our example, we want to turn Lab 2 into a percentage, using the perfect score we have entered.

Copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=C4/C$2 OpenOffice:=C4/C$2 Copy and paste the same formula into all cells of the new column.

To view as percentages, select the cells that contain the new grades. Right click and select “Format Cells.” On the Number tab, choose “Percentage” from the Category. The default is 1 decimal place, but you can change this to any number of decimal places you would like.

Formatting cells as percentages

return to top ↑

How to Produce Percentiles

This function will reproduce numeric grades as a percentile.

In our example, we want to turn Lab 2 into a percentile. Copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=PERCENTRANK($C$4:$C$16,C4,3) OpenOffice:=PERCENTRANK($C$4:$C$16;C4) Copy and paste the same formula into all cells of the new column.

This example sets the percentiles at 3 significant digits. If you would like to change this, just change the 3 in the formula to any number you like. Excel will only show non-zero digits though, unless you format cells to display a certain number of decimal places.
This formula will also produce decimal numbers. To view as percentages, select the cells that contain the new grades. Right click and select “Format Cells.” In the Number tab, choose “Percentage” from the Category. The default is 1 decimal place, but you can change this.

return to top ↑

How to Produce Pass/Fail

This function will reproduce grades as either a Pass or a Fail.

In our example, we want to convert the Midterm grades into either Pass or Fail. Assume that all grades above and including 25 constitute a “Pass”, while all grades below 25 constitute a “Fail”.
Copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=IF(E4>25,"Pass","Fail") OpenOffice:=IF(E4>25;"Pass";"Fail") Copy and paste the same formula into all cells of the new column. You can change the resulting words by editing in between the quotation marks in the formula.

return to top ↑

How to Round

This function will round all grades to whole numbers.

In our example, we want to convert all Midterm grades into whole numbers. Everything above and including 0.5 will be rounded up, while everything else will be rounded down.
Copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=ROUND(E4,0) OpenOffice:=ROUND(E4;0) Copy and paste the same formula into all cells of the new column.

return to top ↑

How to Round and Bump

This function will round all marks that end with 8.5 or above to the nearest multiple of ten. This would make a 68.5 a 70, a 79.0 an 80, and an 89.9 a 90.

To do this, copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=IF(MOD(ROUND(E4,0),10)=9,ROUND(E4,0)+1,ROUND(E4,0)) OpenOffice:=IF(MOD(ROUND(E4;0);10)=9;ROUND(E4;0)+1,ROUND(E4;0)) Copy and paste the same formula into all cells of the new column.

return to top ↑

How to Rank Grades

This function will rank grades in either ascending or descending order.

Ascending:
Assume the grades start in cell K4.
Copy the following formula into a new cell parallel to the grade column (e.g. L4): Excel:=RANK(K4,$K$4:$K$16,1) OpenOffice:=RANK(K4;$K$4:$K$16;1) Copy and paste the same formula into all cells of the new column.

Descending:
Assume the grades start in cell K4.
Copy the following formula into a new cell parallel to the grade column (e.g. L4): Excel:=RANK(K4,$K$4:$K$16,0) OpenOffice:=RANK(K4;$K$4:$K$16;0) Copy and paste the same formula into all cells of the new column.

return to top ↑

How to Calculate the Best Sum

This function will produce the sum of the best x number of grades. The perfect score for each must be the same, or the result is meaningless.

For our example, assume we want to calculate the best two lab marks out of Lab 1, Lab 2, and Lab 3.
Copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=LARGE((C4,D4,F4),1)+LARGE((C4,D4,F4),2) OpenOffice:=LARGE((C4~D4~F4);1)+LARGE((C4~D4~F4);2) Copy and paste the same formula into all cells of the new column.

Entering the formula for the sum of best two lab marks into column J

If you would like the best three, four, or five marks out of a certain array, you can add +LARGE((C4,D4,F4),3) and so on to the formula.

return to top ↑

How to Calculate the Best Average

This function will produce the average of the best x number of grades, assuming the perfect score for each is the same. If the perfect scores are not the same, first calculate the percentage for each grade and apply this formula on those results instead.

For our example, assume we want to calculate the average of the best three lab marks out of a total of five labs.
Copy the following formula into a new cell parallel to the grade column (e.g. J4): Excel:=AVERAGE(LARGE((C4,D4,F4,H4,I4),1),LARGE((C4,D4,F4,H4,I4),2),LARGE((C4,D4,F4,H4,I4),3)) OpenOffice:=AVERAGE(LARGE((C4~D4~F4~H4~I4);1);LARGE((C4~D4~F4~H4~I4);2);LARGE((C4~D4~F4~H4~I4);3)) Copy and paste the same formula into all cells of the new column.

Entering the formula for the average of best three lab marks into column L

If you would like the average of the best four, five, or more marks out of a certain array, you can add LARGE((C4,D4,F4,H4,I4),4) and so on to the formula.

return to top ↑

How to Perform Excused Averaging

This function will calculate an average from a set of marks, ignoring those marked with the special code EXCU. This allows you to excuse course components for certain students.

In our example, we will find the average, expressed as a percentage, of Lab 1 and Lab 2. Let's look at two students, Sara and Jim. Both students missed Lab 1, but Sara had a medical emergency and has been excused from Lab 1, while Jim did not have a valid reason for missing the lab and received a 0. Even though Sara and Jim both received the same grade for Lab 2, excused averaging gives Sara a much higher grade because her failure to complete Lab 1 is not held against her.

Excel:=(IF(C4<>"EXCU",C4,0)/C$2+IF(D4<>"EXCU",D4,0)/D$2)/(IF(C4<>"EXCU",1,0)+IF(D4<>"EXCU",1,0))*100 OpenOffice:=(IF(C4<>"EXCU";C4;0)/C$2+IF(D4<>"EXCU";D4;0)/D$2)/(IF(C4<>"EXCU";1;0)+IF(D4<>"EXCU";1;0))*100 Copy and paste the same formula into all cells of the new column.

Entering the formula for excused averaging of lab marks into column J

return to top ↑

How to Conduct a T-Test

This function will generate the probability associated with a Student's t-Test.

For our example, assume we want to conduct a one-tailed distribution t-Test of Lab 1 and Lab 2. This will be a paired test since the data is from the same sample of students.
Copy the following formula into any new cell: Excel:=TTEST(C4:C16,D4:D16,1,1) OpenOffice:=TTEST(C4:C16;C4:C16;1;1)

If you would like to conduct a two-tailed distribution t-Test, change the second last number of the formula to a 2. If you would like to conduct a two-sample equal variance test, change the last number of the formula to a 2. If you would like to conduct a two-sample unequal variance test, change the last number of the formula to a 3.

return to top ↑

How to Find the Pearson Coefficient

This function will generate a Pearson correlation coefficient between two columns of grades.

For our example, assume we want to find the Pearson coefficient of Lab 1 and Lab 2.
Copy the following formula into any new cell:
Excel:=PEARSON(C4:C16,D4:D16) OpenOffice:=PEARSON(C4:C16;C4:C16)

return to top ↑

How to Use the If-Then-Else Formula

This function will award a certain grade if the inputs are above a certain amount, otherwise it will award a 0. This function is useful for calculating class participation or attendance.

Assume that there are five classes in total. If the student participated that class, he/she gets a 1, otherwise a 0. Therefore, a perfect participation score after five classes would be 5. Now assume you want to give everyone who participated in 3 classes or more a perfect score, a 5, while everyone with less than 3 classes gets a 0.

Assuming the participation is recorded in columns B to F, starting in row 4, the formula would look like this:


Excel:=IF(SUM(B4:F4)>=3,5,0) OpenOffice:=IF(SUM(B4:F4)>=3;5;0)

Copy and paste the same formula into all cells of the new column.

return to top ↑

How to Standardize to Mean

This function will re-produce grades standardized around a mean and a standard deviation. You, the user, must specify what mean and what standard deviation you would like.

In the following formula, replace x with a numerical number for your desired mean, and y with a numerical number for your desired standard deviation. Then, copy the formula into a new cell parallel to the grade column (e.g. J4):
Excel:=y*(C4-AVERAGE($C$4:$C$16))/STDEVP($C$4:$C$16)+x
E.g.: =10*(C4-AVERAGE($C$4:$C$16))/STDEVP($C$4:$C$16)+75 OpenOffice:=y*(C4-AVERAGE($C$4:$C$16))/STDEVP($C$4:$C$16)+x
E.g.: =10*(C4-AVERAGE($C$4:$C$16))/STDEVP($C$4:$C$16)+75

Copy and paste the same formula into all cells of the new column.

Entering the formula for standardized marks into column J

return to top ↑

How to Calculate Term Grades

This is a standard formula for calculating Term Grades. In this example the components of the grade are in columns E, F, and G.

Excel:=(E4/$E$2*100*$E$3 + F4/$F$2*100*$F$3 + G4/$G$2*100*$G$3) / ($E$3+$F$3+$G$3) OpenOffice:=(E4/$E$2*100*$E$3 + F4/$F$2*100*$F$3 + G4/$G$2*100*$G$3) / ($E$3+$F$3+$G$3)

Copy and paste the same formula into all cells of the new column.

This is the formula to use if you want the presence of any grade component below 50% to constitute a "Fail."

Excel:=IF(E4/E$2<0.5, "FAIL", IF(F4/F$2<0.5, "FAIL", IF(G4/G$2<0.5, "FAIL", (E4/E$2*100*E3+F4/F$2*100*F3+G4/G$2*100*G3)/(E3+F3+G3)))) OpenOffice:=IF(E4/E$2<0.5; "FAIL"; IF(F4/F$2<0.5; "FAIL"; IF(G4/G$2<0.5; "FAIL"; (E4/E$2*100*E3+F4/F$2*100*F3+G4/G$2*100*G3)/(E3+F3+G3))))

Copy and paste the same formula into all cells of the new column.

This is the formula to use if you want to excuse certain students from course components using the special code EXCU. This is similar to the Excused Averaging formula.

Excel:=(IF(E4<>"EXCU",E4,0)/E$2*100*E3+IF(F4<>"EXCU",F4,0)/F$2*100*F3+IF(G4<>"EXCU",G4,0)/G$2*100*G3)/(IF(E4<>"EXCU",E3,0)+IF(F4<>"EXCU",F3,0)+IF(G4<>"EXCU",G3,0)) OpenOffice:=(IF(E4<>"EXCU";E4;0)/E$2*100*E3+IF(F4<>"EXCU";F4;0)/F$2*100*F3+IF(G4<>"EXCU";G4;0)/G$2*100*G3)/(IF(E4<>"EXCU";E3;0)+IF(F4<>"EXCU";F3;0)+IF(G4<>"EXCU";G3;0))

Copy and paste the same formula into all cells of the new column. For a more advanced method of dealing with excused course components (weight shifting), use the Excel or OpenOffice extension.

How to Calculate Final Grades

This is a standard formula for calculating Final Grades. In this example the components of the grade are in columns E, F, and G.

Excel:=(E4/$E$2*100*$E$3 + F4/$F$2*100*$F$3 + G4/$G$2*100*$G$3) OpenOffice:=(E4/$E$2*100*$E$3 + F4/$F$2*100*$F$3 + G4/$G$2*100*$G$3)

Copy and paste the same formula into all cells of the new column.

This is the formula to use if you want the presence of any grade component below 50% to constitute a "Fail."

Excel:=IF(E4/E$2<0.5, "FAIL", IF(F4/F$2<0.5, "FAIL", IF(G4/G$2<0.5, "FAIL", E4/E$2*100*E3+F4/F$2*100*F3+G4/G$2*100*G3))) OpenOffice:=IF(E4/E$2<0.5; "FAIL"; IF(F4/F$2<0.5; "FAIL"; IF(G4/G$2<0.5; "FAIL"; E4/E$2*100*E3+F4/F$2*100*F3+G4/G$2*100*G3)))

Copy and paste the same formula into all cells of the new column.

This is the formula to use if you want to excuse certain students from course components using the special code EXCU. This is similar to the Excused Averaging formula.

Excel:=(IF(E4<>"EXCU",E4,0)/E$2*100*E3+IF(F4<>"EXCU",F4,0)/F$2*100*F3+IF(G4<>"EXCU",G4,0)/G$2*100*G3)/(IF(E4<>"EXCU", E3, 0)+IF(F4<>"EXCU", F3, 0)+IF(G4<>"EXCU", G3, 0)) OpenOffice:=(IF(E4<>"EXCU";E4;0)/E$2*100*E3+IF(F4<>"EXCU";F4;0)/F$2*100*F3+IF(G4<>"EXCU";G4;0)/G$2*100*G3)/(IF(E4<>"EXCU";E3;0)+IF(F4<>"EXCU";F3;0)+IF(G4<>"EXCU";G3;0))

Copy and paste the same formula into all cells of the new column. For a more advanced method of dealing with excused course components (weight shifting), use the Excel or OpenOffice extension.

return to top ↑