Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
Hi,
I have a school project that I'd like to produce a Excel formula for. We have two columns, the first has letters and the second has numbers eg. cell A1 = A, cell B1 = 1, cell A2 = B and cell B2 = 2 and so on to Z = 26. I have a simple look up formula that sums the value of numbers assigned to each letter entered into several cells but I'd like to formula (thing an array) whereby I type a word into a single cell and in another cell the value is shown? The letters entered could contain spaces eg a first name and surname and I guess shouldn't be longer than 26 characters. Any points most welcome, Rob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
Is this what you mean
=SUMPRODUCT(--(MATCH(UPPER(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1) ),A1:A26,0)) ) -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Hi, I have a school project that I'd like to produce a Excel formula for. We have two columns, the first has letters and the second has numbers eg. cell A1 = A, cell B1 = 1, cell A2 = B and cell B2 = 2 and so on to Z = 26. I have a simple look up formula that sums the value of numbers assigned to each letter entered into several cells but I'd like to formula (thing an array) whereby I type a word into a single cell and in another cell the value is shown? The letters entered could contain spaces eg a first name and surname and I guess shouldn't be longer than 26 characters. Any points most welcome, Rob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
On Sun, 8 Jan 2006 16:08:08 -0000, "Rob"
wrote: Hi, I have a school project that I'd like to produce a Excel formula for. We have two columns, the first has letters and the second has numbers eg. cell A1 = A, cell B1 = 1, cell A2 = B and cell B2 = 2 and so on to Z = 26. I have a simple look up formula that sums the value of numbers assigned to each letter entered into several cells but I'd like to formula (thing an array) whereby I type a word into a single cell and in another cell the value is shown? The letters entered could contain spaces eg a first name and surname and I guess shouldn't be longer than 26 characters. Any points most welcome, Rob With your word in A1, and assuming the spaces should be ignored: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-64) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
Hi Bob
The OP said it contain spaces so maybe a modification to =SUMPRODUCT(--(MATCH(UPPER(MID(SUBSTITUTE(C1," ",""), ROW(INDIRECT("1:"&LEN(SUBSTITUTE(C1," ","")))),1)),A1:A26,0))) -- Regards Roger Govier "Bob Phillips" wrote in message ... Is this what you mean =SUMPRODUCT(--(MATCH(UPPER(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1) ),A1:A26,0)) ) -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... Hi, I have a school project that I'd like to produce a Excel formula for. We have two columns, the first has letters and the second has numbers eg. cell A1 = A, cell B1 = 1, cell A2 = B and cell B2 = 2 and so on to Z = 26. I have a simple look up formula that sums the value of numbers assigned to each letter entered into several cells but I'd like to formula (thing an array) whereby I type a word into a single cell and in another cell the value is shown? The letters entered could contain spaces eg a first name and surname and I guess shouldn't be longer than 26 characters. Any points most welcome, Rob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
Hi Ron
I don't think that would ignore the spaces, it would reduce the total value by 32 for each space found wouldn't it? I think you would need to use SUBSTITUTE(A1," ","") for each occurrence of (A1) in your formula, or add to the formula +(LEN(A1)-LEN(SUBSTITUTE(A1," ","") ))*32 -- Regards Roger Govier "Ron Rosenfeld" wrote in message ... On Sun, 8 Jan 2006 16:08:08 -0000, "Rob" wrote: Hi, I have a school project that I'd like to produce a Excel formula for. We have two columns, the first has letters and the second has numbers eg. cell A1 = A, cell B1 = 1, cell A2 = B and cell B2 = 2 and so on to Z = 26. I have a simple look up formula that sums the value of numbers assigned to each letter entered into several cells but I'd like to formula (thing an array) whereby I type a word into a single cell and in another cell the value is shown? The letters entered could contain spaces eg a first name and surname and I guess shouldn't be longer than 26 characters. Any points most welcome, Rob With your word in A1, and assuming the spaces should be ignored: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-64) --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
On Sun, 8 Jan 2006 17:24:55 -0000, "Roger Govier"
wrote: Hi Ron I don't think that would ignore the spaces, it would reduce the total value by 32 for each space found wouldn't it? I think you would need to use SUBSTITUTE(A1," ","") for each occurrence of (A1) in your formula, or add to the formula +(LEN(A1)-LEN(SUBSTITUTE(A1," ","") ))*32 I pasted in the wrong formula <sheepish grin =SUMPRODUCT(CODE(UPPER(MID(SUBSTITUTE(A1," ",""), ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1)))-64) Thanks for picking that up. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
Hi Ron
All too easily done.<bg I had been playing with exactly the same formula myself, and just getting round to the substitute, when I noticed Bob's posting. Rather than posting another solution to the OP, I chose to try to correct Bob's. If I hadn't already been playing with the problem, I probably wouldn't have noticed either error. -- Regards Roger Govier "Ron Rosenfeld" wrote in message ... On Sun, 8 Jan 2006 17:24:55 -0000, "Roger Govier" wrote: Hi Ron I don't think that would ignore the spaces, it would reduce the total value by 32 for each space found wouldn't it? I think you would need to use SUBSTITUTE(A1," ","") for each occurrence of (A1) in your formula, or add to the formula +(LEN(A1)-LEN(SUBSTITUTE(A1," ","") ))*32 I pasted in the wrong formula <sheepish grin =SUMPRODUCT(CODE(UPPER(MID(SUBSTITUTE(A1," ",""), ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1)))-64) Thanks for picking that up. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup word and value
Thanks to all the suggestions, I'll play with them to find the answer I'm
looking for. Thanks, Rob "Rob" wrote in message ... Hi, I have a school project that I'd like to produce a Excel formula for. We have two columns, the first has letters and the second has numbers eg. cell A1 = A, cell B1 = 1, cell A2 = B and cell B2 = 2 and so on to Z = 26. I have a simple look up formula that sums the value of numbers assigned to each letter entered into several cells but I'd like to formula (thing an array) whereby I type a word into a single cell and in another cell the value is shown? The letters entered could contain spaces eg a first name and surname and I guess shouldn't be longer than 26 characters. Any points most welcome, Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|