Average age between age ranges

[Gordonovitch]

I have a data set which includes columns containing "Date of Birth", "Age", "Sex" (represented by M or F). I have named the Date of Birth column range "DOB", the Age column range "AGE" and the Sex column "SEX". I have divided the data into age "groups" 0 - 19, 20 - 39 etc. What I need to do is find the average age in each age group. To create a count for each group, I have used the following formula:

=COUNTIFS(AGE,">="&B6,AGE,"<="&C6,SEX,B7) where B6 is "20", C6 is "30" & B7 is "F"

<TABLE style="WIDTH: 294pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=391><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; WIDTH: 86pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20 width=114>Age Range</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=105>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=85>39</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 width=87></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>Criteria</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>M</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #002060; WIDTH: 86pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 height=20 width=114>Average Age</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #002060; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=105> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #002060; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=85> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #002060; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=87>TOTAL</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 height=20>COUNT </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>28</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>45</TD></TR></TBODY></TABLE>

I need to find out the average age within the group (between 20 & 39). I've been searching the forum for hours but can't seem to find the answer.

 

[Peter_SSs]

Couldn't you use AVERAGEIFS() in a similar manner to the way you used COUNTIFS()?

 

[grizz]

look at this -- may help
http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

 

[Gordonovitch]

To get average between groups, I tried this but there are too few arguments?

=AVERAGEIFS(AGE,">="&B13,AGE,"<="&C13,SEX,B14)

And this has too many arguments?

=AVERAGE(IF(AGE,">="&B13,AGE,"<="&C13,SEX,B14))

 

[Peter_SSs]

To get average between groups, I tried this but there are too few arguments?

=AVERAGEIFS(AGE,">="&B13,AGE,"<="&C13,SEX,B14)

And this has too many arguments?

=AVERAGE(IF(AGE,">="&B13,AGE,"<="&C13,SEX,B14))

From the Help, the syntax for AVERAGEIFS is

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Your first formula lists a number of criteria_ranges and the corresponding criteria. What it didn't have (as its first argument) was the average_range. So what I think you need is

=AVERAGEIFS(AGE, AGE,">="&B13,AGE,"<="&C13,SEX,B14)

 

[Gordonovitch]

I think I figured it out

=AVERAGEIFS(AGE,AGE,">="&B34,AGE,"<="&C34,SEX,B35)

 

[Gordonovitch]

Thanks for helping me along . . .