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

 

[Peter_SSs]

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.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
1				word	Value	Letter
2	1	A		angel	30	G		1	6	13	18	21	22	28	41	59	80	102	130	171	230	310	412	542	713	943
3	2	B		dark	5	A		4	5	14	16	20	25	39	55	75	100	139	194	269	369	508	702	971	1340	1848
4	3	C		languages	20	A		3	4	9	16	19	20	27	32	33	36	40	49	65	84	104	131	163	196	232
5	4	D		abc	6	C		1	3	6	7	10	16	23	33	49	72	105	154	226	331	485	711	1042	1527	2238
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 (2)

Cell Formulas
Range	Formula
H2:Z5	H2	=G2+IFNA(INDEX($A$2:$A$27,MATCH(MID($D2,COLUMNS($H:H),1),$B$2:$B$27,0)),OFFSET(H2,,-LEN($D2)))
F2:F5	F2	=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))

 

[doriannjeshi]

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.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
1				word	Value	Letter
2	1	A		angel	30	G		1	6	13	18	21	22	28	41	59	80	102	130	171	230	310	412	542	713	943
3	2	B		dark	5	A		4	5	14	16	20	25	39	55	75	100	139	194	269	369	508	702	971	1340	1848
4	3	C		languages	20	A		3	4	9	16	19	20	27	32	33	36	40	49	65	84	104	131	163	196	232
5	4	D		abc	6	C		1	3	6	7	10	16	23	33	49	72	105	154	226	331	485	711	1042	1527	2238
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 (2)

Cell Formulas
Range	Formula
H2:Z5	H2	=G2+IFNA(INDEX($A$2:$A$27,MATCH(MID($D2,COLUMNS($H:H),1),$B$2:$B$27,0)),OFFSET(H2,,-LEN($D2)))
F2:F5	F2	=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))

I am using this then!

 

[Peter_SSs]

I am using this then!

 

[doriannjeshi]

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

Book6
	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
1				word	Value	Letter
2	1	A		strong	40	R		1	3	12	18	23	30	31	34	46	64	87	117	148	182	228	292	379	496	644
3	2	B		mani	40	I		4	5	10	19	23	28	38	57	80	108	146	203	283	391	537	740	1023	1414	1951
4	3	C		languages	20	A		3	4	9	16	19	20	27	32	33	36	40	49	65	84	104	131	163	196	232
5	4	D		abc	3	B		1	3	6	7	10	16	23	33	49	72	105	154	226	331	485	711	1042	1527	2238
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
Sheet3

Cell Formulas
Range	Formula
H2:Z5	H2	=G2+IFNA(INDEX($A$2:$A$27,MATCH(MID($D2,COLUMNS($H:H),1),$B$2:$B$27,0)),OFFSET(H2,,-LEN($D2)))
F2:F5	F2	=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))

 

[Peter_SSs]

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.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
1				word	Value	Letter
2	1	A		strong	40	R		1	3	12	18	23	30	31	33	42	48	53	60	61	63	72	78	83	90	91
3	2	B		mani	40	M		4	5	10	19	23	24	29	38	42	43	48	57	61	62	67	76	80	81	86
4	3	C		languages	20	A		3	4	9	16	19	20	27	32	33	36	37	42	49	52	53	60	65	66	69
5	4	D		abc	3	B		1	3	6	7	9	12	13	15	18	19	21	24	25	27	30	31	33	36	37
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 (3)

Cell Formulas
Range	Formula
H2:Z5	H2	=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:F5	F2	=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))

 

[doriannjeshi]

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.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
1				word	Value	Letter
2	1	A		strong	40	R		1	3	12	18	23	30	31	33	42	48	53	60	61	63	72	78	83	90	91
3	2	B		mani	40	M		4	5	10	19	23	24	29	38	42	43	48	57	61	62	67	76	80	81	86
4	3	C		languages	20	A		3	4	9	16	19	20	27	32	33	36	37	42	49	52	53	60	65	66	69
5	4	D		abc	3	B		1	3	6	7	9	12	13	15	18	19	21	24	25	27	30	31	33	36	37
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 (3)

Cell Formulas
Range	Formula
H2:Z5	H2	=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:F5	F2	=UPPER(MID(REPT(D2,99),MATCH(E2,H2:Z2)+ISNA(MATCH(E2,H2:Z2,0)),1))

Yes it is great, thank you for the improvement!

 

[Peter_SSs]

Cheers. Glad you picked up my error.