# Assign equivalent numerical values to characters and calculate average

1. ## 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.

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.

2. ## Re: Assign equivalent numerical values to characters and calculate average

Seems you only need to adapt the formula to include the extra characters.

=AVERAGE(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"A+","A","A-","B+","B","B-"},{4.25,4,3.35,3,2.75})))

This is an array formula and must be committed using Ctrl+Shift+Enter.

Continue developing the remainder of the formula for the full range of grades.

3. ## Re: Assign equivalent numerical values to characters and calculate average

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

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

4. ## Re: Assign equivalent numerical values to characters and calculate average

LOOKUP requires the lookup array to be in ascending order.

=AVERAGE(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}) )

5. ## Re: Assign equivalent numerical values to characters and calculate average

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

6. ## Re: Assign equivalent numerical values to characters and calculate average

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 ?

7. ## Re: Assign equivalent numerical values to characters and calculate average

=average(lookup(a1:d1, {"a";"a-";"a+";"ab";"b";"b-";"b+";"c";"c-";"c+";"d";"d-";"d+"}, {4;3.75;4.25;false;3;2.75;3.25;2;1.75;2.25;1;0.75;1.25}) )

8. ## Re: Assign equivalent numerical values to characters and calculate average

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

There are currently 1 users browsing this thread. (0 members and 1 guests)