Calculating average letter grades in an excel Markbook
Firstly I have only a limited knowledge and understanding of Excel and so ask for your forgiveness in advance if this is a primitive query!!
I am a teacher and wish to create an excel ‘Markbook’ and have searched high and low all over the net for the answer to my problem but as of yet can only find answers to very similar but different questions. If anyone can offer a solution or some advice I would be most grateful.
I would like to record and display the marks for homework completed on a week by week basis and would like to see a running average grade as the term progresses.
Difficult part is that we give grades as a two part result based on a letter grade for attainment (X,A,B,C,D) and a number grade based on effort put into the work (1,2,3,4,5). i.e X1 for great work that the pupil has put a lot of effort into and at the other end of the scale D5 for very poor work with little to no effort spent.
Progress so far…
I have put together a simple example of the eventual Markbook and will include it with this post.
I currently have a column for some sample names and a table for 12 weeks worth of marks.
At the end of this table I have an Attainment Average column and an Effort Average column.
In the example I have worked out how to average the effort part of the grade so have highlighted the Effort Average box green & left these out bar the grades for ‘Andrew’ which are for example only.
Using the formula below I have managed to convert the letter grades for each week into a number, then average them, round up and convert back into a letter grade.
Unfortunately the above formula only works if all the grades have been entered and this is not the case during the term time with weeks left, if a boy is absent and misses a homework or if a project is less than 12 weeks due to sports etc.
Also our top grade (X as an equivalent to an A*) returns a random letter not the correct average.
Lastly I am reluctant to insert any hidden columns or have referenced cells hidden away as I am looking to make this table a template to reproduce for the number of classes taught and this could make pasting new sets of names difficult in the future.
Thanks very much again in advance if you can help I am willing to try anything!! (also sorry for the long post)
Video Tutorials / Excel Dashboards Reports