As we move into the peak time of year for grading and marks processing, thousands of academics and staff across the University will be poring over Excel spreadsheets trying to make sense of marks, grades, distributions, and everything in between. In solidarity, this article presents some tricks that will only take you a few minutes to read and learn, but may save you many times that this marking season. We only cover a few stalwarts here, in recognition that there are thousands of excellent Excel tutorials and guides out there.
This short tips sheet assumes that you have some basic Excel knowledge, such as knowing what cells and cell references are (the B3, Z20, T43 things), being able to enter formulas and functions, and knowing basic functions like AVERAGE and SUM.
VLOOKUP finds the needles in the haystacks
Locating marks and other student-specific data
The most common operation that we need to do as teachers and administrators is probably finding particular marks for particular students and putting these marks next to their SID. The VLOOKUP function is your friend here. Consider the following two spreadsheets – the one on the left is a typical marks return sheet from Sydney Student, and the one on the right could be any spreadsheet that you have on your computer that houses student marks.
In this example, you need to place the final mark (column L in your own spreadsheet) into column I of the marks return sheet. VLOOKUP helps you to do this – it (i) looks down through the first column of a specified ‘range’ (a rectangular selection of cells), (ii) finds a particular value it’s been asked to look for (the ‘lookup value’), then (iii) goes across that row, and (iv) locates the value in a column it’s been asked to extract data from. To do this, it needs to know (i) where to look, (ii) what value to look for, and (iii) what other column you need to grab data from. The structure of the VLOOKUP function is:
=VLOOKUP(<lookup value>,<range>,<column in range to grab value from>,<don't perform an exact match?>)
This is the formula we’d use in cell I2 of the marks return sheet, followed by a short screencast (37 seconds) that demonstrates this in action. Cell “M2” contains the SID, the range reference “[my_random_spreadsheet.xlsx]marks!$A:$L” just refers to the second spreadsheet, “12” tells Excel that we want it to grab the value from the 12th column of the range, and “FALSE” tells Excel to find an exact match for M2.
=VLOOKUP(M2,[my_random_spreadsheet.xlsx]marks!$A:$L,12,FALSE)
An important note for VLOOKUP is that the lookup values (in this instance, SIDs) must be in the first column of the selected range.
Transforming marks into grades
A little-known trick with VLOOKUP is its ability to do a non-exact search. For example, you can use this ability to quickly convert numerical marks into letter grades. Check out the following video (47 seconds) to see how. This leverages the last parameter in the VLOOKUP function, which was set to FALSE in the example above. If set to TRUE, Excel searches through the range and finds the closest value that is greater than or equal to the lookup value.
This example also highlights the importance of including FALSE when you want Excel to perform an exact match (e.g. when looking up an SID). By default, Excel uses TRUE for the last parameter, meaning that by default it will look for the closest value instead of an exact value. An important distinction since you want students identified by exact SID not the next closest!
IF gives you branching abilities
The IF function allows you to tell Excel to examine a cell (or multiple cells, actually) for a particular condition, and then spit back a certain value if the condition holds true, and another value if the condition is not true. For example, if the grade for a particular assessment is zero, then don’t count another piece of assessment, otherwise count it. The structure of the IF function is:
=IF(<condition>,<value if condition is true>,<value if condition is false>)
The video below (34 seconds) demonstrates the example just described.
If you want to get extra tricky, you can combine conditions in the IF function. For example:
=IF((<condition1>)*(<condition2>),<value if both conditions are true>,<value otherwise>)
And if you want to use or instead of and, replace the asterisk with a plus.
AVERAGEIFS, SUMIFS, COUNTIFS let you calculate conditionally
The AVERAGE, SUM, and COUNT functions are also commonly used functions. Did you know there are combo functions that combine the calculations of these functions with the power of multiple IF statements? These combo functions are called AVERAGEIFS, SUMIFS, and COUNTIFS. Say we wanted to find out the average grade of an assignment by tutorial group. You could do this by sorting your worksheet by tutorial group and then entering a series of manually-selected ranges. Or, use AVERAGEIFS. The structures of these functions are similar:
=AVERAGEIFS(<range to calculate average of>,<first range to judge criterion 1 on>,<criterion 1>...) =SUMIFS(<range to calculate the sum of>,<first range to judge criterion 1 on>,<criterion 1>...) =COUNTIFS(<first range to judge criterion 1 on>,<criterion 1>...)
The COUNTIFS function looks a bit different because it is missing the first parameter of AVERAGEIFS and SUMIFS.
The example described above, for AVERAGEIFS, is demonstrated in the following video (46 seconds):
LARGE gives the highest-of
Sometimes we find ourselves setting sequential nominal assessments to keep students on track throughout a semester. For example, these could be fortnightly online quizzes that are worth a low proportion of the final marks, and we want to encourage students to do them by saying we will take, say, the highest 3 marks out of 4 attempts. Canvas assignment groups can be used for this, although some find it a bit problematic. The quick Excel solution is to use the LARGE function. The general structure of this function is:
=LARGE(<range to draw values from>,{p,q,r...})
In this structure, p, q, r… refer to the p-th, q-th, and r-th highest values. So to get the 1st, 2nd, and 3rd highest values, we would use {1,2,3} here. Based on the example described above, here’s a short video (35 seconds) demonstrating how to combine LARGE with SUM in Excel.
If you’d rather calculate the average, just substitute the SUM function with the AVERAGE function.
Calculate late penalties using arithmetic, ROUNDUP, and IF
As the University moves towards adopting a common policy in relation to late penalties, many faculties already have resolutions stating, for example, that each calendar day (or part thereof) late incurs a 5% penalty of the maximum mark available. To do this, we’ll need to subtract the due date and time from the submission date and time to find out how many days late a submission is. We’ll use the ROUNDUP function to consider the ‘part thereof’ condition. Then, we use the IF function to decide if it’s actually late, and then apply some more simple arithmetic to calculate the percentage deduction. The ROUNDUP function has this structure:
=ROUNDUP(<cell reference or number>,<number of decimal places to retain>)
This video (74 seconds) demonstrates this example:
Other tricks
Hopefully the tips above will save you some time in processing marks and grades. Here are some other useful Excel abilities that will also save you time:
- Filter your table to quickly locate subsets of data.
- Paste-as-value disconnects any cell references and just gives you the raw values.
- For the extreme user, array formulas are amazing and allow you to move beyond the conditional aggregation functions (e.g. AVERAGEIFS) to apply conditions to other functions that don’t have a built-in conditional version.
Tell me more
- The University has an enterprise subscription to Lynda.com, which hosts a vast array of great video-based tutorials. Highly recommended for deeper dives.
- The Microsoft Excel help centre has a great selection of regularly-updated guides.
- Do you have any other favourite Excel tricks for teaching? Share them on the Educational Innovation Yammer group.
