Sum of the alphabet's assigned values till 2 digits in single cell

[Velks]

Hi Experts,

I want to Sum the alphabet's assigned values till 2 digits, where each alphabets are assigned with value which can be changed as and when needed.

Below example might give more clarity on my question. Sorry for my poor language.

I'm having the word "ABCDE" in cell "A2" and I want the Result as "21" in "B2".

Assigned Values are...

A=1 B=2, C=3, D=4, E=5, etc.,

Calculation:

ABCDE
1+2+3+4+5
3+5+7+9
8+3+7
2+1. 21 is the expected result.

BCD
234
57

for ABC 57 is the Result and for DCB 75.

DCB
432
75

Note:
All the double digits are converted in to single digits like 12 as 3, 16 as 7 and so on by MOD 9.

I'm ready to use ASCII codes to assign the dynamic values for alphbats.
Thanks in advance.

 

[Tetra201]

See if the following UDF works for you. Usage: =WordSum(A2,1) where 1 is the Value Set number.
The UDF currently has two value sets that you posted; it can be easily expanded with additional ones.

Option Base 1
Function WordSum(InpStr As String, Optional SetNo As Byte = 1) As Byte
    SymArr = Array(32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126)
    ValArr1 = Array(0, 0, 0, 0, 0, 0, 10, 0, 0, 0, 13, 14, 0, 0, 0, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 0, 0, 0, 0, 0, 3, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 0, 0, 0, 0, 0, 3, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 0, 0, 0, 0)
    ValArr2 = Array(0, 0, 0, 0, 0, 0, 10, 0, 0, 0, 10, 20, 0, 0, 0, 3, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 0, 0, 0, 0, 0, 5, 1, 2, 3, 4, 5, 8, 3, 5, 1, 1, 2, 3, 4, 5, 7, 8, 1, 2, 3, 4, 6, 6, 6, 5, 1, 7, 0, 0, 0, 0, 0, 5, 1, 2, 3, 4, 5, 8, 3, 5, 1, 1, 2, 3, 4, 5, 7, 8, 1, 2, 3, 4, 6, 6, 6, 5, 1, 7, 0, 0, 0, 0)
    ValArr = Array(ValArr1, ValArr2)
    n = Len(InpStr)
    If n = 0 Then Exit Function
    ReDim TmpArr(1 To n)
    For i = 1 To n
        TmpArr(i) = ValArr(SetNo)(Application.Match(Asc(Mid(InpStr, i, 1)), SymArr, 0))
    Next i
    Select Case n
        Case 1: WordSum = TmpArr(1): Exit Function
        Case 2:
        Case Else
            Do
            n = n - 1
            For i = 1 To n
                TmpArr(i) = ((TmpArr(i) + TmpArr(i + 1) - 1) Mod 9) + 1
            Next i
            Loop Until n = 2
    End Select
    WordSum = TmpArr(1) * 10 + TmpArr(2)
End Function

 

[Tetra201]

@Velks:

Further clarification needed.

Per Post #8, WordSum for "AA" is 11.

What are the expected WordSum values for:
- string "99"?
- string "*1" Set 1 and Set 2?
- string "+1" Set 1 and Set 2?
- string "1*" Set 1 and Set 2?
- string "1+" Set 1 and Set 2?
- string "*9" Set 1 and Set 2?
- string "+9" Set 1 and Set 2?
- string "9*" Set 1 and Set 2?
- string "9+" Set 1 and Set 2?

 

[Rick Rothstein]

	A	B	C	D	E
1	Word	Value Set 1	WordSum1	Value Set 2	WordSum2
2	Xyz	21	46	13	68
3	a2c*r	28	25	18	85
4	1243	10	94	10	94
5	ABC	6	35	6	35

<tbody>
</tbody>

Sheet Name is “Num Calculator” and

I am looking for the WordSum1 in Cell C2 using value set 1 and WordSum2 in Cell E2 using value set 2.

Okay, I am confused again. In the table above, you labeled two of the columns WordSum1 and WordSum2, but the value in those columns is just the straight addition of the code for each letter directly. The function that I wrote for you in Message #5 (which would need to be adjusted for the Value Sets you just told us about) is called WordSum but does its summation as you requested in Message #1 (sum by pairs, apply Mod 9 and repeat until two digits remain)... do you still want that function (you don't show a column for it except by its function name)? If so, do you also want the one you show in the above table (simple addition of letter values)? Please clarify what you want.

 

[Velks]

Thanks for your efforts but unfortunately I'm not getting the expected results.

Word	Outcome	Expected Result
XYZ	87	46
ABC	57	35
a123d	31	42
A123D	31	42
A12	45	23

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

I have added few more messages, could you please refer them and suggest. Thanks.

 

[Velks]

Okay, I am confused again. In the table above, you labeled two of the columns WordSum1 and WordSum2, but the value in those columns is just the straight addition of the code for each letter directly. The function that I wrote for you in Message #5 (which would need to be adjusted for the Value Sets you just told us about) is called WordSum but does its summation as you requested in Message #1 (sum by pairs, apply Mod 9 and repeat until two digits remain)... do you still want that function (you don't show a column for it except by its function name)? If so, do you also want the one you show in the above table (simple addition of letter values)? Please clarify what you want.

Actually, I provided that additional info to tell what exactly I’m doing with these formulas, but it created again confusion.

My overall needs are tow formula, out of the two I have solved it for 1 and for 2^nd one I seeking help. I will be happier to accept the expert’s advice on my first formula also if that can be done more easily.

1) Direct sum of assigned values – This I got it by myself using the array formula which I’ve mentioned. It works for all UPPER case, lower cases, numbers, special charterers and mixed as well. (I did not requested for this in #1 as I had a solution, but later I thought it might give the visibility to all how I was applying the values sets to arrive the sum)

2) Summation as requested in Message #1 (sum by pairs, apply Mod 9 and repeat until two digits remain), it has to work in all type of scenarios (like UPPER case, lower cases, numbers, special charterers and mixed)

	A	B	C	D
1	Word	Direct Sum (Value Set 1) Just FYI	WordSum Result (Original Code) # 5	WordSum1 (Expected Result) Summation Reaching till 2 digit
2	Xyz	13	97	46
3	a2c*r	18	#VALUE!	25
4	1243	10	#VALUE!	94
5	ABC	6	35	35

<tbody>
</tbody>

WordSum	The code given by you
WordSum1	IF we use the Value Set1 for WordSum code, then we can name it like this
WordSum2	IF we use the Value Set2 for WordSum code, then we can name it like this

<tbody>
</tbody>

I have added column 'D' with expected result for 2nd question for your ready reference , thanks a lot.

 

[Velks]

This is for Message #11

 

[Tetra201]

Thanks for your efforts but unfortunately I'm not getting the expected results.

Word	Outcome	Expected Result
XYZ	87	46
ABC	57	35
a123d	31	42
A123D	31	42
A12	45	23

<tbody>
</tbody>

I have added few more messages, could you please refer them and suggest. Thanks.

You are not getting the expected results because, most likely, you did not include the "Option Base 1" at the beginning of your module.

Could you please clarify calculations for strings from Post #12?

 

[Velks]

This is for Message #11

You are not getting the expected results because, most likely, you did not include the "Option Base 1" at the beginning of your module.

Could you please clarify calculations for strings from Post #12?

What are the expected WordSum values for:	Set 1	Set 2
- string "99"?	99	99
- string "*1" Set 1 and Set 2?	41	11
- string "+1" Set 1 and Set 2?	51	21
- string "1*" Set 1 and Set 2?	14	11
- string "1+" Set 1 and Set 2?	15	12
- string "*9" Set 1 and Set 2?	49	19
- string "+9" Set 1 and Set 2?	59	29
- string "9*" Set 1 and Set 2?	94	91
- string "9+" Set 1 and Set 2?	95	92

<tbody>
</tbody>

I will check again and update you, thanks.

 

[Velks]

You are not getting the expected results because, most likely, you did not include the "Option Base 1" at the beginning of your module.

Could you please clarify calculations for strings from Post #12?

Yes you are absolutely right, it works perfectly after adding the "Option Base 1", but MOD is not working if the assigned character value is greater than 9, could you please help on that and also advise how to add set 3 in future if required.

Thank you so much for your help.

 

[Tetra201]

Yes you are absolutely right, it works perfectly after adding the "Option Base 1", but MOD is not working if the assigned character value is greater than 9, could you please help on that and also advise how to add set 3 in future if required.

Thank you so much for your help.

Here is an updated code to include clarifications from Post #18.

To include additional sets, just create ValArr3 = Array(......), ValArr4 = Array(......), etc., and list their members similarly to ValArr1.

Then, add the new arrays to ValArr like this: ValArr = Array(ValArr1, ValArr2, ValArr3, ValArr4). That's it.

Option Base 1
Function WordSum(InpStr As String, Optional SetNo As Byte = 1) As Byte
    SymArr = Array(32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126)
    ValArr1 = Array(0, 0, 0, 0, 0, 0, 10, 0, 0, 0, 13, 14, 0, 0, 0, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 0, 0, 0, 0, 0, 3, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 0, 0, 0, 0, 0, 3, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 0, 0, 0, 0)
    ValArr2 = Array(0, 0, 0, 0, 0, 0, 10, 0, 0, 0, 10, 20, 0, 0, 0, 3, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 0, 0, 0, 0, 0, 5, 1, 2, 3, 4, 5, 8, 3, 5, 1, 1, 2, 3, 4, 5, 7, 8, 1, 2, 3, 4, 6, 6, 6, 5, 1, 7, 0, 0, 0, 0, 0, 5, 1, 2, 3, 4, 5, 8, 3, 5, 1, 1, 2, 3, 4, 5, 7, 8, 1, 2, 3, 4, 6, 6, 6, 5, 1, 7, 0, 0, 0, 0)
    ValArr = Array(ValArr1, ValArr2)
    n = Len(InpStr)
    If n = 0 Then Exit Function
    ReDim TmpArr(1 To n)
    For i = 1 To n
        TmpArr(i) = ValArr(SetNo)(Application.Match(Asc(Mid(InpStr, i, 1)), SymArr, 0))
    Next i
    Select Case n
        Case 1: WordSum = TmpArr(1): Exit Function
        Case 2
            For i = 1 To 2
                TmpArr(i) = TmpArr(i) \ 10 + TmpArr(i) Mod 10
            Next i
        Case Else
            Do
            n = n - 1
            For i = 1 To n
                TmpArr(i) = ((TmpArr(i) + TmpArr(i + 1) - 1) Mod 9) + 1
            Next i
            Loop Until n = 2
    End Select
    WordSum = TmpArr(1) * 10 + TmpArr(2)
End Function