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

V

Velks

New Member
Joined
Apr 22, 2017
Messages
27
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.
 
Sort by date Sort by votes
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.
Code: 
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
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
@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?
 
Upvote 0
Velks said:
[TABLE="width: 439"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Word
[/TD]
[TD]Value Set 1
[/TD]
[TD]WordSum1
[/TD]
[TD]Value Set 2
[/TD]
[TD]WordSum2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Xyz
[/TD]
[TD]21
[/TD]
[TD]46
[/TD]
[TD]13
[/TD]
[TD]68
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]a2c*r
[/TD]
[TD]28
[/TD]
[TD]25
[/TD]
[TD]18
[/TD]
[TD]85
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1243
[/TD]
[TD]10
[/TD]
[TD]94
[/TD]
[TD]10
[/TD]
[TD]94
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ABC
[/TD]
[TD]6
[/TD]
[TD]35
[/TD]
[TD]6
[/TD]
[TD]35
[/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...
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.
 
Upvote 0
Thanks for your efforts but unfortunately I'm not getting the expected results.



[TABLE="width: 235"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Word[/TD]
[TD]Outcome[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]a123d[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]A123D[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]A12[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]

I have added few more messages, could you please refer them and suggest. Thanks.
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...


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 2nd 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)

[TABLE="width: 543"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Word
[/TD]
[TD]Direct Sum
(Value Set 1)
Just FYI
[/TD]
[TD]WordSum Result
(Original Code) # 5
[/TD]
[TD]WordSum1 (Expected Result)
Summation Reaching till 2 digit
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Xyz
[/TD]
[TD]13
[/TD]
[TD]97
[/TD]
[TD]46
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]a2c*r
[/TD]
[TD]18
[/TD]
[TD]#VALUE!
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1243
[/TD]
[TD]10
[/TD]
[TD]#VALUE!
[/TD]
[TD]94
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ABC
[/TD]
[TD]6
[/TD]
[TD]35
[/TD]
[TD]35
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
Velks said:
Thanks for your efforts but unfortunately I'm not getting the expected results.

[TABLE="width: 235"]
<tbody>[TR]
[TD]Word[/TD]
[TD]Outcome[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]a123d[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]A123D[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]A12[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]

I have added few more messages, could you please refer them and suggest. Thanks.
Click to expand...
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?
 
Upvote 0
Velks said:
This is for Message #11
Click to expand...

Tetra201 said:
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?
Click to expand...


[TABLE="width: 456"]
<tbody>[TR]
[TD]What are the expected WordSum values for:[/TD]
[TD]Set 1[/TD]
[TD]Set 2[/TD]
[/TR]
[TR]
[TD]- string "99"?[/TD]
[TD]99[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]- string "*1" Set 1 and Set 2?[/TD]
[TD]41[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]- string "+1" Set 1 and Set 2?[/TD]
[TD]51[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]- string "1*" Set 1 and Set 2?[/TD]
[TD]14[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]- string "1+" Set 1 and Set 2?[/TD]
[TD]15[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]- string "*9" Set 1 and Set 2?[/TD]
[TD]49[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]- string "+9" Set 1 and Set 2?[/TD]
[TD]59[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]- string "9*" Set 1 and Set 2?[/TD]
[TD]94[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]- string "9+" Set 1 and Set 2?[/TD]
[TD]95[/TD]
[TD]92[/TD]
[/TR]
</tbody>[/TABLE]


I will check again and update you, thanks.
 
Last edited:
Upvote 0
Tetra201 said:
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?
Click to expand...

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.
 
Upvote 0
Velks said:
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.
Click to expand...
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.
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
115
Rex987456
R
S
  • Question Question
A tricky array sum question into a single cell...
Replies
4
Views
312
etaf
etaf
G
  • Question Question
Easy Formula - Pros Please help :)
Replies
7
Views
449
GLOJACK
G
togenmoser
  • Solved
Finding all possible combinations of 2 sums
2
Replies
10
Views
718
togenmoser
togenmoser
F
  • Question Question
Formula Help: Summing numbers from text cells, that occur before/after specific characters
Replies
3
Views
774
ExceLoki
ExceLoki

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top