# Numbers&letters calculation



## doriannjeshi (Wednesday at 11:04 AM)

Hello,
I want to find the current letter if the Value is specified . All the letter values (according to the table)  start being added to each other until we get the Value being asked to show the letter.





wordValuecurrent letter formula1Aangel30G2Bdark5A3Clanguages20A4D5E6F7G8H9I1J2K3L4M5N6O7P8Q9R1S2T3U4V5W6X7Y8Z


----------



## anand3dinesh (Wednesday at 11:14 AM)

sorry I did not understand your question if value 5 is added then you want letter E shown in your last column?


----------



## doriannjeshi (Wednesday at 3:43 PM)

anand3dinesh said:


> sorry I did not understand your question if value 5 is added then you want letter E shown in your last column?


Hi Anand,
Sorry, I need to give the steps for calculations
basically I need the last letter to be added to reach the asked value, the addin restarts from the first letter if value not reached

wordValuecurrent letter formulacalculations1Aangel30Ga1+n5+g7+e5+l3+a1+n5=27+G7;=G2Bdark5Ad4+1a=5;=A3Clanguages20Al3+a1+n5+g7+u3+a1=20;=A4D5E6F7G8H9I1J2K3L4M5N6O7P8Q9R1S2T3U4V5W6X7Y8Z


----------



## Dave Patton (Wednesday at 5:09 PM)

2 ideas to get you started.
Strike the second idea; the numbers do not continue to ascend in column A.
You might get better results if you post the information with the forum's tool named XL2BB.

T202301a.xlsmABCDE1ABEHH21A32B43C54D65E76F87G98H1fCell FormulasRangeFormulaD1D1=XLOOKUP(XLOOKUP(LEFT(C1,1),B2:B6,A2:A6)+XLOOKUP(MID(C1,2,1),B2:B6,A2:A6)+XLOOKUP(MID(C1,3,1),B2:B6,A2:A6),A2:A9,B2:B9)E1E1=CHAR(CODE(LEFT(C1))-64+CODE(MID(C1,2,1))-64+CODE(MID(C1,3,1))-64+64)


----------



## Dave Patton (Wednesday at 5:22 PM)

Maybe cancel all of the above

T202301a.xlsmABCDEF18ABEE21A32B43C54D65E76F87G98H1fCell FormulasRangeFormulaF1F1=XLOOKUP(B1-(XLOOKUP(LEFT(C1,1),B2:B6,A2:A6)+XLOOKUP(MID(C1,2,1),B2:B6,A2:A6)),A2:A9,B2:B9)


----------



## doriannjeshi (Wednesday at 5:51 PM)

Dave Patton said:


> Maybe cancel all of the above
> 
> T202301a.xlsmABCDEF18ABEE21A32B43C54D65E76F87G98H1fCell FormulasRangeFormulaF1F1=XLOOKUP(B1-(XLOOKUP(LEFT(C1,1),B2:B6,A2:A6)+XLOOKUP(MID(C1,2,1),B2:B6,A2:A6)),A2:A9,B2:B9)


Thank you for the solution Dave!
does this solution require office 365? 
also does it work if last letter is a G ?


----------



## Dave Patton (Wednesday at 6:11 PM)

Thank you for the solution Dave!               This is not a solution but  ideas that may help you.
does this solution require office 365?         The solution does not require 365 but it does use XLookup. Is Xlookup in 2019.
                                                                     You can use other lookups but some alternatives may require different lookup tables.
also does it work if last letter is a G ?          I do not know. You will have to expand the ranges and check the ideas.


----------



## Peter_SSs (Wednesday at 6:29 PM)

Dave Patton said:


> You might get better results if you post the information with the forum's tool named XL2BB.


OP did that in post #1.


----------



## Peter_SSs (Wednesday at 8:56 PM)

Peter_SSs said:


> OP did that in post #1.


.. but probably better if you use Mini Sheet rather than Table Only 

Can we

Use helper columns for your version if you want to use worksheet formulas?
or
Use vba? Post back if you want this.
For a worksheet formula solution, see if this would do what you want.
Copy the column H formulas across as far as you think you might need. If you copy them further or less than column Z as I have done then make sure you adjust the "Z"s in the column F formula to match

23 01 12.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAA1wordValueLetter21Aangel30G157531575315753157532Bdark5A419241924192419241943Clanguages20A315731751315731751354D65E76F87G98H109I111J122K133L144M155N166O177P188Q199R201S212T223U234V245W256X267Y278ZLetterCell FormulasRangeFormulaH2:Z4H2=INDEX($A$2:$A$27,MATCH(MID($D2,MOD(COLUMNS($I:I)-1,LEN($D2))+1,1),$B$2:$B$27,0))F2:F4F2=UPPER(MID(D2,MOD(MATCH(E2,SUBTOTAL(9,OFFSET(H2,,,,COLUMN(H2:Z2)-COLUMN(H2)+1)))+ISNA(MATCH(E2,SUBTOTAL(9,OFFSET(H2,,,,COLUMN(H2:Z2)-COLUMN(H2)+1)),0))-1,LEN(D2))+1,1))


----------



## doriannjeshi (Yesterday at 12:34 PM)

Peter_SSs said:


> .. but probably better if you use Mini Sheet rather than Table Only
> 
> Can we
> 
> ...


I thought it couldn't be done!!
Thank you very much for the colossal solution!


----------



## doriannjeshi (Wednesday at 11:04 AM)

Hello,
I want to find the current letter if the Value is specified . All the letter values (according to the table)  start being added to each other until we get the Value being asked to show the letter.





wordValuecurrent letter formula1Aangel30G2Bdark5A3Clanguages20A4D5E6F7G8H9I1J2K3L4M5N6O7P8Q9R1S2T3U4V5W6X7Y8Z


----------



## Peter_SSs (Yesterday at 8:46 PM)

You're welcome. Thanks for the follow-up.

Here is another option that you could consider. Helper column formula is slightly longer but the col F formula is much simpler. 

23 01 12.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZ1wordValueLetter21Aangel30G16131821222841598010213017123031041254271394332Bdark5A45141620253955751001391942693695087029711340184843Clanguages20A349161920273233364049658410413116319623254Dabc6C136710162333497210515422633148571110421527223865E76F87G98H109I111J122K133L144M155N166O177P188Q199R201S212T223U234V245W256X267Y278ZLetter (2)Cell FormulasRangeFormulaH2:Z5H2=G2+IFNA(INDEX($A$2:$A$27,MATCH(MID($D2,COLUMNS($H:H),1),$B$2:$B$27,0)),OFFSET(H2,,-LEN($D2)))F2:F5F2=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))


----------



## doriannjeshi (Today at 6:28 AM)

Peter_SSs said:


> You're welcome. Thanks for the follow-up.
> 
> Here is another option that you could consider. Helper column formula is slightly longer but the col F formula is much simpler.
> 
> 23 01 12.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZ1wordValueLetter21Aangel30G16131821222841598010213017123031041254271394332Bdark5A45141620253955751001391942693695087029711340184843Clanguages20A349161920273233364049658410413116319623254Dabc6C136710162333497210515422633148571110421527223865E76F87G98H109I111J122K133L144M155N166O177P188Q199R201S212T223U234V245W256X267Y278ZLetter (2)Cell FormulasRangeFormulaH2:Z5H2=G2+IFNA(INDEX($A$2:$A$27,MATCH(MID($D2,COLUMNS($H:H),1),$B$2:$B$27,0)),OFFSET(H2,,-LEN($D2)))F2:F5F2=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))


I am using this then!


----------



## Peter_SSs (Today at 6:34 AM)

doriannjeshi said:


> I am using this then!


----------



## doriannjeshi (Today at 7:00 AM)

can you please check the word: Mani, it gives me a diferent result
The first solution reports a correct M letter

Book6ABCDEFGHIJKLMNOPQRSTUVWXYZ1wordValueLetter21Astrong40R1312182330313446648711714818222829237949664432Bmani40I451019232838578010814620328339153774010231414195143Clanguages20A349161920273233364049658410413116319623254Dabc3B136710162333497210515422633148571110421527223865E76F87G98H109I111J122K133L144M155N166O177P188Q199R201S212T223U234V245W256X267Y278ZSheet3Cell FormulasRangeFormulaH2:Z5H2=G2+IFNA(INDEX($A$2:$A$27,MATCH(MID($D2,COLUMNS($H:H),1),$B$2:$B$27,0)),OFFSET(H2,,-LEN($D2)))F2:F5F2=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))


----------



## Peter_SSs (Today at 7:28 AM)

doriannjeshi said:


> Mani, it gives me a diferent result
> The first solution reports a correct M letter


Good catch - You are correct. 
 I should have noticed that the numbers were getting too big too quickly!

See if this is better. Note that column G must be blank.

23 01 12.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZ1wordValueLetter21Astrong40R13121823303133424853606163727883909132Bmani40M45101923242938424348576162677680818643Clanguages20A3491619202732333637424952536065666954Dabc3B13679121315181921242527303133363765E76F87G98H109I111J122K133L144M155N166O177P188Q199R201S212T223U234V245W256X267Y278ZLetter (3)Cell FormulasRangeFormulaH2:Z5H2=G2+IFNA(INDEX($A$2:$A$27,MATCH(MID($D2,COLUMNS($H:H),1),$B$2:$B$27,0)),OFFSET(H2,,-LEN($D2))-OFFSET(G2,,-LEN($D2)))F2:F5F2=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))


----------



## doriannjeshi (Today at 8:48 AM)

Peter_SSs said:


> Good catch - You are correct.
> I should have noticed that the numbers were getting too big too quickly!
> 
> See if this is better. Note that column G must be blank.
> ...


Yes it is great, thank you for the improvement!


----------

