Averaging Letter Grades

formula.jpg

I've used the following formula successfully to find the class average of letter grades for my class in each area (see photo above - averages in the row highlighted green).

Please Login or Register  to view this content.

However, I've tried to adapt the formula to create an average for the individual student for each learning area and I'm not having any luck. I want the T column to be the individual average of the students grade in the E, J, and O columns, and then I'll fill this down for all students. This is where I'm at now.

Please Login or Register  to view this content.

With the bold areas being where the issue lies (I believe), because that's the only part of the formula that I changed.

Where am I going wrong?

Any and all help would be appreciated!!

Hi,

You could use CHOOSE in this instance, i.e.:

=IFERROR(INDEX({"A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},ROUND(AVERAGE(IF(CHOOSE({1,2,3},E8,J8,O8)<>"",MATCH(CHOOSE({1,2,3},E8,J8,O8),{"A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0))),0)),"")

still committed as an array formula**.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're welcome!

Cheers