Convert Grades to points and add up?

Question

Level 1

12 points

Convert Grades to points and add up?

Hi,

I am a teacher and by assigning point value to grades I want to be able to see the average point score for all the subjects. I can then look at any one subject, and compare how well they did in it to their average grade score.

Here is my table with the total point score and then an average one at the end. Can anyone help with this?? I use a Lookup function for something similar, but that is only a single value; here it is multiple.

Many thanks,

Greg

iMac, iOS 6.1.2

Posted on Oct 12, 2015 11:48 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,276 points

Oct 12, 2015 1:07 PM in response to KippMajors78

You could do something like this:

The formula in M2:

=VLOOKUP(B2,Grade Value::$A:$B,2,0)

In K2:

=SUM(M2:U2)

In L2:

=AVERAGE(M2:U2)

You can hide columns M through U if you wish.

SG

Edit: If you want a letter grade "average" in L2 instead of a number grade you could try this:

=LOOKUP(AVERAGE(M2:U2),Grade Value::$B,Grade Value::$A)

Reply

Answer 2

KippMajors78 Author

Level 1

12 points

Oct 12, 2015 1:17 PM in response to SGIII

Hi SG,

Thanks for the very swift response - looking at my previous posts, it always seems to be you that solves my problems! So many thanks to you!!!

One final query - as some students take more subjects that others, how do I account for the black subject spaces. Here it generates an error codes.

I could manually adjust the parameters for each student depending on how many grades they have, but that is the long way round.

I don't want to add a '0' to them either as it will artificially alter the grade point average.

So, is there a way to make it ignore any blank spaces in the subject grade column? Perhaps by adding something to the lookup table - but again being wary of adding a number that will alter the APS....

Many thanks again,

Greg

Reply

Answer 3

SGIII

Level 8

35,276 points

Oct 12, 2015 10:56 PM in response to KippMajors78

KippMajors78 wrote:

So, is there a way to make it ignore any blank spaces in the subject grade column? Perhaps by adding something to the lookup table - but again being wary of adding a number that will alter the APS....

Hi Greg,

Here's one way to approach that problem:

In L2:

=K2÷COUNTIF(M2:U2,"<>"&"")

And in M2 copied right:

=IFERROR(VLOOKUP(B2,Grade Value::$A:$B,2,0),"")

Wrapping the formulas in M2:U2 in IFERROR turns them into blanks (the "") if there is a blank in the corresponding cell in B2:J2. That's because the VLOOKUP can't find a blank in the lookup table and duly complains with an error. IFERROR sees that and turns the cell's value to a blank.

Then the average is calculated by taking the sum and dividing it by the number of cells in M2:U2 that are not blank. The COUNTIF is set up to count the cells that are not blank.

If you have more than 9 subjects you could add columns and extend the ranges in the formulas.

SG

Reply