Averaging data over three years based on date of a weekday or weekend

[nmusoke17]

I have data based on date and day over three years. however, I want to do an average of the data for a particular date over the three years and if that date is a weekday for all three years it averages for all the years and if not, it only averages for the number of times that date falls on a weekday for those previous three years. What excel formula can I use to do that automatically?

 

[awoohaw]

Here are the same formulas i used in POST#5 using your new data (plus 20 more rows, since you did not include any different years). I also purposefully left the first column as numeric values of the dates (the first date is 2020/7/1). I aslo am showing the decimals in the second column.

PLEASE paste this exactly into a new worksheet or workbook. After you paste you may need to re-enter the formula (press F2 and then CSE or Enter).
(if you have to change commas to semi colons, please do so carefully). Also, you may not need to use CSE to commit the array formula for excel 2019, but if you get an error please try that.

Book1
	A	B	C	D	E	F	G	H
24						FILTER		CSE
25	44013	282,691.00			2020-Jul-01	162,534.67		162,534.67
26	44014	276,428.00			2020-Jul-02	192,917.50		192,917.50
27	44015	482,806.00			2020-Jul-03	482,806.00		482,806.00
28	44016	531,723.00			2020-Jul-04	109,721.00		109,721.00
29	44017	780,749.00			2020-Jul-05	104,475.00		104,475.00
30	44018	395,985.00			2020-Jul-06	202,139.00		202,139.00
31	44019	232,047.00			2020-Jul-07	144,890.33		144,890.33
32	44020	948,012.00			2020-Jul-08	386,411.33		386,411.33
33	44021	384,201.00			2020-Jul-09	245,452.00		245,452.00
34	44022	527,073.00			2020-Jul-10	527,073.00		527,073.00
35	44378	101,056.00
36	44379	109,407.00
37	44380	101,667.00
38	44381	105,914.00
39	44382	102,409.00
40	44383	104,376.00
41	44384	100,496.00
42	44385	101,644.00
43	44386	106,703.00
44	44387	109,928.00
45	44743	103,857.00
46	44744	106,815.00
47	44745	104,247.00
48	44746	109,721.00
49	44747	106,541.00
50	44748	106,056.00
51	44749	102,128.00
52	44750	109,578.00
53	44751	103,820.00
54	44752	106,012.00
Sheet1

Cell Formulas
Range	Formula
F25:F34	F25	=AVERAGE(FILTER($B$25:$B$54,(MONTH(E25)=MONTH($A$25:$A$54))*(DAY(E25)=DAY($A$25:$A$54))*(WEEKDAY($A$25:$A$54,2)<6)))
H25:H34	H25	=SUM((MONTH(E25)=MONTH(A25:A54))*(DAY(E25)=DAY(A25:A54))*(WEEKDAY(A25:A54,2)<6)*(B25:B54))/ SUM((MONTH(E25)=MONTH(A25:A54))*(DAY(E25)=DAY(A25:A54))*(WEEKDAY(A25:A54,2)<6))
Press CTRL+SHIFT+ENTER to enter array formulas.