Formula for chart

[yitzymerm]

Please see snapshot of ht & wt chart below, Column C is showing the height, Row 7 are the ages & Row 6 is the category result, and the weight amounts in the chart are the maximum allowed (i.e. Age 25, Height 4.8 can weigh up to 130 to fall into excellent category, at 131 lbs they are already in the 'good' category until 152 etc.)

I would like to be able to enter the Ht, Wt, & age in column B & the result should automatically come up in B4 (excellent, good or poor), please advise simplest & best formula that should be used.

Thanks in advance.

View attachment 80818

 

[yitzymerm]

Try.

The formulas are too complex to debug.
So I am not sure the formula are all right.
And I make some conditional formats to check values easily.

Book.xlsx
	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
1	Enter M/F	M		Ages	20-24	25-29	30-39	40-49	50-59	60+	20-24	25-29	30-39	40-49	50-59	60+	All Ages
2	Enter Height	5.1		Ht/Rating	Male - Elite						Female - Elite						Preferred	Standard	Class 2	Class 3	Class 4	Class 6	Class 8	Class 10	Class 12
3	Enter Weight	223		4.8	128	131	132	134	136	138	124	126	130	133	134	135	151	191	202	213	222	231	240	N/A	N/A
4	Enter Age	60		4.9	131	133	135	137	139	141	126	130	133	136	138	138	154	195	206	217	226	235	244	N/A	N/A
5	Rating Result	Class 2		4.10	135	137	139	141	143	145	131	134	137	139	141	142	158	199	210	221	230	239	249	N/A	N/A
6	Better Rate	11		4.11	138	140	142	144	146	148	134	137	139	142	144	145	161	203	214	224	233	243	253	N/A	N/A
7				5	141	143	145	147	149	151	136	139	142	145	147	148	165	207	218	229	239	248	257	266	275
8				5.1	145	147	149	151	153	155	140	143	146	149	151	152	169	212	223	233	243	252	261	270	279
9				5.2	147	149	152	154	156	158	143	146	149	152	154	155	172	216	227	237	247	256	265	274	283
10				5.3	151	153	155	158	160	162	146	149	153	156	158	159	177	221	232	243	253	263	273	282	291
11				5.4	156	158	160	162	165	167	151	154	157	161	162	164	183	227	238	249	260	270	280	290	299
12				5.5	159	162	164	166	169	171	154	158	161	164	167	168	188	233	244	255	266	277	287	297	306
13				5.6	164	166	169	171	174	176	159	162	166	169	171	173	192	239	250	261	272	283	294	304	313
14				5.7	168	170	173	175	178	181	163	166	170	173	175	177	197	245	256	268	279	290	302	312	321
15				5.8	171	174	176	179	182	185	166	170	173	177	179	181	202	252	264	276	287	298	310	320	329
16				5.9	176	179	182	185	187	190	171	174	178	183	185	186	207	259	272	284	296	307	319	329	338
17				5.10	182	184	187	190	192	195	175	179	184	187	190	191	212	266	279	291	303	315	326	337	347
18				5.11	185	188	191	194	196	199	179	184	187	191	194	195	217	273	286	299	311	323	334	345	355
19				6	191	194	197	199	202	205	185	189	193	197	199	201	223	281	294	307	319	331	342	353	363
20				6.1	196	199	201	204	207	210	190	194	198	202	204	206	228	289	302	315	327	339	350	362	372
21				6.2	199	202	205	208	211	214	193	197	202	206	208	210	235	297	310	323	335	347	358	371	381
22				6.3	206	209	212	215	218	221	200	204	208	212	215	217	241	305	318	331	343	355	367	380	390
23				6.4	212	215	218	221	224	227	205	209	214	218	221	223	248	313	326	339	351	363	376	389	399
24				6.5	217	220	223	227	230	234	210	215	219	224	227	228	255	321	334	347	359	372	385	398	408
25				6.6	224	227	230	234	238	241	217	221	226	232	235	236	263	328	342	355	367	381	394	407	417
26				6.7	230	235	238	241	245	248	223	228	234	238	241	243	270	336	350	363	375	390	403	416	426
27				6.8	238	241	245	248	252	255	229	235	240	245	248	250
28				6.9	243	247	250	254	257	261	236	241	246	251	254	256
29				6.10	249	252	256	260	263	267	241	246	251	257	260	262
Sheet(2)

Cell Formulas
Range	Formula
B5	B5	=IF(B3<=INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),"Elite",INDEX($Q$2:$Y$2,1,IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1)+1,1)))
B6	B6	=IF($B$3<=INDEX($Q$3:$Q$29,MATCH($B$2,$D$3:$D$29,0)),B3-INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),IF(INDEX($Q$3:$Y$29,MATCH($B$2,$D$3:$D$29,0),IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1),1))="",$B$3-INDEX(IF($B$1="M",$E$3:$J$29,$K$3:$P$29),MATCH($B$2,$D$3:$D$29,0),MATCH($B$4,VALUE(LEFT($E$1:$J$1,2)),1)),$B$3-INDEX($Q$3:$Y$29,MATCH($B$2,$D$3:$D$29,0),IFERROR(MATCH($B$3-1,OFFSET($Q2:$Y2,MATCH($B$2,$D$3:$D$29,0),0),1),1))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q2:Y2	Expression	=$B$5=Q$2	text	NO
Q2:Y2	Expression	=$D2=$B$2	text	NO
Q3:Y29	Expression	=$B$5=Q$2	text	NO
P1,J1,J3:J29,P3:P29	Expression	=$B$4>=VALUE(LEFT(J$1,2))	text	NO
E1:P1,E3:P29,E2,K2	Expression	=IFERROR(AND($B$4>=VALUE(LEFT(E$1,2)),$B$4<=VALUE(RIGHT(E$1,2))),FALSE)	text	NO
D3:Y29	Expression	=$D3=$B$2	text	NO

Hi HongRu,

Thank you very much for helping me out with this.

I was able to implement it on this chart and on some other similar charts that I had, will mark this done, was truly a great help!!!!!