Convert Letter to Number, then Calculate Average

Greetings all,

I have a problems that would be great if someone could solve.

I've got a table that contains a number of dates and below that, an alphabetic grade that a person received on each date. I need to find a way to convert each alphabetic grade to a numeric grade and then get the average of that numeric grade to appear in the 'average' cell at the end.

I know how to convert alphabetic to numeric and have that result appear in another column, but I need to just have the average of all the grades appear and I don't want to add any new columns. I know it's possible, but I can't get it to work.

The alphabetic grades and their equivalents are: U=49.9, DC:62.5, VC=80, SC=90, EC=95.

I've attached a simplified version of the doc.

Thanks for your help in advance.

Cheers

Try:

=SUMPRODUCT((B4:I4=B10:B14)*(C10:C14))/SUMPRODUCT(--(B4:I4=B10:B14))

or if you arrange your table so that first column is in ascending order, then

=AVERAGE(LOOKUP(B4:I4,B10:C14))

confirmed with CTRL+SHIFT+ENTER not just ENTER

EDIT: NBVC's has better error checking in the formula. Go with that.

What are the necessary changes you made?

The second formula needs to be confirmed with CTRL+SHIFT+ENTER keys so that { } brackets appear around the formula.

You referenced the wrong range in the denominator...

s.b. =SUMPRODUCT((B3:Y3=A7:A11)*(B7:B11))/SUMPRODUCT(--(B3:Y3=A7:A11))

=SUMPRODUCT((B3:Y3=$A$7:$A$11)*($B$7:$B$11))/SUMPRODUCT(--(B3:Y3=$B$7:$B$11))

would allow you to copy it down!

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.