| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Letter to Numeric Value
I have imported a text file with study participant responses that were coded
A,B,C,D,E. In order to conduct a statistical analysis I need to convert these to numeric values A=1, etc. I went to the CODE function but this codes A=65, etc.and only codes one cell at a time. What function or formula is necessary to code for my desired values and accomplished for the dataset. Thanks. -- Larry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Letter to Numeric Value
Larry
In any cell enter =LOOKUP(B1,{"A","B","C","D","E"}, {1,2,3,4,5}) Adjust B1 to suit. Can be copied down a column if the column contains letters. Not case-sensitive. Gord Dibben MS Excel MVP On Thu, 19 Jul 2007 15:38:00 -0700, Larry wrote: I have imported a text file with study participant responses that were coded A,B,C,D,E. In order to conduct a statistical analysis I need to convert these to numeric values A=1, etc. I went to the CODE function but this codes A=65, etc.and only codes one cell at a time. What function or formula is necessary to code for my desired values and accomplished for the dataset. Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Letter to Numeric Value
If you have a range of cells that contain only one of these single letters:
A,B,C,D,E Select the range of cells in question Goto the menu EditReplace Find what: A Replace with: 1 Replace all Repeat for the other letters. -- Biff Microsoft Excel MVP "Larry" wrote in message ... I have imported a text file with study participant responses that were coded A,B,C,D,E. In order to conduct a statistical analysis I need to convert these to numeric values A=1, etc. I went to the CODE function but this codes A=65, etc.and only codes one cell at a time. What function or formula is necessary to code for my desired values and accomplished for the dataset. Thanks. -- Larry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Letter to Numeric Value
Are they in the same cell or do you mean each cell has either A or B or C
etc? If there are one letter per cell and you want to sum the cells it is easy =SUMPRODUCT(COUNTIF(A1:A10,{"A";"B";"C";"D";"E"}), {1;2;3;4;5}) will sum A1:A10 -- Regards, Peo Sjoblom "Larry" wrote in message ... I have imported a text file with study participant responses that were coded A,B,C,D,E. In order to conduct a statistical analysis I need to convert these to numeric values A=1, etc. I went to the CODE function but this codes A=65, etc.and only codes one cell at a time. What function or formula is necessary to code for my desired values and accomplished for the dataset. Thanks. -- Larry |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Letter to Numeric Value
Just subtract 64 from the value that CODE gives you to yield 1-5 for A-
E. Copy the formula down. Hope this helps. Pete On Jul 19, 11:38 pm, Larry wrote: I have imported a text file with study participant responses that were coded A,B,C,D,E. In order to conduct a statistical analysis I need to convert these to numeric values A=1, etc. I went to the CODE function but this codes A=65, etc.and only codes one cell at a time. What function or formula is necessary to code for my desired values and accomplished for the dataset. Thanks. -- Larry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting Letter Grade to Numerical Value | Excel Discussion (Misc queries) | |||
| Formula involving a letter that has to have a numeric value | Excel Worksheet Functions | |||
| Vlookup using letter and numeric codes | Excel Discussion (Misc queries) | |||
| How do I assign a numeric value to a text letter | Excel Discussion (Misc queries) | |||
| Converting Letter Grades to Numeric | Excel Worksheet Functions | |||
Linear Mode
