Average of income for the last 6 months (rolling) excluding weekends

[Carlos5]

Hello all, I did search but I can't find anything that helps me so here goes:
I am using Excel 2019. Column A has the date, Column B has the day (Mon, Tues, etc), and column C has the total $ made for that day. I am trying to write a formula to give me the average of the last 6 months excluding Sat and Sun. I assume I could use the Column B (the Day column) to exclude Sat and Sun. and use the TODAY() function -180 days to get the time frame but I can't seem to put the two togther.
Thanks for any help you can give me.

 

[awoohaw]

@Carlos5
Post #2 gives a running 6 months average excluding weekends.
None of the solutions that have been offered use VBA.

 

[Carlos5]

@Carlos5
Post #2 gives a running 6 months average excluding weekends.
None of the solutions that have been offered use VBA.

Thanks. I realized that after I posted. I saw a VBA tag and thought that is what it was.

 

[felixstraube]

Another option. You will get the average of the last 180 days excluding weekends
Remember to enter the formula with Ctrl+Shift+Enter as it is an array formula.

Libro1
	A	B	C	D	E
1	Date		Earnings		Average of last 180 days
2	01/01/2023 do		493,00		269,07
3	02/01/2023 lu		214,00
4	03/01/2023 ma		79,00
5	04/01/2023 mi		385,00
6	05/01/2023 ju		100,00
7	06/01/2023 vi		207,00
8	07/01/2023 sá		256,00
9	08/01/2023 do		170,00
10	09/01/2023 lu		429,00
11	10/01/2023 ma		209,00
12	11/01/2023 mi		467,00
13	12/01/2023 ju		413,00
14	13/01/2023 vi		466,00
15	14/01/2023 sá		245,00
16	15/01/2023 do		421,00
17	16/01/2023 lu		456,00
18	17/01/2023 ma		146,00
19	18/01/2023 mi		112,00
Hoja1

Cell Formulas
Range	Formula
E2	E2	=SUM((A2:A10000>TODAY()-180)*(WEEKDAY(A2:A10000,2)<6)*C2:C10000)/SUM((A2:A10000>TODAY()-180)*(WEEKDAY(A2:A10000,2)<6))

 

[Carlos5]

Another option. You will get the average of the last 180 days excluding weekends
Remember to enter the formula with Ctrl+Shift+Enter as it is an array formula.

Libro1
	A	B	C	D	E
1	Date		Earnings		Average of last 180 days
2	01/01/2023 do		493,00		269,07
3	02/01/2023 lu		214,00
4	03/01/2023 ma		79,00
5	04/01/2023 mi		385,00
6	05/01/2023 ju		100,00
7	06/01/2023 vi		207,00
8	07/01/2023 sá		256,00
9	08/01/2023 do		170,00
10	09/01/2023 lu		429,00
11	10/01/2023 ma		209,00
12	11/01/2023 mi		467,00
13	12/01/2023 ju		413,00
14	13/01/2023 vi		466,00
15	14/01/2023 sá		245,00
16	15/01/2023 do		421,00
17	16/01/2023 lu		456,00
18	17/01/2023 ma		146,00
19	18/01/2023 mi		112,00
Hoja1

Cell Formulas
Range	Formula
E2	E2	=SUM((A2:A10000>TODAY()-180)*(WEEKDAY(A2:A10000,2)<6)*C2:C10000)/SUM((A2:A10000>TODAY()-180)*(WEEKDAY(A2:A10000,2)<6))

Hi,
This worked just as I wanted. Thank you.
I also like the pivot table idea. I will try that as well.
Thanks again everyone!

 

[felixstraube]

Happy to help. Thanks for the feedback