Calculating average letter grades in an excel Markbook

[andiwhite]

Hi there,

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.

Task
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.

Problems
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.

=CHAR(ROUND(AVERAGE(CODE(D4),CODE(F4),CODE(H4),CODE(J4),CODE(L4),CODE(N4),CODE(P4),CODE(R4),CODE(T4),CODE(V4),CODE(X4),CODE(Z4)),0))

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)
Andrew

Markbook_AJW.xls

[Batman]

Hi andiwhite,

Welcome to the Ozgrid forum.

Try this as a formula in cell AC4, and fill down.

=INDEX({"X";"A";"B";"C";"D"},ROUND(SUMPRODUCT(--(D4:AA4>5),MATCH(D4:AA4,{"X","A","B","C","D",1,2,3,4,5,0},0))/SUM(--(ISTEXT(D4:AA4))),0),1)

It's an array formula, and must be confirmed with Shift + Ctrl + Enter, instead of just Enter.

[andiwhite]

Fantastic!

I really appreciate your time thank you so much.

Works like a dream! I think I just about understand how - had to look a few parts up but pretty much there now.

Dont suppose you know of any way to make this cell show blank if no grades have been added yet do you? Dont worry if not this is more than enough to keep me going for all my classes!

Thanks so much for your speedy reply
Andrew

[Batman]

Andrew,

The first action that the formula will take will be to check if any data has been entered in the range. If not, it will return a blank cell, otherwise it will do the calculation. You still might get a 'division by zero' error if numeric grades have been entered, but no alpha equivalents; is that likely?

=IF(COUNTA(D4:AA4)=0,"",INDEX({"X";"A";"B";"C";"D"},ROUND(SUMPRODUCT(--(D4:AA4>5),MATCH(D4:AA4,{"X","A","B","C","D",1,2,3,4,5,0},0))/SUM(--(ISTEXT(D4:AA4))),0),1))

It's still an array formula, and must be confirmed accordingly.

[andiwhite]

Legend!

Once again that works perfectly! No grades -> No Average, Any or all grades -> Average grade!

Fantastic help I would say that I know quite a lot about the subject that I teach but have no idea about excel, its nice to learn some new things from an expert in the field!

I take my hat off to you and am very appreciative for your help, this is a great forum!

Thanks so much
Andrew