Formula to average all Monday values in a calendar month excluding a holiday

[pbgexcel]

I have a vertical list consisting of every single day in the year and a corresponding price. I'm looking to use AVERAGEIFS() to average only the Monday values in a given month excluding any holidays, the dates for which I have listed elsewhere for reference.

Right now I'm trying to use MONTH()=1 AND WEEKDAY()=1 to specify only the Mondays in January, but to no avail. I also want to exclude 1/1/2018, as New Year's is a holiday, as I have noted in another cell, 'C1', for referencing in the formula. Any ideas how to go about this?

Thanks for your help!

Date	Value
1/1/2018	10
1/2/2018	20
1/3/2018	10
1/4/2018	20
1/5/2018	10
1/6/2018	20
1/7/2018...	10
12/30/2018	20
12/31/2018	10

<tbody>
</tbody>

 

[pbgexcel]

I'm currently working with this, but Excel does not even accept it as a formula:

=AVERAGEIFS(DD4:DD37,DC4:DC37,MONTH()=1,DC4:DC37,WEEKDAY()=1)

DD4:DD37 contains the values to average together
DC4:DC37 contains the corresponding dates for these values

I try to check the month and day of week of these dates as average criteria.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Mon 01-Jan-18	10		FALSE	20
2	Tue 02-Jan-18	20		FALSE
3	Wed 03-Jan-18	10		FALSE
4	Thu 04-Jan-18	20		FALSE
5	Fri 05-Jan-18	10		FALSE
6	Sat 06-Jan-18	20		FALSE
7	Sun 07-Jan-18	10		FALSE
8	Mon 08-Jan-18	20		TRUE
9	Tue 09-Jan-18	10		FALSE
2a

Cell Formulas
Range	Formula
D1	=AND(WEEKDAY(A1,1)=2,COUNTIF(Hol,A1)=0)
E1	=AVERAGEIF(D1:D9,TRUE,B1:B9)

Named Ranges
Name	Refers To	Cells
Hol	='2a'!$J$1

The holidays are in the range named "Hol".
This uses a helper column.

 

[pbgexcel]

Thanks Dave! You've been a great help!

 

[Dave Patton]

without a helper column try

=SUMPRODUCT(--(TEXT(A1:A9,"ddd")="Mon"),--(A1:A9<>Hol),(B1:B9))/SUMPRODUCT(--(TEXT(A1:A9,"ddd")="Mon"),--(A1:A9<>Hol))

 

[Dave Patton]

If you require the calculation by month, you can expand the formulas

=AND(WEEKDAY(A1,1)=2,COUNTIF(Hol,A1)=0,MONTH(A1)=1)

=SUMPRODUCT(--(TEXT(A1:A14,"ddd")="Mon"),--(A1:A14<>Hol),--(MONTH(A1:A14)=1),(B1:B14))/SUMPRODUCT(--(TEXT(A1:A14,"ddd")="Mon"),--(A1:A14<>Hol),--(MONTH(A1:A14)=1))

 

[Dave Patton]

Excel 2010
	A	B	C	F	G
1	Mon 01-Jan-18	10		Mon0118	52.5
2	Tue 02-Jan-18	20
3	Wed 03-Jan-18	10
4	Thu 04-Jan-18	20
5	Fri 05-Jan-18	10
6	Sat 06-Jan-18	20
7	Sun 07-Jan-18	10
8	Mon 08-Jan-18	20
9	Tue 09-Jan-18	10
10	Mon 15-Jan-18	30
11	Tue 16-Jan-18	40
12	Mon 22-Jan-18	50
13	Mon 29-Jan-18	110
14	Mon 05-Feb-18	70
2a

Cell Formulas
Range	Formula
G1	=SUMPRODUCT(--(TEXT(A1:A14,"dddmmyy")=F1),--(A1:A14<>Hol),(B1:B14))/SUMPRODUCT(--(TEXT(A1:A14,"dddmmyy")=F1),--(A1:A14<>Hol))

Named Ranges
Name	Refers To	Cells
Hol	='2a'!$J$1

 

[barry houdini]

Won't the holidays be a range of cells rather than a single cell, in which case that last formula wouldn't work

Possibly try an array formula like this:

=AVERAGE(IF(TEXT(A1:A14,"dddmmyy")=F1,IF(ISNA(MATCH(A1:A14,Hols,0)),B1:B14)))

confirm with CTRL+SHIFT+ENTER

 

[mohdabrar]

What should I do if I don't want to exclude the formulae on this one. Also, my data is horizontal. Dates are in AF2:BJ4 and the Values I need to Average are in AF4:BJ4. Any help on this would be great! Thanks!

 

[mohdabrar]

What should I do if I don't want to exclude the formulae on this one. Also, my data is horizontal. Dates are in AF2:BJ4 and the Values I need to Average are in AF4:BJ4. Any help on this would be great! Thanks!

What should I do if I don't want to exclude Holidays in the formulae on this one. Also, my data is horizontal. Dates are in AF2:BJ4 and the Values I need to Average are in AF4:BJ4. Any help on this would be great! Thanks!

I wanted to edit, couldn't find an option.