Excel Formula Help – Absolute References in Tables
In this blog we will look at how to create a simple application in Microsoft Excel 2012 to record average student performance in percentage values. The resulting table will look like this:
In the first instance, enter the student marks for the work done before the Points Possible. Totals for each student can use the =SUM formula in the normal way:
Copy down to fill in all Totals. The Points Possible row should also be totalled:
Class Averages are similarly done in the usual way. Select the cell, in this case B16, and the click the formula fx button to select the =AVERAGE function and complete the field.
It may be easier to hide contiguous columns that are not being used. Select the columns, right click and click on Hide – note below, the Unhide option:
To work out the average in percentage terms, fist select the relevant cells and again, right click, select Format Cells and then Percentage:
Intuitively, you may decide that a simple =H$/H15 formula will give you the percentage, and for a single sell this would be correct:
However, on trying to copy down you will find errors:
Return to the formula and add make the Total Points Possible cell an absolute reference by using the '$' symbol before both the column and row denotation.
This fixes the Total Points Possible cell reference during the copy down. You now have the student averages so far in percentage terms and your application is almost now complete:
A percentage based Class Average can also be achieved in I16 with with the correct formatting and use of =AVERAGE function.
Adding additional scores to the sheet now results in an automated processing of Student and Class averages. As you add marks, don't be put off by the strange looking percentages:
The numbers will look correct once you add the Points Possible at the end of the column:
For further details on absolute references, check out our Youtube clip below or go to the Microsoft help page. In our next blog, we will look at how to convert student percentages to letter grades using VLOOKUP.
