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

N

Nglaze

New Member
Joined
Aug 19, 2016
Messages
3
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>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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
ABCDEFGHI
1TESTMale20253035404550
2Test 114 - 1912 - 1812 - 1711 - 1710 - 158 - 147 - 12
3Test 216 - 2215 - 2114 - 2113 - 1913 - 1911 - 1710 - 14
4Test 3610 - 735560 - 700545 - 680470 - 640445 - 605380 - 570305 - 500
5Test 487 - 11586 - 11680 - 11073 - 10971 - 10359 - 9152 - 86
6Test 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
7Female
8Test 112 - 1711 - 1610 - 1510 - 159 - 148 - 134 - 11
9Test 213 - 1912 - 1812 - 1711 - 1710 - 1610 - 158 - 13
10Test 3545 - 660500 - 635480 - 615430 - 585385 - 540340 - 510275 - 440
11Test 475 - 10773 - 10768 - 10168 - 10060 - 9155 - 8544 - 72
12Test 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
15NameGenderAgeTestScoreRow HelperCol HelperRating
16Name 1Male27Test 360032Avg
17Name 2Female40Test 5-3115BA
18Name 3Female24Test 220.481AA
19Name 4Male52Test 5-157Avg
Rating
 
Last edited:
Upvote 0
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.
 
Upvote 0
You are very welcome. Glad it worked out so well for you. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Copy range based on criteria in column - macro
2 3
Replies
24
Views
408
doriannjeshi
D
S
  • Question Question
Pull data from sheet to new sheet based on multiple criteria from another sheet in excel
Replies
2
Views
361
sallam Qasem
S
S
  • Solved
Average in excel
Replies
3
Views
252
etaf
etaf
O
  • Solved
How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.
Replies
3
Views
111
kvsrinivasamurthy
K
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top