Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
not calculating formulas | Excel Discussion (Misc queries) | |||
calculating SUM with two conditions | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) |