Adding together letter grades and assigning an overall grade

Hi,

My husband is a teacher and has asked me to make his life a little easier during exam times by creating a automatic grading excel spreadsheet. However, I am having trouble getting my head around how to actually go about it. I was thinking some sort of vlookup table but not really sure. I have attached the workbook I've set up so far.

To start off with I need to add together each KU grade during term 1 (cells B4, G4 & L4) and put the letter result in cell Q4. I thought I could do this by assigning each grade a number and dividing by the total by the number of grades which then corresponds to an overall letter grade. But the number of grades I want to add together will vary from time to time. For example, the I grades for term 1... I only have 1. So I need the formula to say if a cell is blank, leave it out and reduce the dividing number by 1. (Sorry I hope that makes some sense?)

Would anyone be able to help? Or have any other ideas on how I could go about this?

Thanks,
Jen

Please Login or Register  to view this content.

the denominator changes accordingly if all three grades are granted or just 2 or 1, no provision for no grade was considered. Hoja1 has the table you were looking for

question: for the semester grade do you add the term 1 grades too, and for the second semester?

in the file attached i used row 5 thru 7 for calculations, they can be hidden or transported to another sheet

Hi rcm,
Thanks for your help with this. I really appreciate it. I have done a little work to get it set up how I wanted... doing the calculations on the far right so they are out of the way of all the results and less likely to be tampered with. (attached) Although I am having trouble getting it to work for the grades that have less than 3 marks. I just get an error instead of the calculation working on 2 or 1 mark. I also noticed that some variations of marks return an error as well. E.g for the KU grades if you have a D, D+ and a B+ I get an error.

Answer to your question... the year is split into 2 semesters. So you don't have to take into consideration semester 1 (and term 1) results in the semester 2 grades.

Ah, I see you've added an IFERROR statement to yours to address the less than 3 marks issue. I will have a go at incorporating it into my sheet.

Thanks again. :D

Here is the finished product for 25 students

Holy Moley! WOW! Thank you so much! Your a major excel wizard!

I will get my husband to test. Would it be ok if I contact you if we come across anything we might need explained? It should be either today or tomorrow...

Here is the file with the first request, just try to explain what is it to be done in the Rung tabs