average formula based on date

[spacecaptainsuperguy]

I am so close to the answer, but I've spent the last couple of days floundering on it and I'm to the point where I could use some suggestions. I've got some data that shows a resignation date and a refund date and a field that calculates the number of days between the two. What I'm looking to do is create a formula that will calculate the average number of days for a given year. So for example, what was the average wait time for people that were refunded in 2007? Here is some sample data that I've been using to try to figure this one out. I suspect it will need an array formula & I've built a few over the years, but I'm not awesome with them. Any suggestions are much appreciated.

If the formula can calculate the number of days within the formula that would be even more awesome, since I don't really need that column of data (column D below)

Excel Workbook
	A	B	C	D
1	Name	Resigned	Refunded	No. of Days
2	Gary Wooton	12/28/2005	2/24/2006	58
3	Rand McQuinn	12/1/2005	4/28/2006	148
4	William Washburn	1/16/2005	6/5/2006	505
5	Anthony Phifer	7/26/2006	12/12/2006	139
6	Trip Duperier	7/28/2006	4/4/2007	250
7	F. O'neil Griffin	8/24/2006	5/9/2007	258
8	Tracy Wodskow	9/20/2006	6/20/2007	273
9	Charles Farrell	12/6/2006	12/19/2007	378
10	Graham King	5/11/2006	1/15/2008	614
11	Scott Thompson	12/6/2006	1/21/2008	411
12	Larry Johnson	12/15/06	04/10/08	482
13	Walter Johnson	12/16/06	5/30/2008	531
14	Harland Dunn	12/20/2006	8/17/2008	606
15	Virginia Turner	12/31/2006	8/18/2008	596
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=+C2-B2
D3	=+C3-B3
D4	=+C4-B4
D5	=+C5-B5
D6	=+C6-B6
D7	=+C7-B7
D8	=+C8-B8
D9	=+C9-B9
D10	=+C10-B10
D11	=+C11-B11
D12	=+C12-B12
D13	=+C13-B13
D14	=+C14-B14
D15	=+C15-B15

 

[Desu Nota from Columbus]

Couldn't you use a pivot table with the posted data, Group the category "Refunded" by year and set the values to average?

 

[spacecaptainsuperguy]

Unfortunately no, for two reasons. This data is broken up into various blocks with empty rows between sections of them and I'm wanting to put this information in an existing management summary that lies on another sheet.

Ideally I will have this formula sitting in a column with a result for each year shown on the management summary.

 

[Desu Nota from Columbus]

I got this to work, I used a combination of helper cells and sumif/countif.

Insert a new column D, pushing #Days to column E.

D2= =TEXT(C2, "yyyy") --copy this down the column


In I1,J1,K1,L1,M1,.... put 2005,2006,2007,2008,... respectively.

In I2 put =SUMIF($D$2:$D$1460,I$1,$E$2:$E$1460)/COUNTIF($D$2:$D$1460,I$1)

Copy this formula across j2,k2,l2.....


Change the bounds of the array, i trialed this with 1460 records.

Does this make sense? Will it work with your data?

 

[spacecaptainsuperguy]

Desu,
That works great. I will keep working to see if there is a way to do your column D calculation (for the year) withing the final formula, but as is, it gets me where I need to be.

Thank you very much.