Working out a grade average using a lookup table

[kitkatkin]

Can anyone help, I am trying to work out a grade average for students in my class. I have converted the grades to points in a lookup table so that their grade level is traffic lighted according to whether they are below,on or above their target level but I also want to be able to work out their average grade level too.

I am an Excel newbie, have very basic knowledge (I'm a History teacher, I don't use formulae on a regular basis) and have only got this far on my worksheeet by finding other bits and pieces out on the web.

Can someone help me with a formula to work out the average grade???

Thanks

[JBeaucaire]

I have converted the grades to points in a lookup table

Where? Your grades are not numeric, they're combinations of letters and numbers, meaning they are text.

If you want to use a lookup table, create it. Your grade text-strings in the first columns, the numeric value on the second column.

Then each grade can be converted to a real number, those numbers can be averaged, and that average can then be converted back to your text-strings with the same table.

[kitkatkin]

The lookup table is to the right of the spreadsheet (an organgy colour) and is called "Levelpoints"

[JBeaucaire]

My bad, I see them off to the right now. Checking.

[djapigo]

Hi kitkatkin,

This is what I came up with... don't ask about the dividing factor... for some reason I could not find a proper COUNT formula...

=SUM(TRANSPOSE(INDIRECT("z"&MATCH($B3:$N3,$Y$6:$Y$21,0)+5)))/(COUNTA($B3:$N3)-COUNTBLANK($B3:$N3))

Couple of things to know about this formula...
1. This is an array-formula, so you need to use CTRL-SHIFT-ENTER, instead of just ENTER
2. For blank entries, I placed the text marker ' (single apostrophe)... again don't ask, something strange about the MATCH function
3. You'll have to create a 4th conditional formatting to show no formatting for blank entries (=""), bring it to the top and check Stop if True

Man, this formula was really frustrating for such a simple concept... I'm sure others will give you a cleaner formula...

Good Luck...

Dennis

[JBeaucaire]

Your lookup table makes no sense to me, the numbers to not increase as the scores go up, so that's truly odd.

The first part of the problem is easy since the grades are exact, this array formula placed in O2:

=AVERAGE(LOOKUP($D3:$N3, $Y$6:$Y$20, $Z$6:$Z$20))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. If you just press ENTER you will get an error, try again, the answer should be 18.1818

But converting that BACK into a grade would require that table be sorted ascending by the numbers. If you do that, sort the table so the numbers ascend, the first formula above will still work, and this fuller formula will give you back the correct Average score code:

=INDEX($Y$6:$Y$20, MATCH(AVERAGE(LOOKUP($D3:$N3, $Y$6:$Y$20, $Z$6:$Z$20)), $Z$6:$Z$20, 1))

....also an array formula. After entered correctly, you can copy O2 downward.

[JBeaucaire]

This array formula in O2:

=IFERROR(INDEX($Y$6:$Y$20, MATCH(AVERAGE(IF($D3:$N3=$Y$6:$Y$20, $Z$6:$Z$20, "")), $Z$6:$Z$20, 1)), "")

[kitkatkin]

Your lookup table makes no sense to me, the numbers to not increase as the scores go up, so that's truly odd.

Good point - this is the idiosyncracy of the British grading system. So the grades go up in numerical levels 3,4,5 etc but then within each level is a sublevel of c,b,a.

E.g. "just" level 3 = 3c, "secure" level 3 = 3b and a "top level 3 = 3a. Therefore a level 3a is higher than a 3b and once a student gets a level 3a they would next move up to a 4c.

Hence a 3c = 7 points on my lookup table and a 3a=9

[kitkatkin]

But converting that BACK into a grade would require that table be sorted ascending by the numbers. If you do that, sort the table so the numbers ascend, the first formula above will still work, and this fuller formula will give you back the correct Average score code:

=INDEX($Y$6:$Y$20, MATCH(AVERAGE(LOOKUP($D3:$N3, $Y$6:$Y$20, $Z$6:$Z$20)), $Z$6:$Z$20, 1))

....also an array formula. After entered correctly, you can copy O2 downward.

Thank you so much, that works perfectly! There's no way I would have ever figured that out.

Just one last question....if its half way through the year and we've only done say 4 of the 11 tests, how can I get it to show the grade average when some of the cells haven't been completed (e.g H2 to N2),????

[JBeaucaire]

Just sort that table by column Z and it will work correctly. Column Y doesn't need to be ordered.

[JBeaucaire]

Use the formula from post #7, it won't mind the empty cells.

[kitkatkin]

Totally sorted now. Thanks so much! I can go back to marking essays now