Calculating a sum of various lenght
 

I want to be able to place a word and assign a value to it according to the
position of its letters in the alphabet.

ie ONE = 15 for O + 14 for N + 5 for E = 34
TWO = 58

I have developed the formula
SUM(FIND(MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14, 15,16},1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-(16-LEN(A1))

is there some way to modify it so it says sum(find(mid(a1,{1..len(a1)},1)

Alan

Alternatively, you could use a user-defined function. Paste the code into a
VBA module.

Function WordSum(x As String)
On Error Resume Next
x = UCase(x)
WordSum = Asc(Right(x, 1)) - 64 + _
WordSum(Left(x, Len(x) - 1))
End Function


To use enter =WordSum(A1) where A1 contains the word you want to evaluate.


"ASA" wrote:

I want to be able to place a word and assign a value to it according to the
position of its letters in the alphabet.

ie ONE = 15 for O + 14 for N + 5 for E = 34
TWO = 58

I have developed the formula
SUM(FIND(MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14, 15,16},1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-(16-LEN(A1))

is there some way to modify it so it says sum(find(mid(a1,{1..len(a1)},1)

Alan

If it's possible you will have spaces in your text you can replace

x = UCase(x)

with

x = UCase(Replace(x, " ", "", 1, -1, vbTextCompare))


"ASA" wrote:

I want to be able to place a word and assign a value to it according to the
position of its letters in the alphabet.

ie ONE = 15 for O + 14 for N + 5 for E = 34
TWO = 58

I have developed the formula
SUM(FIND(MID(A1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14, 15,16},1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-(16-LEN(A1))

is there some way to modify it so it says sum(find(mid(a1,{1..len(a1)},1)

Alan

Hi Alan,

Try,

=SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-64)

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=386676