Cohort Aging Analysis using AVERAGEIF and SMALL w/ IF criteria

[krisrdagon88]

Hello wise ones,

I am doing some cohort analysis, and it is my first time trying functions like SMALL, LARGE, AGGREGATE, etc. This is as far as I got, and I'm stuck! I've included a Dropbox link to an Excel file with select data. In summary -

(See Screenshot #1) For work, I am doing cohort aging analysis on all online applications that have been submitted for month-to-date April. In the Excel file > Data tab, each row represents an application, and there are 3 columns: Date Added (aka submitted), Date Approved, Duration (from Date Added to Approved). FYI - You'll see in the file that many don't have Date Approved timestamps yet, because, well, they haven't been processed. I'll be exporting this data weekly so there will new approval timestamps every week.

(See Screenshot #2) Back to the cohort aging analysis, I'm trying to find out, on average, how many days it takes the first 25% of applications to go from Date Added to Date Approved, then the next 25%, and the next 25%, and so on. On top of that, I'd like to view each week's application submissions as their own cohort.

Question: You'll see that I attempted a formula for 2 of the cells (the rest I just put "X days" as placeholder), using a combination of AVERAGEIF and SMALL, and I get the "DIV/0" error. How should I correct it? And/or is there another better way to set up this formula?

Screenshot #1

Unknown
	A	B	C
1	Date Added	Date Approved	Duration
2	4/6/16 17:52	4/18/16 15:03	11.8830208
3	4/3/16 15:09	4/18/16 15:08	14.999375
4	4/4/16 16:47	4/18/16 15:11	13.9334028
5	4/1/16 16:59	4/18/16 15:14	16.9271875
6	4/13/16 0:40	4/18/16 15:30	5.61798611
7	4/12/16 12:21	4/18/16 15:32	6.13277778
8	4/2/16 15:55	4/18/16 15:35	15.9862384
9	4/6/16 18:56	4/18/16 15:37	11.8616782
Data

Screenshot #2

FYI, I attempted to troubleshoot on my own - I isolated the nested formula

=SMALL(IF((Data!A:A>=B3)*(Data!A:A<B4),Data!B:B),5)​

The 5th smallest / earliest approval date, for apps that came in between 4/2 and 4/8, should be 4/18/2016 3:35:35 PM. However, it returned 4/18/2016 3:30:13 PM instead, literally the 5th earliest approval date of all applications, ignoring the nested Date Added criteria. Not sure why!

Unknown
	A	B	C	D	E
1	Average # of days from Date Added to Approved
2	Date Added:	>=4/2 and <4/8	>=4/8 and <4/14	>=4/15 and <4/21	>=4/22 and <4/28
3	Beginning	4/2/16	4/8/16	4/15/16	4/22/16
4	End	4/8/16	4/14/16	4/21/16	4/28/16
5	# of apps	253	253	210	53
6	1st quartile	#DIV/0!	X days	X days	X days
7	2nd quartile	#DIV/0!	X days	X days	X days
8	3rd quartile	X days	X days	X days	X days
9	4th quartile	X days	X days	X days	X days
Summary

Cell Formulas
Range	Formula
B5	=COUNTIFS(Data!$A:$A,">="&B3,Data!$A:$A,"<"&B4)
B6	{=AVERAGEIF(Data!$B:$B,"<="&SMALL(IF((Data!$A:$A>=B$3)*(Data!$A:$A),Data!$B:$B),B$5/4),Data!$C:$C)}
B7	{=AVERAGEIF(Data!$B:$B,"<="&SMALL(IF((Data!$A:$A>=B$3)*(Data!$A:$A),Data!$B:$B),B$5/4*2),Data!$C:$C)}
C5	=COUNTIFS(Data!$A:$A,">="&C3,Data!$A:$A,"<"&C4)
D5	=COUNTIFS(Data!$A:$A,">="&D3,Data!$A:$A,"<"&D4)
E5	=COUNTIFS(Data!$A:$A,">="&E3,Data!$A:$A,"<"&E4)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nishant94]

It would be helpful if you could post some expected result with smaller sample data.

 

[krisrdagon88]

Sure, let me just use the same data first, this is what I'm expecting

For the 253 applications that came in between 4/2-4/8, the first 25% to get approved averaged 14 days to approval. That's the result I'm expecting.

FYI - First 25% would be 63 apps, but in the data set only 48 have been approved so far, so the 14 days cited above is actually of the first 48 apps.

Appreciate any guidance or suggestions, and happy to provide more clarification!

Unknown
	A	B
14	Date Added:	>=4/2 and <4/8
15	Beginning	4/2/16
16	End	4/8/16
17	# of apps	253
18	1st quartile	14.03
19	2nd quartile
20	3rd quartile
21	4th quartile
Summary

 

[krisrdagon88]

Here's a smaller sample, and below this I pasted what I would expect. I've added it to the Dropbox file.

Unknown
	A	B	C
1	Date Added	Date Approved	Duration
2	4/6/16 17:52	4/18/16 15:03	11.8830208
3	4/3/16 15:09	4/18/16 15:08	14.999375
4	4/4/16 16:47	4/18/16 15:11	13.9334028
5	4/1/16 16:59	4/18/16 15:14	16.9271875
6	4/13/16 0:40	4/18/16 15:30	5.61798611
7	4/12/16 12:21	4/18/16 15:32	6.13277778
8	4/2/16 15:55	4/18/16 15:35	15.9862384
9	4/6/16 18:56	4/18/16 15:37	11.8616782
10	4/2/16 18:54	4/18/16 15:51	15.872662
11	4/6/16 21:04	4/18/16 15:52	11.7831134
12	4/2/16 16:24	4/18/16 15:58	15.9814931
Example Data

Unknown
	A	B	C
1	Date Added:	>=4/2 and <4/8	>=4/8 and <4/14
2	Beginning	4/2/16	4/8/16
3	End	4/8/16	4/14/16
4	# of apps	8	2
5	1st quartile	13.44	5.88
6	2nd quartile	14.96
7	3rd quartile	13.87
8	4th quartile	13.88
Example expected summary

Cell Formulas
Range	Formula
B4	=COUNTIFS('Example Data'!$A:$A,">="&B2,'Example Data'!$A:$A,"<"&B3)
B5	=AVERAGE('Example Data'!C2:C3)
B6	=AVERAGE('Example Data'!C4,'Example Data'!C8)
B7	=AVERAGE('Example Data'!C9:C10)
B8	=AVERAGE('Example Data'!C11:C12)
C4	=COUNTIFS('Example Data'!$A:$A,">="&C2,'Example Data'!$A:$A,"<"&C3)
C5	=AVERAGE('Example Data'!C6:C7)

 

[krisrdagon88]

Hi, would anybody be able to enlighten me on this situation? or if I'm approaching it the wrong way and should try a new setup, open to that as well. Thanks!