On Mon, 19 Feb 2007 06:47:03 -0800, Ade wrote:

I am trying to record my students' grades by converting the letter grades of
individual tasks (D,M,P,BP) to number (3,2,1,0) and then summing up the
results to get a total for the unit. e.g
Student A gets P,M,P,D,BP for their tasks
this equals 1,2,1,3,0 = 6 points in total.
I have put their individual grades in a spreadsheet but it is taking forever
to convert and calculate each total individually for each student.
I would like to be able to do this as a formula in excel but am driving
myself mad trying to do it. If this makes any sense to anyone, do you have
any suggestions for a formula/formulae that I could use to achieve this?

Thanks

Ade

First of all 1+2+1+3+0 = 7, not 6 <G.


You can use an array formula to get the results.

Assuming that the letter grades are in contiguous cells in a row, and that
there are no blanks, you could use the formula:

=SUM(MATCH(B1:F1,{"BP","P","M","D"},0)-1)

This is an array formula so, after you type or paste it into the cell, hold
down <ctrl<shift while hitting <enter. Excel will place braces {...} around
the formula if you did it correctly.

B1:F1 is the range where the letter grades are entered. Adjust it
appropriately.
--ron