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]

Maybe this:

Book2
	A	B	C	D	E
1	Date
2	Wed 2024-05-01	100		05-01	75
3	Thu 2024-05-02	100		05-02	53.33333
4	Fri 2024-05-03	100		05-03	53.33333
5	Sat 2024-05-04	100		05-04	30
6	Sun 2024-05-05	100		05-05	30
7	Mon 2024-05-06	100		05-06	55
8	Tue 2024-05-07	100		05-07	100
9	Mon 2023-05-01	50
10	Tue 2023-05-02	50
11	Wed 2023-05-03	50
12	Thu 2023-05-04	50
13	Fri 2023-05-05	50
14	Sat 2023-05-06	50
15	Sun 2023-05-07	50
16	Sun 2022-05-01	10
17	Mon 2022-05-02	10
18	Tue 2022-05-03	10
19	Wed 2022-05-04	10
20	Thu 2022-05-05	10
21	Fri 2022-05-06	10
22	Sat 2022-05-07	10
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=AVERAGE(FILTER(B2:B22,(MONTH(D2)=MONTH(A2:A22))*(DAY(D2)=DAY(A2:A22))*(WEEKDAY(A2:A22,2)<6)))
D3:D8,A3:A8	D3	=D2+1
A9:A22	A9	=EDATE(A2,-12)

Hope it helps. And welcome to the forum.

 

[nmusoke17]

I have tried using that formula but Excel keeps giving me a message that “formula is invalid. It may be because Ms excel 2019 doesn’t have “FILTER” as a function, is it doesn’t recogise it in the formula. What could be the workaround.

Thanks

 

[awoohaw]

Mmm. Yes, I'll work on something. But, usually when a function is not available it is a #NAME error not a formula invalid error. Have you tried pasting the entire mini sheet in a brand new workbook or worksheet? If it works like that it should be in your version.

Also, when I was working on the formula I looked at the contextual help of the filter in a formula dialog box and it says it is in 2019:

(Also please paste the formula you used, as you may have a typo)

 

[awoohaw]

Here it is as a CSE (CNTL SHFT ENTR) Array formula:

Book1
	A	B	C	D	E	F	G
1	Date				FILTER		CSE
2	Wed 2024-05-01	100		Wed 2024-05-01	75		75
3	Thu 2024-05-02	100		Thu 2024-05-02	53.33333333		53.33333
4	Fri 2024-05-03	100		Fri 2024-05-03	53.33333333		53.33333
5	Sat 2024-05-04	100		Sat 2024-05-04	30		30
6	Sun 2024-05-05	100		Sun 2024-05-05	30		30
7	Mon 2024-05-06	100		Mon 2024-05-06	55		55
8	Tue 2024-05-07	100		Tue 2024-05-07	100		100
9	Mon 2023-05-01	50
10	Tue 2023-05-02	50
11	Wed 2023-05-03	50
12	Thu 2023-05-04	50
13	Fri 2023-05-05	50
14	Sat 2023-05-06	50
15	Sun 2023-05-07	50
16	Sun 2022-05-01	10
17	Mon 2022-05-02	10
18	Tue 2022-05-03	10
19	Wed 2022-05-04	10
20	Thu 2022-05-05	10
21	Fri 2022-05-06	10
22	Sat 2022-05-07	10
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=AVERAGE(FILTER(B2:B22,(MONTH(D2)=MONTH(A2:A22))*(DAY(D2)=DAY(A2:A22))*(WEEKDAY(A2:A22,2)<6)))
D3:D8,A3:A8	D3	=D2+1
G2:G8	G2	=SUM((MONTH(D2)=MONTH(A2:A22))*(DAY(D2)=DAY(A2:A22))*(WEEKDAY(A2:A22,2)<6)*(B2:B22))/ SUM((MONTH(D2)=MONTH(A2:A22))*(DAY(D2)=DAY(A2:A22))*(WEEKDAY(A2:A22,2)<6))
A9:A22	A9	=EDATE(A2,-12)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Cubist]

Mmm. Yes, I'll work on something. But, usually when a function is not available it is a #NAME error not a formula invalid error. Have you tried pasting the entire mini sheet in a brand new workbook or worksheet? If it works like that it should be in your version.

Also, when I was working on the formula I looked at the contextual help of the filter in a formula dialog box and it says it is in 2019:

(Also please paste the formula you used, as you may have a typo)
View attachment 112361

I believe that page is wrong. FILTER is available starting from version 2021.

 

[awoohaw]

I believe that page is wrong. FILTER is available starting from version 2021.

Thanks. Good to know.

But, why did it generate a FORMULA ERROR instead of a #NAME error?

But, I did provide an alternate solution.

 

[Cubist]

Thanks. Good to know.

But, why did it generate a FORMULA ERROR instead of a #NAME error?

But, I did provide an alternate solution.

It might just be how Excel handled errors in 2019. I don't recall.
Yes, I've noticed. Just wanted to clarify the confusion above.

 

[nmusoke17]

Thanks a lot for the support. I have tried using the second formula you shared, but it also doesn't seem to work as expected. let me share with you some datasets and see how can help out.

01/07/2020​	282,691
02/07/2020​	276,428
03/07/2020	482,806
04/07/2020	531,723
05/07/2020	780,749
06/07/2020	395,985
07/07/2020	232,047
08/07/2020	948,012
09/07/2020	384,201
10/07/2020	527,073

 

[awoohaw]

Thanks a lot for the support. I have tried using the second formula you shared, but it also doesn't seem to work as expected. let me share with you some datasets and see how can help out.

01/07/2020​	282,691
02/07/2020​	276,428
03/07/2020	482,806
04/07/2020	531,723
05/07/2020	780,749
06/07/2020	395,985
07/07/2020	232,047
08/07/2020	948,012
09/07/2020	384,201
10/07/2020	527,073

showing us the formula you used would be a better way for us to help you. And not telling us the exact error message is no help at all.

But just looking at the table... it seems you have dates that are text and not dates. Be sure all the first column are dates (select the entire range of first column and format as a number. If the values do not change to numbers you have text values not date values). The same for the second column, except try to add decimals.... if they remain left justified or do not display .00, .0, etc. then they are also text and not numbers..

And did you paste the solutions i offereed into a fresh workbook or worksheet???? What happened?