Desperate! HELP... converting text to numbers
 

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

Desperate! HELP... converting text to numbers
 

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.

"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

Desperate! HELP... converting text to numbers
 

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.

--
Gary's Student
gsnu200707


"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

Desperate! HELP... converting text to numbers
 

Lookup will convert the letters to number

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

"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

Desperate! HELP... converting text to numbers
 

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

Desperate! HELP... converting text to numbers
 

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


"Gary''s Student" wrote:

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.

--
Gary's Student
gsnu200707


"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

Desperate! HELP... converting text to numbers
 

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)

--
Regards

Roger Govier


"Ade" wrote in message
...
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

Desperate! HELP... converting text to numbers
 

On Mon, 19 Feb 2007 10:02:35 -0500, Ron Rosenfeld
wrote:

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


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

Desperate! HELP... converting text to numbers
 

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.
--
Gary''s Student
gsnu200707


"Ade" wrote:

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


"Gary''s Student" wrote:

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.

--
Gary's Student
gsnu200707


"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

... converting text to numbers YAYYYYY!!!
 

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


"Ron Rosenfeld" wrote:

On Mon, 19 Feb 2007 10:02:35 -0500, Ron Rosenfeld
wrote:

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


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

... converting text to numbers YAYYYYY!!!
 

On Mon, 19 Feb 2007 07:54:08 -0800, Ade wrote:

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