Numbers&letters calculation

[doriannjeshi]

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.

			word	Value	current letter formula
1	A		angel	30	G
2	B		dark	5	A
3	C		languages	20	A
4	D
5	E
6	F
7	G
8	H
9	I
1	J
2	K
3	L
4	M
5	N
6	O
7	P
8	Q
9	R
1	S
2	T
3	U
4	V
5	W
6	X
7	Y
8	Z

 

[anand3dinesh]

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

 

[doriannjeshi]

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

			word	Value	current letter formula	calculations
1	A		angel	30	G	a1+n5+g7+e5+l3+a1+n5=27+G7;=G
2	B		dark	5	A	d4+1a=5;=A
3	C		languages	20	A	l3+a1+n5+g7+u3+a1=20;=A
4	D
5	E
6	F
7	G
8	H
9	I
1	J
2	K
3	L
4	M
5	N
6	O
7	P
8	Q
9	R
1	S
2	T
3	U
4	V
5	W
6	X
7	Y
8	Z

 

[Dave Patton]

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.xlsm
	A	B	C	D	E
1			ABE	H	H
2	1	A
3	2	B
4	3	C
5	4	D
6	5	E
7	6	F
8	7	G
9	8	H
1f

Cell Formulas
Range	Formula
D1	D1	=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)
E1	E1	=CHAR(CODE(LEFT(C1))-64+CODE(MID(C1,2,1))-64+CODE(MID(C1,3,1))-64+64)

 

[Dave Patton]

Maybe cancel all of the above

T202301a.xlsm
	A	B	C	D	E	F
1		8	ABE			E
2	1	A
3	2	B
4	3	C
5	4	D
6	5	E
7	6	F
8	7	G
9	8	H
1f

Cell Formulas
Range	Formula
F1	F1	=XLOOKUP(B1-(XLOOKUP(LEFT(C1,1),B2:B6,A2:A6)+XLOOKUP(MID(C1,2,1),B2:B6,A2:A6)),A2:A9,B2:B9)

 

[doriannjeshi]

Maybe cancel all of the above

T202301a.xlsm
	A	B	C	D	E	F
1		8	ABE			E
2	1	A
3	2	B
4	3	C
5	4	D
6	5	E
7	6	F
8	7	G
9	8	H
1f

Cell Formulas
Range	Formula
F1	F1	=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]

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]

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]

OP did that in post #1.

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

Can we

1. Use helper columns for your version if you want to use worksheet formulas?
   or
2. 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.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1				word	Value	Letter
2	1	A		angel	30	G		1	5	7	5	3	1	5	7	5	3	1	5	7	5	3	1	5	7	5
3	2	B		dark	5	A		4	1	9	2	4	1	9	2	4	1	9	2	4	1	9	2	4	1	9
4	3	C		languages	20	A		3	1	5	7	3	1	7	5	1	3	1	5	7	3	1	7	5	1	3
5	4	D
6	5	E
7	6	F
8	7	G
9	8	H
10	9	I
11	1	J
12	2	K
13	3	L
14	4	M
15	5	N
16	6	O
17	7	P
18	8	Q
19	9	R
20	1	S
21	2	T
22	3	U
23	4	V
24	5	W
25	6	X
26	7	Y
27	8	Z
Letter

Cell Formulas
Range	Formula
H2:Z4	H2	=INDEX($A$2:$A$27,MATCH(MID($D2,MOD(COLUMNS($I:I)-1,LEN($D2))+1,1),$B$2:$B$27,0))
F2:F4	F2	=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]

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

Can we

1. Use helper columns for your version if you want to use worksheet formulas?
   or
2. 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.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1				word	Value	Letter
2	1	A		angel	30	G		1	5	7	5	3	1	5	7	5	3	1	5	7	5	3	1	5	7	5
3	2	B		dark	5	A		4	1	9	2	4	1	9	2	4	1	9	2	4	1	9	2	4	1	9
4	3	C		languages	20	A		3	1	5	7	3	1	7	5	1	3	1	5	7	3	1	7	5	1	3
5	4	D
6	5	E
7	6	F
8	7	G
9	8	H
10	9	I
11	1	J
12	2	K
13	3	L
14	4	M
15	5	N
16	6	O
17	7	P
18	8	Q
19	9	R
20	1	S
21	2	T
22	3	U
23	4	V
24	5	W
25	6	X
26	7	Y
27	8	Z
Letter

Cell Formulas
Range	Formula
H2:Z4	H2	=INDEX($A$2:$A$27,MATCH(MID($D2,MOD(COLUMNS($I:I)-1,LEN($D2))+1,1),$B$2:$B$27,0))
F2:F4	F2	=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))

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