Assign Numeric Value to Text

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


---
Message posted from http://www.ExcelForum.com/

0
1/9/2004 11:49:56 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
1039 Views

Similar Articles

[PageSpeed] 45

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

=SUMPRODUCT(CODE(UPPER($A$1:$A$100))-64)

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

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



"martialtiger >" <<martialtiger.zsgl6@excelforum-nospam.com> wrote in message
news:martialtiger.zsgl6@excelforum-nospam.com...
> 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
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/10/2004 12:24:56 AM
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.


---
Message posted from http://www.ExcelForum.com/

0
1/10/2004 1:21:32 AM
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

"martialtiger >" <<martialtiger.zsktu@excelforum-nospam.com> wrote in
message news:martialtiger.zsktu@excelforum-nospam.com...
> 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.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
alandrob (23)
1/10/2004 1:52:27 AM
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


---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"martialtiger >" <<martialtiger.zsktu@excelforum-nospam.com> wrote in message news:martialtiger.zsktu@excelforum-nospam.com...
> 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.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>



0
dmcritchie (2586)
1/10/2004 2:02:02 AM
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

--
Message posted from http://www.ExcelForum.com

0
1/10/2004 3:30:05 AM
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.

"martialtiger >" <<martialtiger.zsqs3@excelforum-nospam.com> wrote
> You guys are great!  Thanks for help.  Hope you all have a great
> weekend!  I'll be at work :(   But at least you guys made it a whole
> lot easier!


0
dmcritchie (2586)
1/10/2004 4:04:39 AM
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" <ken.wright@NOSPAMntlworld.com> wrote in message
news:eAOpsAx1DHA.1676@TK2MSFTNGP12.phx.gbl...
> For a range A1:A100 with just one letter in each cell:-
>
> =SUMPRODUCT(CODE(UPPER($A$1:$A$100))-64)
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
>
>
> "martialtiger >" <<martialtiger.zsgl6@excelforum-nospam.com> wrote in message
> news:martialtiger.zsgl6@excelforum-nospam.com...
> > 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
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004


0
ken.wright (2489)
1/10/2004 9:05:15 AM
Reply:

Similar Artilces:

How to assign one client to more than one users?
I have to clients A nad B. Moreover I have 4 software developers : P2, P3, P4 and P5. P1 is a chief ( with admin rights ). What I want to do now is : P1 creates an account for client A. P1 wrote some info about A and some notes. P1 wants to assign client A to the P2, P3 and P4. P1 also wants them to see all the notes he had written. Similar to client B, for whom P1 wants to assign P4 and P5 . P1 also wants : -P2 not to see account for B, -P5 not to see the account for A, -P4 to see both accounts A nad B. And here is my question : Is it possible, and if so ( a little exma...

text boxes lose formatting, size, and placement
I am using Office 2010 on a Windows 7 machine. I am working with text boxes and I am having the following problems: 1. When I scroll the text box loses its size, place, and sometimes the text in the box. 2. When I toggle betwee show/hide formatting marks, the same thing happens. 3. The Position button options do not place the text box as expected. Is this a beta issue? Is there some default I need to reset? BTW: the document has sections and columns if that is important to know. Thanks...plc This appears to be a display issue. Word makes surprisingly large demands on the displ...

Is Auto Expansion (i.e., wrap text) of a cell possible when the cell's contents are based on another cell?
I am trying to display the results from one tab (Tab 1) in another (Tab 2). So, for example, tab 2 contains the formula "=Tab1!A2". However, when the results are too large to fit the cell in tab 2 (i.e., the cell that contains a formula that draws from a cell in tab 1), the wrap text feature does not work unless i first double click in the cell in tab 2. Is there any way around this? Can the wrap text feature work automatically somehow? Or will i need to double click in every cell that contains text that doesn't fit into the cell. Thanks for any suggestions, or VBA code, th...

Table Default Values
Is it possible to set the default value of a field in the table to either a value entered into a message box or a value entered into another table? I'm importing data from a text file into an existing table. I have been manually changing the default value for the report date field each time I import a new report, but since I won't be doing the importing anymore, would like to automate or simplify this process. Any suggestions? Hi, If you want the current date you can just use =Date(). If some other date, you can do something like this: Public Function Lo...

Number and Text issues
I have an .xls file that has a list of clients that I have filtered and sorted. In column "A", there is a list of numbers, some that have preceeding zeros and some that do not, but all of the numbers are of the same length. I have created a macro in another .xlsm spreadsheet (driven by commandbutton located on a userform) which allows the user to enter (via inputbox) a number and it will search the .xls file and return to the userform the information which is in the same row as the number entered by the client. This macro takes all of the values in column "A&q...

Auto Assigning Categories
Is there a way to turn off the auto assignment of categories for downloaded transactions in Money 2006? We shop at Walmart, Costco, etc. where we purchase many different goods. We split most of these transactions into multiple categories. Money remembers the last split we have made and automatically assigns that split/category to the next dowloaded transaction from that store. I would rather assign cateogories myself and remove the "smart" feature of money. I would appreciate any input or suggestions. Doug "Doug Moller" <DougMoller@discussions.microsoft.com...

Linking text to style for auto update
I want to auto update figure/table number in text to that in Figure/Table style numbers Use Insert > Cross Reference to put the numbers into the text; they don't update every time the numbering changes, but only when you tell them to (Ctrl-A, F9) or when you print or switch to Print Preview. On Dec 18, 4:05=A0pm, Taurus <Tau...@discussions.microsoft.com> wrote: > I want to auto update figure/table number in text to that in Figure/Table > style numbers ...

initialized values for member variables
First I need to confirm two facts before I ask my question - if my "facts" are wrong, then please still try to answer my question and please tell me which fact is incorrect (potentially both): FACT: MVC does NOT support initializing member variables at declaration - i.e.: class Test { int intVar = 42; }; FACT: The C++ standard DOES support intializing member variables at declaration - i.e.: class Test { int intVar = 42; }; If this is the case, does Microsoft have a work-around? Other than 1) putting the initialization code for each of the member variables in a member function...

Counting values from vlup giving results
Hello I managed to create a vlookup table on the source sheet and another where data is chosen on the users sheet but to advance is too technical. I need to display certain data chosen from the dropdown list to display in another column on the users sheet. Party1 and Party3 to display in column G. Item Party1 and Party3 (in column A) in the vlup have a code Y (column C) in the vlup source. Only need about 15rows to display Y and N item results listed in the users sheet. ie. Party 1 & 3 need to be displayed, summed and counted on the users sheet as they are both coded as Y. A | B | ...

saving value of variable on assert
Hi, Can I save the value of a variable on an assert abort? My program terminates in debug mode on as assert statement. The JIT debugger indicates that the program logic fails when parsing the contents of a CString pointer (though this is not the direct reason for the failure). The CString is a valid string - but my program logic fails to handle a given format ( I am parsing the string expecting a given format). The pointer points to a downloaded HTML page and so the string is large. Is it possible to save the buffer contents of the string into a text file for analysis of the page format?...

Error in setting value of a range
I have a sheet called Step2 and I am using the following statement Globals.Step2.Range("A6").Value ="Test" This used to work before with VSTO 2005 Beta 1. I am now using Beta 2. I get the following error System.Runtime.InteropServices.COMException occurred ErrorCode=-2146827284 Message="Exception from HRESULT: 0x800A03EC" Source="Microsoft.Office.Interop.Excel" StackTrace: at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2) When I type ?globals.Step1.Range("A1").Value in the immediate pane I g...

Cell value = sheet name
What's the formula to put the sheet name into a cell? Thank you, MB Workbook needs to be saved first =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,34) -- Regards, Peo Sjoblom "anonymous" <anonymous@discussions.microsoft.com> wrote in message news:05b301c53946$cdc04130$a501280a@phx.gbl... > What's the formula to put the sheet name into a cell? > > Thank you, > > MB take a look here: http://www.mcgimpsey.com/excel/formulae/cell_function.html In article <05b301c53946$cdc04130$a501280a@phx.gbl&...

interlinear or parallel text
I need to prepare a document in two languages, where the text would go in alternate lines. From the googling I have done, it could be called parallel corpora, or interlinear or parallel text. There seems to be a lot of research on translation software that would do it: http://research.microsoft.com/apps/pubs/default.aspx?id=75238 http://tcc.itc.it/people/forner/multilingualcorpora.html However, my case is much simpler (I think). I'll try to keep it short: I have a leaflet in English but I need to add Polish text directly below English for the people who don't speak Engl...

filling intermittent blank cells with last value
I have a number of columns of data. All cells in a column are filled intermittently. How do you fill cells with the last value shown. Hi John - Assuming your data goes from row 2 to at most row 1000, insert this formula in cell A1; don't type the curly brackets, just the part within them, then hold down Ctrl+Shift while pressing enter. If you do this properly, it creates an array formula, which Excel indicates with the brackets. {=INDEX(A2:A1000,MAX(ROW(A2:A1000)*NOT(ISBLANK(A2:A1000)))-1)} - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/...

Macro/VBA to loop through list of values and output row- Help Need
I am new to VBA/Macros and really don't know how to get this to work. I have a spreadsheet which calculates distances between two postcodes for a list of suppliers. Each supplier can have one or more branches at different locations (postcodes). I need to return only the closest branch of each supplier (i.e. copy the row of the closest supplier to a new sheet. The sheet has 6 columns. Only 2 of these columns are needed to work out this problem. Column A holds the Supplier Name and Column F holds the distance from us. I have worked out the steps needed to complete this...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

Can you highlight text in Publisher 2007 (like you can in Word)?
When using Word, you can highlight text. Can this be done in Publisher 2007? If so, how? Create a fat line, send it to the back. Or a rectangle, fill with a color, no line, send to the back. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Beach Lover" <BeachLover@discussions.microsoft.com> wrote in message news:2021DDC7-80B5-4AB7-BAF0-4FDD27FB8EA0@microsoft.com... > When using Word, you can highlight text. Can this be done in Publisher 2007? > If so, how? This work around is great.....THANKS! "Ma...

How do I italicize just part of the text in the legend?
I have text in a legend -- part of which needs to be italicized and the other part does not. Any one know how to do this? Hi, You can not format individual characters within the legend text. You could build your own legend using textboxes or datalabels. Cheers Andy legend wrote: > I have text in a legend -- part of which needs to be italicized and the other > part does not. Any one know how to do this? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

findin values
Hi i have a spreadsheet that is set up as a grid column B is faults columns c to z are hours(24) in the faults column i have ten faults So we have FAULTS 1 2 3 4 5 6 7 Fault 1 1 2 Fault 2 1 4 Fault 3 2 ~~ ~~ ~~ totals 1 5 4 what i want to do is look through column c(hour 1) and return only the faults(column B) and Qty where a fault has something other than zero or Blank i need to ignore the totals can anyone please point me in the right direction? thanks kevin Hi Kevin Select A1:Z1 Data>Filter>Autofilter Choose drop down on hour r...

wrap text inserts a padding blank line
I use Excel 2003 (11.5612.5606). I thought I had a row autofit problem; I actually seem to have a wrap text problem. I have a cell in which the text can fit in one line; when wrap text is set, the cell is two lines deep. I have seen many descriptions of this problem; I have seen neither explanation nor solution. To demonstrate the problem: 1) open a new workbook; 2) Either: a) in A1, set the column width to 600 pixels, the font as Tahoma, Bold, Italic and the text on one line to 12345678 1 2345678 2 2345678 3 2345678 4 2345678 5 2345678 6 2345678 7 2345678 8 2 At this point, the text is in o...

Ct:1 Appears in Left Join for all values rather than selected valu
Hi, I have a table called tblCodeBlue that has values such as AcctNumber, Unit, and CodeBlueDate. I created a query called qryCodeBlue1 with AccoutNumber, Unit, Month (Calculated using CodeBlueDate). I am importing my data into another system so I need a numeric value so I also added Ct:1 for Count with a value of 1. I also have a table called tblMonths that has Month and Unit as fields. There are eight units in our facility and we report on data going back to 1/1/08 so this table has eight records for each month from 1/1/08 to 12/1/10. I created a query called qryCodeBlue2...

how to treat string value as a number
how to handle string as a number equivalent to Integer.parseInt or Float.parseFloat as in Java. Thanks. have you been given work assignments? what Excel problem does this relate to? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "kang" <kang@hao.com> wrote in message news:elWq%23KdyHHA.1188@TK2MSFTNGP04.phx.gbl... > how to handle string as a number > equivalent to Integer.parseInt or Float.parseFloat as in Java. > > Thanks. nope. when I add the values it gives some strange values. Bob Phillips wrote: > ha...

REFORMAT A TEXT CELL #2
HI, I NEED TO REFORMAT A CELL TO INCLUDE A "^" AND A "(" AT THE BEGINNING OF THE CELL AND A ")" AT THE END. EXAMPLE OLD: JONES EXAMPLE NEW ^(JONES) ANY HELP IS GREATLY APPRECIATED -- Jerry Arnone PMP, IT PROJECT+, CCNA, MCSE, CCA, CNA, A+, SECURITY+, MS Project White Belt JARNONE@BELLSOUTH.NET Try custom format "^("@")" Regards, Peo Sjoblom "Jerry Arnone, PMP" wrote: > HI, > > I NEED TO REFORMAT A CELL TO INCLUDE A "^" AND A "(" AT THE BEGINNING OF THE > CELL AND A ")" AT TH...

I need to replace/remove quotes from text...
I'm trying to use this in a query, but I can't figure out how to remove the quotes around text ie "Nashville" need to be Nashville. Company_Name: Replace([CompanyName]," ","") I'm using Access 2003 Thanks Kelvin Replace( CompanyName, """", "" ) Hoping it may help, Vanderghast, Access MVP "Kelvin" <someone@domain.com> wrote in message news:uNxdTTGLIHA.2176@TK2MSFTNGP06.phx.gbl... > I'm trying to use this in a query, but I can't figure out how to remove > the quotes around text ie ...

validation list drop down box, how do I bring in text commands
I have a reference set in excel that is two columns. The first column lists functions in text (i.e. AVERAGE, COUNT, COUNTA, MAX, MIN....). The second column lists the corresponding function number (i.e. AVG = 1, COUNT =2....). I made a drop down box using data, validation, list so that the function numbers appear in a row. The row below I put in subtotal(grab command from drop down, data set to calc). I can't figure out how to return the TEXT from my columns above, rather than function number. I want my drop down to have AVERAGE, not 1. Hi Scott, Debra Dalgleish has pages ...