Desperate! HELP... converting text to numbers

[Guest]

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

 

[Guest]

probably quite easy but it would be helpfil to know where the P,M,P,D,BP are.
Are they in separate cells or a string in a single cell. What is the
significance of BP, is it always zero, if so it can be ignored but it is more
complicated if it can have 2 values.

 

[Guest]

Let's say the students individual grades are in column B with only one grade
per cell. In C1 enter:
=(B1="D")*3+(B1="M")*2+(B1="P")
and copy down

column C will have the equivalent grades in numerical form. you can use the
=SUM() function to add them up.

 

[Guest]

Lookup will convert the letters to number

=LOOKUP(a3,{"D","M","P","BP";3,2,1,0})

 

[Ron Rosenfeld]

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

 

[Guest]

Wow!Thanks so much for your replies Mike,Gary and Joel, but methinks I've got
a block somewhere cos it aint working!
In response to Mike, the grades are in individual cells like this

AO1 AO2 AO3 AO4 AO5 AO6 Points Fin.Grade
M M M M M M
P M M M M M
M M M M M BP
D P P P M P

In the points column, I need to add up the letters as numbers as indicated
previously... does this make more sense?
If I use the formula given to me by Gary" it doesn't account for the
variation in grades and enable me to sum up in an additional column?

still confused

Ade

 

[Roger Govier]

Hi

With your grades in B2:F2 enter in G2
=LOOKUP(B2,{"BP",0;"D",3;"M",2;"P",1})
copy across through C2:K2
In L2 enter
=SUM(G2:K2)

For your example it returns 7 (not 6 as you state)

 

[Ron Rosenfeld]

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

Using your table of grades previously posted, and adjusting to include column
A, my formula gives the following results:


AO1 AO2 AO3 AO4 AO5 AO6 Points Fin.Grade
M M M M M M 12
P M M M M M 11
M M M M M BP 10
D P P P M P 9

--ron

 

[Guest]

You are lucky the data is in a nice rectangular block.

1. Hi-light the data
2. Pull-down
Edit > Replace
Find What : BP
Replace with: 0

Then use Find/Replace to change the Ps into 1s
Then use Find/Replace to change the Ms into 2s
Then use Find/Replace to change the Ds into 3s

You started with a table of letters and will have a table of numbers.

 

[Guest]

Ron you are a genius!! It worked perfectly...
'scuse the maths!
Thanks everyone for your responses... I am once again whole!

 

[Ron Rosenfeld]

Ron you are a genius!! It worked perfectly...
'scuse the maths!
Thanks everyone for your responses... I am once again whole!

Glad to help. Thanks for the feedback.
--ron