Google Sheets Tips

Leaving Cert Points Calculator

We had a query this week from a school looking for help with their CAO points calculations. Every year they receive their Leaving Cert results in a spreadsheet, and every year they carefully calculate CAO points, line by line, with a calculator.

They thought there was a better way, and the good news is that they were right!

This Google Sheet contains sample data and gives you a basic template which you can use to automate this process for your school. By clicking on File-Make a Copy at that link you can create your own copy of the template and see how it all fits together.

As with all spreadsheet tasks there are many possible solutions to this one. The basic structure of this approach is highlighted in this screenshot, with the original data highlighted in yellow on the left and the new data we’ve added highlighted in blue on the right:

(Don’t worry, you don’t need to read off the tiny picture!) The new cells we’ve added give us a grid of CAO points values, which are calculated automatically using the IFS function. This is necessarily a long function, as it describes the relationship between the letter grades and the CAO points. Be careful with foundation level subjects! Although we’ve included them here as worth zero points this can vary slightly depending on the specific CAO course being applied for, so you may need to treat those rows as special cases.

=IFS(
    B2="H1", 100,
    B2="H2", 88,
    B2="H3", 77,
    B2="H4", 66,
    B2="H5", 56,
    B2="H6", 46,
    B2="H7", 37,
    B2="H8", 0,
    B2="O1", 56,
    B2="O2", 46,
    B2="O3", 37,
    B2="O4", 28,
    B2="O5", 20,
    B2="O6", 12,
    B2="O7", 0,
    B2="O8", 0,
    B2="F1", 0,
    B2="F2", 0,
    B2="F3", 0,
    B2="F4", 0,
    B2="F5", 0,
    B2="F6", 0,
    B2="F7", 0,
    B2="F8", 0,
    B2="", 0
)

Cell Y2 (which contains the formula above) will now hold the points value for the grade in cell B2, which in the example is 77 points for a B3 in Irish. All the function does is run through each letter grade check for a match. “If B2 contains H1, then the answer is 100; if B2 contains H2 the answer is 88, etc”.

We can simply drag the corner of the cell containing this formula to fill out the whole grid. The reference to cell B2 will automatically update, and the section of the sheet highlighted in blue in our screenshot will now contain the points values for all the grades in the yellow part of the sheet.

There is one other catch here - the bonus 25 points for maths! We can adjust the formula in the maths column only as follows (again, this is in the sheet shared above):

=IFS(
    B2="H1", 100+25,
    B2="H2", 88+25,
    B2="H3", 77+25,
    B2="H4", 66+25,
    B2="H5", 56+25,
    B2="H6", 46+25,
    B2="H7", 37,
    B2="H8", 0,
    B2="O1", 56,
    B2="O2", 46,
    B2="O3", 37,
    B2="O4", 28,
    B2="O5", 20,
    B2="O6", 12,
    B2="O7", 0,
    B2="O8", 0,
    B2="F1", 0,
    B2="F2", 0,
    B2="F3", 0,
    B2="F4", 0,
    B2="F5", 0,
    B2="F6", 0,
    B2="F7", 0,
    B2="F8", 0,
    B2="", 0
)

You don’t have the use “+25” here - you could just put 125 for a H1, 113 for a H2 and so on - but I find this way of writing it clearer.

Totalling the best six

Now that we’ve got our points values we need to add them together to get a total for each student. Of course we only want the top six subjects, so in addition to the “sum” function we also need to use the “large” function. “Large” allows us to get the n-th largest item from a list. In our case we want the 1st largest, 2nd largest, etc, down to the 6th largest, and we can get those values and add them together with the following function:

=SUM(
   LARGE(Y2:AS2, 1), 
   LARGE(Y2:AS2, 2), 
   LARGE(Y2:AS2, 3), 
   LARGE(Y2:AS2, 4), 
   LARGE(Y2:AS2, 5), 
   LARGE(Y2:AS2, 6)
)

You’ll find this in column AT in the sample sheet shared above.

What can go wrong

If you use Excel instead of Google Sheets the process should be the very same, including the same function names. We haven’t tested that though, so proceed with some caution.

For readability, we’ve already replaced the numerical subject IDs in the results sheet with the subject names. It probably goes without saying, but don’t mix up the maths column! The function for every other column will be effectively identical, but maths is different because of the bonus points.

The other place to be very careful is with cell range references. Our sample school offers 21 subjects, and the totalling formula above references a range of 21 cells (“Y2:AS2”, meaning every cell from Y2 to AS2, including Y2 and AS2). If your school offers more or fewer subjects your sheet will be a bit different, and you need to make sure you’re not leaving out any cells from those functions.

Equally, be sure that your grid of CAO points (the portion highlighted in blue in the screenshot above) actually matches the grid of grade results (the yellow highlighted portion). If you miss a column, that will of course impact the final results!

As with any automation it’s important to manually check at least a few of the output values to make sure all is in order.

Best of luck to all the leaving cert students awaiting their grades, and to all the hard working admin staff working to get them prepared! If you have any questions about this process, or any other spreadsheet questions which you’d like us to cover in our new Tips section, please let us know.