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