Assign Numeric Value to Text

  • Thread starter Thread starter martialtiger
  • Start date Start date
M

martialtiger

Hi Everyone,

I am looking for a way to assign numberical values to 2 different
letters (i.e. A equal to 1, B equal to 2). Then what I am looking to
do with this is add up, all the letters in numerical value, in a column
and get a total on 1 row and then the average on a 2nd row. Any and
all help is greatly appreciated.

Happy New Years!
:D
 
For a range A1:A100 with just one letter in each cell:-

=SUMPRODUCT(CODE(UPPER($A$1:$A$100))-64)
 
I'm sorry... i do not understand your post. Let me go into detail as
far as what I'm looking to do. I'm creating a spreadsheet to QA my
sales dept. one of the columns is for Pass/Fail (P, F). I want to
assign a value of 8 for Pass and O for Fail. Then what I want to do
with it is total the column and then get an average.
 
In B5:B15 I put all my P & Fs Pass and Fail
In C5 I put the formula =IF(B5="P",8,0)
I drag this down to populate C5:C15
In C16 I put =SUM(C5:C15)
In C17 I put =AVERAGE(C5:C15)
Alan
 
The answer previously provided was continuing your
assignment of values 3 for "C", 4 for "D" etc.
You can see a table of ASCII values at
http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm

You gave the letter "o" as a numeric value for Fail.

Anyway from what I see you just need to count the P's and
the F's. as in the range F2:F7 with the formula in F8
F8: =COUNTIF($F$2:OFFSET(F8,-1,0),"P") * 8
/ (COUNTIF($F$2:OFFSET(F8,-1,0),"P") +
COUNTIF($F$2:OFFSET(F8,-1,0),"F") )

The above is not case sensitive, but would be sensitive to
extraneous spaces.

John Walkenbach has a good page on COUNTA and COUNTIF

COUNT, Tip 52:Cell Counting Techniques
http://www.j-walk.com/ss/excel/tips/tip52.htm
range using: DCOUNT, COUNT, COUNTA, COUNTBLANK, COUNTIF, and includes one ...
=COUNTA(D$2:D$500-COUNTIF(D$2:D$500,"y*")-COUNTIF(D$2:D$500,"n*")
=COUNTA(A2:A100)-SUM(COUNTIF(A2:A100,{"yes","y","no","n"}))
count of nonblank cells - cells beginning with y - cells beginning with n
=COUNTA(A2:A100)-SUM(N(A2:A100={"yes","y","no","n"})) -- array formula Alt+Enter
Also see Summing and Counting Using Multiple Criteria (tip 74)
http://www.j-walk.com/ss/excel/tips/tip74.htm
And an article for Microsoft Office -- Count and Sum Your Data in Excel
http://www.microsoft.com/office/using/column10.asp
 
You guys are great! Thanks for help. Hope you all have a grea
weekend! I'll be at work :( But at least you guys made it a whol
lot easier
 
Thanks, you're welcome. At least it's late enough that if I didn't
answer the question the Australians take over -- in theory
anyway. I don't think anyone actually sleeps here any more.
 
LOL You changed the rules. Dave has given you answers, but here are a couple
more:-

Assuming data in A1:A100

=(COUNTIF($A$1:$A$100,"P")*8)/COUNTA($A$1:$A$100)
This works even with blanks

or

=AVERAGE(IF($F$2:$F$7="P",8,0)) array entered using CTRL+SHIFT+ENTER
Does not work if there are any blanks in the range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Ken Wright said:
For a range A1:A100 with just one letter in each cell:-

=SUMPRODUCT(CODE(UPPER($A$1:$A$100))-64)
Click to expand...
 
Back
Top