Calculate Age then Average Based on Condition

[cougster00]

I have a spreadsheet where I am trying to return the average age of a patient seen at a particular office. I need to calculate their age by subtracting the DateSeen (Column A) by their DOB (Column C) and then return the results conditionally based on whether they are seen at office 0 or office 1 (Column B). I can use the embed subtraction into an AVERAGE function, but when I attempt to put it into an AVERAGEIF function, it will not result. Any help would be appreciated.

DateSeen Office DOB ***
8/15/12 0 9/16/39 Male
8/22/12 0 4/20/28 Male
8/22/12 0 3/4/22 Female
9/5/12 1 6/7/26 Male
9/19/12 0 8/5/31 Female
12/19/12 1 5/24/31 Male
1/9/13 1 4/8/29 Male
1/16/13 0 2/1/46 Male

[Tony Valko]

Why don't you include an age column then you can use that for the average calculation.

Data Range

	A	B	C	D	E	F	G	H
1	Date Seen	Office	DOB	Gender	Age	------	Office	AVG
2	8/15/2012	0	9/16/1939	Male	72		0	79
3	8/22/2012	0	4/20/1928	Male	84		1	83
4	8/22/2012	0	3/4/1922	Female	90
5	9/5/2012	1	6/7/1926	Male	86
6	9/19/2012	0	8/5/1931	Female	81
7	12/19/2012	1	5/24/1931	Male	81
8	1/9/2013	1	4/8/1929	Male	83
9	1/16/2013	0	2/1/1946	Male	66

This formula entered in E2 and copied down:

=DATEDIF(C2,A2,"Y")

For the average, this formula entered in H2 and copied down:

=ROUND(AVERAGEIF(B$2:B$9,G2,E$2:E$9),0)

[Tony Valko]

If you would rather not have an age column...

Data Range

	A	B	C	D	E	F	G
1	Date Seen	Office	DOB	Gender	------	Office	AVG
2	8/15/2012	0	9/16/1939	Male		0	79
3	8/22/2012	0	4/20/1928	Male		1	83
4	8/22/2012	0	3/4/1922	Female
5	9/5/2012	1	6/7/1926	Male
6	9/19/2012	0	8/5/1931	Female
7	12/19/2012	1	5/24/1931	Male
8	1/9/2013	1	4/8/1929	Male
9	1/16/2013	0	2/1/1946	Male

This array formula** entered in G2 and copied down:

=ROUND(AVERAGE(IF(B$2:B$9=F2,DATEDIF(C$2:C$9,A$2:A$9,"Y"))),0)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Pete_UK]

In the attached file I've used column E to give you the age in years (and fractions of a year) using this formula in E2:

=IF(A2="","",(A2-C2)/365.25)

(this is approximately right), then in the summary table this formula in I2 can give you the average for Males in Office 0:

=IFERROR(AVERAGEIFS($E:$E,$D:$D,I$1,$B:$B,$H2),"")

which can be copied into J2 and down, and this formula can give you the overall average for each office in K2, copied down:

=IFERROR(AVERAGEIFS($E:$E,$B:$B,$H2),"")

Hope this helps.

Pete