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

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

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

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

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

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

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

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