How to Calculate Average, Below Average & Above Average (Multiple Criteria)

[Nglaze]

We complete 6 fitness tests on women and men. I have all of the info that tells you whether they are above or below average. However, it includes 5 Tests and where the participant ranks is based off of the Test, Gender, and Age.

Test = Test 1 - 5 (Same for Men & Women)
Age = 5 yr ranges (20-54)
Scores = shows the average range, so for Test 1 (Male 22yo), Below = <14, Avg = 14-19, Above = 19<

I have results for 50 people... what is the easiest way to set this up to automatically calculate if they are BA, Avg, AA for each participant on each test? I would like to pull this into a Pivot Table and Chart afterwards.
*I want something automated b/c this list will be about 500 ppl by the end of the year.

TEST	Gender (Male)	20-24	25-29	30-34	35-39	40-44	45-49	50-54
Test 1	Male	14 - 19	12 - 18	12 - 17	11 - 17	10 - 15	8 - 14	7 - 12
Test 2	Male	16 - 22	15 - 21	14 - 21	13 - 19	13 - 19	11 - 17	10 - 14
Test 3	Male	610 - 735	560 - 700	545 - 680	470 - 640	445 - 605	380 - 570	305 - 500
Test 4	Male	87 - 115	86 - 116	80 - 110	73 - 109	71 - 103	59 - 91	52 - 86
Test 5	Male	-2.5 - +4.0	-3.0 - +3.0	-3.5 - +2.5	-4.0 - +2.0	-5.5 - +1.5	-5.5 - +0.5	-6.5 - -0.5
TEST	Gender (Female)	60-64	65-69	70-74	75-79	80-84	85-89	90-94
Test 1	Female	12 - 17	11 - 16	10 - 15	10 - 15	9 - 14	8 - 13	4 - 11
Test 2	Female	13 - 19	12 - 18	12 - 17	11 - 17	10 - 16	10 - 15	8 - 13
Test 3	Female	545 - 660	500 - 635	480 - 615	430 - 585	385 - 540	340 - 510	275 - 440
Test 4	Female	75 - 107	73 - 107	68 - 101	68 - 100	60 - 91	55 - 85	44 - 72
Test 5	Female	-0.5 - +5.0	-0.5 - +4.5	-1.0 - +4.0	-1.5 - +3.5	-2.0 - +3.0	-2.5 - +2.5	-4.5 - +1.0

<colgroup><col style="mso-width-source:userset;mso-width-alt:7862;width:161pt" width="215"> <col style="mso-width-source:userset;mso-width-alt:4900; width:101pt" width="134" span="5"> <col style="mso-width-source:userset;mso-width-alt:4900; width:101pt" width="134" span="3"> </colgroup><tbody>
</tbody>

 

[Peter_SSs]

I assume that those age ranges for Female in your table are mistakes & they should be the same as the Male age ranges.

See if you can make use of this.
Just put the lower age of each age band in row 1.
Formula in F16:H16 are copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	TEST	Male	20	25	30	35	40	45	50
2	Test 1		14 - 19	12 - 18	12 - 17	11 - 17	10 - 15	8 - 14	7 - 12
3	Test 2		16 - 22	15 - 21	14 - 21	13 - 19	13 - 19	11 - 17	10 - 14
4	Test 3		610 - 735	560 - 700	545 - 680	470 - 640	445 - 605	380 - 570	305 - 500
5	Test 4		87 - 115	86 - 116	80 - 110	73 - 109	71 - 103	59 - 91	52 - 86
6	Test 5		-2.5 - +4.0	-3.0 - +3.0	-3.5 - +2.5	-4.0 - +2.0	-5.5 - +1.5	-5.5 - +0.5	-6.5 - -0.5
7		Female
8	Test 1		12 - 17	11 - 16	10 - 15	10 - 15	9 - 14	8 - 13	4 - 11
9	Test 2		13 - 19	12 - 18	12 - 17	11 - 17	10 - 16	10 - 15	8 - 13
10	Test 3		545 - 660	500 - 635	480 - 615	430 - 585	385 - 540	340 - 510	275 - 440
11	Test 4		75 - 107	73 - 107	68 - 101	68 - 100	60 - 91	55 - 85	44 - 72
12	Test 5		-0.5 - +5.0	-0.5 - +4.5	-1.0 - +4.0	-1.5 - +3.5	-2.0 - +3.0	-2.5 - +2.5	-4.5 - +1.0
13
14
15	Name	Gender	Age	Test	Score	Row Helper	Col Helper	Rating
16	Name 1	Male	27	Test 3	600	3	2	Avg
17	Name 2	Female	40	Test 5	-3	11	5	BA
18	Name 3	Female	24	Test 2	20.4	8	1	AA
19	Name 4	Male	52	Test 5	-1	5	7	Avg
Rating

 

[Nglaze]

Thank You sooooo much. It worked perfectly!! I thought I would have to pay an excel professional/expert (excel rescue). I am trying to figure out Index+Mach, Sumproduct, and a few others now...as well as PowerPivot, Data Models, & PowerBI. Again, THANK YOU SO MUCH FOR YOUR HELP!

And yes, I had the age group incorrect.

 

[Peter_SSs]

You are very welcome. Glad it worked out so well for you. Thanks for the follow-up.