Converting Text To Numbers

[Justin]

Hi

I hope someone can help me with this one.

I'm in the process of creating a spreadsheet which will be
used to give pupils an overal score on specific projects.

For example, the spreadsheet will have around 30 questions
to which I can select Yes - No - N/A, or Poor, Average,
Good, Excellent. The problem I need assistance with, is
how do I convert the selected answer (Poor, Average, Good
etc) to a numerical score?

Any help or advice will be greatly appreciated.

Oh, and no, I'm not teaching IT!

 

[JulieD]

Hi Justin

if you're wanting to "sum" all of the 30 questions and assuming they're in
columns B through AE
you could do something like this:
=(COUNTIF(B2:AE2,"Poor")*1)+(COUNTIF(B2:AE2,"Average")*2)+(COUNTIF(B2:AE2,"G
ood")*3)+(COUNTIF(B2:AE2,"Excellent")*4)

Hope this helps
Cheers
JulieD

 

[Harald Staff]

Hi

One way:
Cell A1: Good
Cell A2: Bad
Cell A3: Ugly

Cell C1: Bad
Cell D1: =MATCH(C1,$A$1:$A$3,0)

HTH. Best wishes Harald

 

[Rodney POWELL]

Justin ...


=IF(A1="Excellent",3,IF(A1="Good",2,IF(A1="Average",1,IF(A1="Poor",0,""))))


Hope it Helps,

- Rodney POWELL
Microsoft MVP - Excel

Beyond Technology
Spring, Texas USA
www.BeyondTechnology.com




Hi

I hope someone can help me with this one.

I'm in the process of creating a spreadsheet which will be
used to give pupils an overal score on specific projects.

For example, the spreadsheet will have around 30 questions
to which I can select Yes - No - N/A, or Poor, Average,
Good, Excellent. The problem I need assistance with, is
how do I convert the selected answer (Poor, Average, Good
etc) to a numerical score?

Any help or advice will be greatly appreciated.

Oh, and no, I'm not teaching IT!

 

[Justin]

Thanks for all of your suggestions, you've made it sound
too easy.

I'll try each option and stick with the best.

Thanks all!

 

[Jonathan Rynd]

how do I convert the selected answer (Poor, Average, Good
etc) to a numerical score?

You don't need any additional cells.

=MATCH(C1,{"Poor","Average","Good"},0)

 

[Gord Dibben]

Justin

One more to consider.

=LOOKUP(A1,{"average";"excellent";"good";"N/A";"no";"poor";"yes"},{40;70;60;10;50;20;30})

NOTE: the values to lookup in the array must be in ascending order.

i.e. a-z in first half of array. Second half of array can be any order.

Gord Dibben Excel MVP

 

[Harald Staff]

You don't need any additional cells.

=MATCH(C1,{"Poor","Average","Good"},0)

Very true. However, I believe that cells with short lists are easier to set
up and maintain than hardcoded formulas are. Each spreadsheet has close to
17 million cells, and you can easily have 200+ of them in a file, so why not
use a few for constants and lists ?

Best wishes Harald

 

[Robert Judge]

I have an EXCEL 2003 worksheet that contains a column that has addresses in
the format "123 Main Street", "345 South Street", etc. I want to convert
that data into two columns. One column would contain the numbers ("123",
etc.) and the other column would contain the text ("Main Street", etc.) I
will appreciate suggestions as to how to accomplish this.

 

[Govind]

Hi,

see your other post.

Govind