Assign equivalent numerical values to characters and calculate average

Hi,

I need to calculate student average of large dataset using excel.
It includes numerical equivalent for several characters such as A+ = 4.25, A = 4, A- =3.75, B+ = 3.25 and so on.

Information on the thread

http://www.excelforum.com/excel-work...d-average.html

really helpful to me to get an idea.

But the above method is not working for it. May be that includes + and - signs with character.

is there any solution for that.

Lot of thanks on the given formula. I already tried this method. But it does not work due to the additional + and - marks with characters.

this method works only when
=AVERAGE(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"A","B"},{4,3}))) and got answers correctly.

I want to improve this for
=AVERAGE(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"A+","A","A-","B+","B","B-"},{4.25,4,3.35,3,2.75})))

Need suggestions on this...

thanks

wow... This method perfectly works on my problem

thank so much for this help... Finally it becomes to a few second calculation..
Thank you so much again

how can improve the code to calculate average of number of cells when there are terms include such as AB (for student absent in exam) in between those cells.

need to exclude those cells and calc average from other data...
Try with AVERAGEIF as follows

=AVERAGEIF(LOOKUP(A1:D1, {"A";"A-";"A+";"B";"B-";"B+";"C";"C-";"C+";"D";"D-";"D+"}, {4;3.75;4.25;3;2.75;3.25;2;1.75;2.25;1;0.75;1.25}),"<>*(AB)" )

but it doesn't work ...Is there any method to overcome this ?

Well it works nice & smooth... Thanx a lot