Vlookup, If and then, Average, and many other formula help!

[kiyo052]

Please let me know if this is at all possible! I personally think there are way too many formulas to make this happen but please let me know!


Is there an easier formula that can be dragged for D15 to AF18? I need to find the average of each month for everyone in a specific tenure group starting from their first month.

The Tenure group is in C15:C18
As of right now this is what I have:
D15=AVERAGE(D2,E3,G4)
D16=AVERAGE(P7,M6,I5)
D17=AVERAGE(S8,W9,X10)
D18=AVERAGE(AA11,AC12)

What I'm trying to do is make a formula for D15 that says IF column A is greater than 24 then take the average of everyone's 1st month which is located in column B.

In E15 (which is month 2) the formula would be IF Column A is greater than 24 then take the average of everyone's 2nd month.

This would drag across all the way to the last column and I would do this for tenure groups 15-24, 8-14, and 1-7

Please let me know if I can clarify anything as I am sure that this is very confusing!

Thank you in advance for anyone that can provide any input!!!!

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Tenure	Start Date	Name	Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	Jan-15	Feb-15	Mar-15	Apr-15	May-15
2	29.97	1/1/2013	Matthew	$59,287	$59,161	$113,255	$96,248	$128,844	$114,217	$115,908	$82,845	$107,303	$107,639	$77,705	$87,817	$102,261	$92,543	$125,829	$135,105	$213,091	$158,398	$193,129	$200,506	$188,946	$186,342	$188,254	$151,350	$190,950	$186,674	$253,938	$274,605	$209,456
3	28.93	2/1/2013	Greg	$16,542	$27,795	$28,939	$38,866	$43,492	$80,073	$57,801	$63,640	$45,799	$68,252	$65,503	$59,716	$135,563	$104,069	$114,776	$205,059	$136,233	$144,011	$187,934	$181,242	$168,550	$196,129	$154,527	$134,175	$216,370	$124,917	$154,501	$236,750	$201,280
4	26.97	4/1/2013	Troy			$2,872	$15,092	$59,822	$45,384	$82,824	$120,211	$109,597	$100,088	$72,126	$135,618	$225,843	$132,559	$130,155	$182,781	$199,867	$216,417	$178,550	$170,034	$191,201	$341,320	$163,497	$158,710	$198,438	$345,657	$222,144	$347,713	$222,572
5	24.93	6/1/2013	Christopher					$1,294	$23,312	$29,698	$29,171	$49,605	$83,043	$31,262	$37,569	$69,303	$45,577	$72,735	$78,502	$70,543	$114,417	$121,170	$95,782	$90,006	$76,057	$85,051	$114,759	$97,958	$116,575	$129,445	$107,414	$128,222
6	20.87	10/1/2013	David								$6,515	$1,000	$69,128	$51,573	$55,099	$76,198	$112,926	$122,656	$105,875	$121,016	$109,929	$211,527	$99,252	$107,354	$250,621	$81,319	$161,331	$92,995	$122,268	$108,917	$147,809	$133,110
7	17.8	1/1/2014	Erin											$925	$362	$12,222	$38,723	$58,137	$74,851	$62,644	$90,484	$79,777	$157,931	$144,617	$127,140	$162,065	$167,690	$109,370	$180,775	$166,324	$201,250	$228,167
8	14.8	4/1/2014	Kyle														$1,153	$1,620	$31,183	$51,500	$84,367	$90,372	$151,424	$199,323	$220,469	$164,562	$168,557	$192,265	$140,232	$210,822	$199,984	$184,835
9	10.73	8/1/2014	Jonathan																				$4,123	$55,962	$73,412	$103,664	$101,761	$91,746	$102,481	$103,655	$133,664	$138,004
10	9.7	9/1/2014	Mike																				$30,737	$59,027	$76,968	$85,653	$99,477	$114,536	$76,936	$103,459	$90,250	$108,985
11	6.67	12/1/2014	Tammy																						$1,829	$8,864	$63,822	$123,548	$55,031	$83,184	$86,807	$101,972
12	4.6	2/1/2015	Page																									$13,928	$86,399	$120,099	$138,945	$152,139
13
14				Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12	Month 13	Month 14	Month 15	Month 16	Month 17	Month 18	Month 19	Month 20	Month 21	Month 22	Month 23	Month 24	Month 25	Month 26	Month 27	Month 28	Month 29
15			Tenure 25+	$34,058	$49,307	$65,835
16			Tenure 15-24	$34,887	$39,998	$47,469
17			Tenure 8-14	$31,444	$61,477	$81,144
18			Tenure 1-7	$75,111	$121,824	$96,988

<colgroup><col><col><col><col><col><col><col><col span="25"></colgroup><tbody>
</tbody>

 

[DRSteele]

I think I might have stumbled upon an answer for you:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Tenure	Start Date	Name	January 1, 2013	February 1, 2013	March 1, 2013	April 1, 2013	May 1, 2013	June 1, 2013	July 1, 2013	August 1, 2013	September 1, 2013	October 1, 2013	November 1, 2013	December 1, 2013	January 1, 2014	February 1, 2014	March 1, 2014	April 1, 2014	May 1, 2014	June 1, 2014	July 1, 2014	August 1, 2014	September 1, 2014	October 1, 2014	November 1, 2014	December 1, 2014	January 1, 2015	February 1, 2015	March 1, 2015	April 1, 2015	May 1, 2015
2	29.97	January 1, 2013	Matthew	$59,287	$59,161	$113,255	$96,248	$128,844	$114,217	$115,908	$82,845	$107,303	$107,639	$77,705	$87,817	$102,261	$92,543	$125,829	$135,105	$213,091	$158,398	$193,129	$200,506	$188,946	$186,342	$188,254	$151,350	$190,950	$186,674	$253,938	$274,605	$209,456
3	28.93	February 1, 2013	Greg	$16,542	$27,795	$28,939	$38,866	$43,492	$80,073	$57,801	$63,640	$45,799	$68,252	$65,503	$59,716	$135,563	$104,069	$114,776	$205,059	$136,233	$144,011	$187,934	$181,242	$168,550	$196,129	$154,527	$134,175	$216,370	$124,917	$154,501	$236,750	$201,280
4	26.97	April 1, 2013	Troy			$2,872	$15,092	$59,822	$45,384	$82,824	$120,211	$109,597	$100,088	$72,126	$135,618	$225,843	$132,559	$130,155	$182,781	$199,867	$216,417	$178,550	$170,034	$191,201	$341,320	$163,497	$158,710	$198,438	$345,657	$222,144	$347,713	$222,572
5	24.93	June 1, 2013	Christopher					$1,294	$23,312	$29,698	$29,171	$49,605	$83,043	$31,262	$37,569	$69,303	$45,577	$72,735	$78,502	$70,543	$114,417	$121,170	$95,782	$90,006	$76,057	$85,051	$114,759	$97,958	$116,575	$129,445	$107,414	$128,222
6	20.87	October 1, 2013	David								$6,515	$1,000	$69,128	$51,573	$55,099	$76,198	$112,926	$122,656	$105,875	$121,016	$109,929	$211,527	$99,252	$107,354	$250,621	$81,319	$161,331	$92,995	$122,268	$108,917	$147,809	$133,110
7	17.8	January 1, 2014	Erin											$925	$362	$12,222	$38,723	$58,137	$74,851	$62,644	$90,484	$79,777	$157,931	$144,617	$127,140	$162,065	$167,690	$109,370	$180,775	$166,324	$201,250	$228,167
8	14.8	April 1, 2014	Kyle														$1,153	$1,620	$31,183	$51,500	$84,367	$90,372	$151,424	$199,323	$220,469	$164,562	$168,557	$192,265	$140,232	$210,822	$199,984	$184,835
9	10.73	August 1, 2014	Jonathan																				$4,123	$55,962	$73,412	$103,664	$101,761	$91,746	$102,481	$103,655	$133,664	$138,004
10	9.7	September 1, 2014	Mike																				$30,737	$59,027	$76,968	$85,653	$99,477	$114,536	$76,936	$103,459	$90,250	$108,985
11	6.67	December 1, 2014	Tammy																						$1,829	$8,864	$63,822	$123,548	$55,031	$83,184	$86,807	$101,972
12	4.6	February 1, 2015	Page																									$13,928	$86,399	$120,099	$138,945	$152,139
13
14				month
15				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29
16		Tenure 25+	25	34,058.00	49,307.33	65,835.00	74,188.00	109,709.33	93,871.67	93,212.00	66,923.33	103,724.33	132,995.00	89,993.33	117,845.00	129,703.67	135,728.67	182,435.00	149,962.67	175,712.00	179,177.67	238,563.67	177,517.67	181,261.67	179,769.00	222,695.33	196,621.33	221,193.33	187,915.67	163,562.67	158,628.33	69,818.67
17		Tenure 15-24	15	34,887.33	39,998.00	47,469.00	66,884.67	86,204.33	81,467.33	74,407.00	116,083.33	100,041.00	137,134.00	113,273.00	115,195.67	158,136.00	127,754.67	141,145.67	128,083.67	142,164.00	64,656.00	87,522.67	77,022.67	38,858.33	43,148.33	35,804.67	42,740.67	0.00	0.00	0.00	0.00	0.00
18		Tenure 8-14	8	31,444.33	61,476.67	81,144.00	97,837.67	122,573.67	122,668.33	142,136.33	119,489.00	137,068.67	110,089.67	46,744.00	70,274.00	66,661.33	61,611.67	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
19		Tenure 1-7	1	75,110.50	121,823.50	96,988.00	117,661.50	43,403.50	50,986.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Sheet47

Cell Formulas
Range	Formula
D16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
D17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
D18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
D19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
E16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+E$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
E17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+E$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
E18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+E$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
E19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+E$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
F16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+F$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
F17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+F$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
F18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+F$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
F19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+F$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
G16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+G$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
G17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+G$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
G18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+G$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
G19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+G$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
H16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+H$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
H17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+H$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
H18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+H$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
H19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+H$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
I16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+I$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
I17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+I$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
I18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+I$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
I19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+I$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
J16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+J$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
J17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+J$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
J18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+J$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
J19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+J$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
K16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+K$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
K17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+K$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
K18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+K$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
K19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+K$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
L16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+L$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
L17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+L$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
L18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+L$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
L19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+L$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
M16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+M$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
M17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+M$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
M18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+M$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
M19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+M$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
N16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+N$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
N17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+N$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
N18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+N$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
N19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+N$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
O16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+O$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
O17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+O$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
O18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+O$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
O19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+O$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
P16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+P$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
P17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+P$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
P18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+P$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
P19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+P$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
Q16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Q$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
Q17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Q$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
Q18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Q$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
Q19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Q$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
R16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+R$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
R17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+R$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
R18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+R$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
R19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+R$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
S16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+S$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
S17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+S$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
S18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+S$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
S19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+S$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
T16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+T$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
T17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+T$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
T18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+T$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
T19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+T$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
U16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+U$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
U17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+U$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
U18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+U$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
U19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+U$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
V16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+V$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
V17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+V$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
V18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+V$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
V19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+V$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
W16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+W$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
W17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+W$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
W18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+W$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
W19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+W$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
X16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+X$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
X17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+X$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
X18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+X$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
X19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+X$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
Y16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Y$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
Y17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Y$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
Y18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Y$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
Y19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Y$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
Z16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Z$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
Z17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Z$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
Z18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Z$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
Z19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+Z$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
AA16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AA$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AA17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AA$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AA18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AA$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
AA19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AA$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
AB16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AB$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AB17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AB$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AB18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AB$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
AB19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AB$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
AC16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AC$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AC17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AC$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AC18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AC$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
AC19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AC$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
AD16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AD$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AD17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AD$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AD18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AD$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
AD19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AD$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
AE16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AE$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AE17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AE$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AE18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AE$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
AE19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AE$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))
AF16	=SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AF$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AF17	=SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AF$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--($B$2:$B$12=$D$1:$AF$1)))
AF18	=SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AF$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--($B$2:$B$12=$D$1:$AF$1)))
AF19	=SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+AF$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--($B$2:$B$12=$D$1:$AF$1)))

 

[kiyo052]

Thanks for the help! I will test this now!

 

[kiyo052]

Hey DRSteele,

Thanks again for looking into this! I see one small issue. For H19, it is taking the average of AE11 and AG12. Is there a way to make the average stops when the month starts? For example, instead of H19= $43,404 it would actually be H19=$86,807. This is because Page has not had a month 5 yet.

Thank you so much for your help. Please let me know if I need to clarify anything else. You are super helpful!

 

[DRSteele]

Oh I see now. I fixed it so that it correctly counts the number of months and avoids DIV#0 errors.

Copy this column (i.e., D16 to D19) all the way to the right:

Excel 2012
	B	C	D
14			month
15			1
16	Tenure 25+	25	34,058.00
17	Tenure 15-24	15	34,887.33
18	Tenure 8-14	8	31,444.33
19	Tenure 1-7	1	75,110.50
Sheet47

Cell Formulas
Range	Formula
D16	=IFERROR(SUMPRODUCT(--($A$2:$A$12>=$C16)*$D$2:$AF$12,--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C16)*(--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))),"")
D17	=IFERROR(SUMPRODUCT(--($A$2:$A$12>=$C17)*$D$2:$AF$12*--($A$2:$A$12<$C16),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C17)*--($A$2:$A$12<$C16)*(--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))),"")
D18	=IFERROR(SUMPRODUCT(--($A$2:$A$12>=$C18)*$D$2:$AF$12*--($A$2:$A$12<$C17),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C18)*--($A$2:$A$12<$C17)*(--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))),"")
D19	=IFERROR(SUMPRODUCT(--($A$2:$A$12>=$C19)*$D$2:$AF$12*--($A$2:$A$12<$C18),--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))/SUMPRODUCT(--($A$2:$A$12>=$C19)*--($A$2:$A$12<$C18)*(--(DATE(YEAR($B$2:$B$12),MONTH($B$2:$B$12)+D$15-1,DAY($B$2:$B$12))=$D$1:$AF$1))),"")