Average weekday - excluding days with 0

[Marklarbear]

Hi guys

I have the following array formula which works a treat for me:

{=AVERAGE(IF(WEEKDAY($O$4:$O$34)=6,$P$4:$P$34))}

(averages Fridays within a given date range)

What I want to do now is to modify the formula to exclude any Friday that has a 0 value or no value at all..... I'm thinking it's an "AVERAGEIF" but I'm struggling to crack it...

As always - any help is greatly appreciated!

 

[Habtest]

Try

Test.xlsx
	N	O	P
1	2
2
3
4		4/23/2021	1
5		4/24/2021	99
6		4/25/2021	99
7		4/26/2021	99
8		4/27/2021	99
9		4/28/2021	99
10		4/29/2021	99
11		4/30/2021	0
12		5/1/2021	99
13		5/2/2021	99
14		5/3/2021	99
15		5/4/2021	99
16		5/5/2021	99
17		5/6/2021	99
18		5/7/2021
19		5/8/2021	99
20		5/9/2021	99
21		5/10/2021	99
22		5/11/2021	99
23		5/12/2021	99
24		5/13/2021	99
25		5/14/2021	2
26		5/15/2021	99
27		5/16/2021	99
28		5/17/2021	99
29		5/18/2021	99
30		5/19/2021	99
31		5/20/2021	99
32		5/21/2021	3
Sheet1

Cell Formulas
Range	Formula
N1	N1	=AVERAGE(IF((WEEKDAY($O$4:$O$32)=6)*($P$4:$P$32<>0),$P$4:$P$32))

 

[Marklarbear]

Try

Test.xlsx
	N	O	P
1	2
2
3
4		4/23/2021	1
5		4/24/2021	99
6		4/25/2021	99
7		4/26/2021	99
8		4/27/2021	99
9		4/28/2021	99
10		4/29/2021	99
11		4/30/2021	0
12		5/1/2021	99
13		5/2/2021	99
14		5/3/2021	99
15		5/4/2021	99
16		5/5/2021	99
17		5/6/2021	99
18		5/7/2021
19		5/8/2021	99
20		5/9/2021	99
21		5/10/2021	99
22		5/11/2021	99
23		5/12/2021	99
24		5/13/2021	99
25		5/14/2021	2
26		5/15/2021	99
27		5/16/2021	99
28		5/17/2021	99
29		5/18/2021	99
30		5/19/2021	99
31		5/20/2021	99
32		5/21/2021	3
Sheet1

Cell Formulas
Range	Formula
N1	N1	=AVERAGE(IF((WEEKDAY($O$4:$O$32)=6)*($P$4:$P$32<>0),$P$4:$P$32))

WORKS BRILLIANTLY!!!!

 

[Habtest]

Glad it works, thanks for the feedback