excel formula calculating rang

[Tripleseas]

hello community,
I want to write a formula in column D that will note the rang name based on the percentage interval.
I want this to be dynamic so i can change the interval percentage if i want.
thank you to all.

Classeur1
	B	C	D	E	F	G	H
3	Code	Tx équip	RANG
4	1221	30%
5	1205	30%
6	1170	31%			Start	End	RANG
7	1131	34%			0%	30%	between 0 and 30
8	1172	34%			31%	60%	Between 31 and 60
9	1045	34%				>60%	Greater than 60
10	1003	34%
11	1211	35%
12	1217	35%
13	1130	36%
14	1079	36%
15	1038	36%
16	1099	37%
17	1104	37%
18	1057	37%
19	1112	38%
20	1295	39%
21	1108	40%
22	1033	40%
23	1181	41%
24	1084	41%
25	1252	41%
26	1007	42%
27	1183	42%
28	1067	43%
29	1063	43%
30	1283	43%
31	1014	43%
32	1144	44%
33	1284	44%
34	1296	44%
35	1002	44%
36	1258	44%
37	1022	45%
38	1242	45%
39	1145	45%
40	1094	46%
41	1207	46%
42	1300	46%
43	1008	46%
44	1173	46%
45	1287	47%
46	1293	47%
47	1206	47%
48	1197	47%
49	1276	79%
50	1097	79%
51	1078	79%
52	1194	80%
53	1239	80%
54	1044	80%
55	1018	80%
56	1229	81%
57	1114	82%
58	1311	83%
59	1185	85%
60	1080	86%
61	1016	87%
62	1065	89%
63	1166	91%
64	1025	91%
65	1160	92%
Feuil1

 

[offthelip]

try this

=CHOOSE(MIN(3,INT(C4*100/30)+1),"Between 0 and 30","Between 31 and 60","Greater than 60")

Note your first value in C4 is just ABOVE 30%

 

[Sufiyan97]

just add 0.6 in E7
and Try

Book1
	A	B	C	D	E	F	G	H
1	Code	Tx équip	RANG
2	1221	30%	between 0 and 30
3	1205	30%	between 0 and 30
4	1170	31%	Between 31 and 60		Start	End	RANG
5	1131	34%	Between 31 and 60		0	0.3	between 0 and 30
6	1172	34%	Between 31 and 60		0.31	0.6	Between 31 and 60
7	1045	34%	Between 31 and 60		0.6	>60%	Greater than 60
8	1003	34%	Between 31 and 60
9	1211	35%	Between 31 and 60
10	1217	35%	Between 31 and 60
11	1130	36%	Between 31 and 60
12	1079	36%	Between 31 and 60
13	1038	36%	Between 31 and 60
14	1099	37%	Between 31 and 60
15	1104	37%	Between 31 and 60
16	1057	37%	Between 31 and 60
17	1112	38%	Between 31 and 60
18	1295	39%	Between 31 and 60
19	1108	40%	Between 31 and 60
20	1033	40%	Between 31 and 60
21	1181	41%	Between 31 and 60
22	1084	41%	Between 31 and 60
23	1252	41%	Between 31 and 60
24	1007	42%	Between 31 and 60
25	1183	42%	Between 31 and 60
26	1067	43%	Between 31 and 60
27	1063	43%	Between 31 and 60
28	1283	43%	Between 31 and 60
29	1014	43%	Between 31 and 60
30	1144	44%	Between 31 and 60
31	1284	44%	Between 31 and 60
32	1296	44%	Between 31 and 60
33	1002	44%	Between 31 and 60
34	1258	44%	Between 31 and 60
35	1022	45%	Between 31 and 60
36	1242	45%	Between 31 and 60
37	1145	45%	Between 31 and 60
38	1094	46%	Between 31 and 60
39	1207	46%	Between 31 and 60
40	1300	46%	Between 31 and 60
41	1008	46%	Between 31 and 60
42	1173	46%	Between 31 and 60
43	1287	47%	Between 31 and 60
44	1293	47%	Between 31 and 60
45	1206	47%	Between 31 and 60
46	1197	47%	Between 31 and 60
47	1276	79%	Greater than 60
48	1097	79%	Greater than 60
49	1078	79%	Greater than 60
50	1194	80%	Greater than 60
51	1239	80%	Greater than 60
52	1044	80%	Greater than 60
53	1018	80%	Greater than 60
54	1229	81%	Greater than 60
55	1114	82%	Greater than 60
56	1311	83%	Greater than 60
57	1185	85%	Greater than 60
58	1080	86%	Greater than 60
59	1016	87%	Greater than 60
60	1065	89%	Greater than 60
61	1166	91%	Greater than 60
62	1025	91%	Greater than 60
63	1160	92%	Greater than 60
64
Sheet1

Cell Formulas
Range	Formula
C2:C63	C2	=VLOOKUP(B2,$E$5:$G$7,3)

 

[James006]

Hi,

If you add 0.6 in cell E7, you can test following formula

=INDEX($G$5:$G$7,MATCH(B2,$E$5:$E$7,1))

 

[Tripleseas]

just add 0.6 in E7
and Try

Book1
	A	B	C	D	E	F	G	H
1	Code	Tx équip	RANG
2	1221	30%	between 0 and 30
3	1205	30%	between 0 and 30
4	1170	31%	Between 31 and 60		Start	End	RANG
5	1131	34%	Between 31 and 60		0	0.3	between 0 and 30
6	1172	34%	Between 31 and 60		0.31	0.6	Between 31 and 60
7	1045	34%	Between 31 and 60		0.6	>60%	Greater than 60
8	1003	34%	Between 31 and 60
9	1211	35%	Between 31 and 60
10	1217	35%	Between 31 and 60
11	1130	36%	Between 31 and 60
12	1079	36%	Between 31 and 60
13	1038	36%	Between 31 and 60
14	1099	37%	Between 31 and 60
15	1104	37%	Between 31 and 60
16	1057	37%	Between 31 and 60
17	1112	38%	Between 31 and 60
18	1295	39%	Between 31 and 60
19	1108	40%	Between 31 and 60
20	1033	40%	Between 31 and 60
21	1181	41%	Between 31 and 60
22	1084	41%	Between 31 and 60
23	1252	41%	Between 31 and 60
24	1007	42%	Between 31 and 60
25	1183	42%	Between 31 and 60
26	1067	43%	Between 31 and 60
27	1063	43%	Between 31 and 60
28	1283	43%	Between 31 and 60
29	1014	43%	Between 31 and 60
30	1144	44%	Between 31 and 60
31	1284	44%	Between 31 and 60
32	1296	44%	Between 31 and 60
33	1002	44%	Between 31 and 60
34	1258	44%	Between 31 and 60
35	1022	45%	Between 31 and 60
36	1242	45%	Between 31 and 60
37	1145	45%	Between 31 and 60
38	1094	46%	Between 31 and 60
39	1207	46%	Between 31 and 60
40	1300	46%	Between 31 and 60
41	1008	46%	Between 31 and 60
42	1173	46%	Between 31 and 60
43	1287	47%	Between 31 and 60
44	1293	47%	Between 31 and 60
45	1206	47%	Between 31 and 60
46	1197	47%	Between 31 and 60
47	1276	79%	Greater than 60
48	1097	79%	Greater than 60
49	1078	79%	Greater than 60
50	1194	80%	Greater than 60
51	1239	80%	Greater than 60
52	1044	80%	Greater than 60
53	1018	80%	Greater than 60
54	1229	81%	Greater than 60
55	1114	82%	Greater than 60
56	1311	83%	Greater than 60
57	1185	85%	Greater than 60
58	1080	86%	Greater than 60
59	1016	87%	Greater than 60
60	1065	89%	Greater than 60
61	1166	91%	Greater than 60
62	1025	91%	Greater than 60
63	1160	92%	Greater than 60
64
Sheet1

Cell Formulas
Range	Formula
C2:C63	C2	=VLOOKUP(B2,$E$5:$G$7,3)

thank you worked great

 

[Tripleseas]

Hi,

If you add 0.6 in cell E7, you can test following formula

=INDEX($G$5:$G$7,MATCH(B2,$E$5:$E$7,1))

thank you worked good as well

 

[Sufiyan97]

You're welcome
Glad we could help.