Formula question re week days

[GMD1975]

Hi,

Using the below as an example: I want to calculate the average number of row 2 but only where the date in row 1 is a week day (mon/fri)... Can anyone help please?

Kind Regards
Gerard

A</SPAN>

B</SPAN>

C</SPAN>

D</SPAN>

E</SPAN>

F</SPAN>

G</SPAN>

H</SPAN>

I</SPAN>

J</SPAN>

K</SPAN>

L</SPAN>

M</SPAN>

N</SPAN>

O</SPAN>

P</SPAN>

Q</SPAN>

R</SPAN>

S</SPAN>

1</SPAN>

01-Sep</SPAN>

02-Sep</SPAN>

03-Sep</SPAN>

04-Sep</SPAN>

05-Sep</SPAN>

07-Sep</SPAN>

08-Sep</SPAN>

09-Sep</SPAN>

10-Sep</SPAN>

11-Sep</SPAN>

12-Sep</SPAN>

13-Sep</SPAN>

14-Sep</SPAN>

15-Sep</SPAN>

16-Sep</SPAN>

17-Sep</SPAN>

18-Sep</SPAN>

19-Sep</SPAN>

20-Sep</SPAN>

2</SPAN>

1</SPAN>

1</SPAN>

108</SPAN>

107</SPAN>

90</SPAN>

87</SPAN>

3</SPAN>

2</SPAN>

113</SPAN>

135</SPAN>

80</SPAN>

81</SPAN>

60</SPAN>

0</SPAN>

3</SPAN>

114</SPAN>

100</SPAN>

104</SPAN>

87</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=2><COL span=4><COL span=2><COL span=5><COL span=3><COL></COLGROUP>

 

[Akashwani]

Hi Gerard,

Does this do what you require?...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	01-Sep	02-Sep	03-Sep	04-Sep	05-Sep	07-Sep	08-Sep	09-Sep	10-Sep	11-Sep	12-Sep	13-Sep	14-Sep	15-Sep	16-Sep	17-Sep	18-Sep	19-Sep	20-Sep
2	1	1	108	107	90	87	3	2	113	135	80	81	60	0	3	114	100	104	87
3
4	Average	80.35714286		1	Sun
5	Total	1125		2	Mon
6	Count	14		3	Tue
7				4	Wed
8	Check	80.35714286		5	Thu
9				6	Fri
10				7	Sat
11
Sheet7

You may need to change the 2 and 6 to 1 and 5.
I hope that helps.

Ak

 

[GMD1975]

Hi, Many thanks, thats spot on

Hi Gerard,

Does this do what you require?...

Sheet7

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	01-Sep	02-Sep	03-Sep	04-Sep	05-Sep	07-Sep	08-Sep	09-Sep	10-Sep	11-Sep	12-Sep	13-Sep	14-Sep	15-Sep	16-Sep	17-Sep	18-Sep	19-Sep	20-Sep
2	1	1	108	107	90	87	3	2	113	135	80	81	60	0	3	114	100	104	87
3
4	Average	80.35714286		1	Sun
5	Total	1125		2	Mon
6	Count	14		3	Tue
7				4	Wed
8	Check	80.35714286		5	Thu
9				6	Fri
10				7	Sat
11

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 58px"><COL style="WIDTH: 84px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 49px"><COL style="WIDTH: 30px"><COL style="WIDTH: 36px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula B4 =SUMPRODUCT(--(WEEKDAY($A$1:$S$1)>=2)*(WEEKDAY($A$1:$S$1)<=6)*$A$2:$S$2)/SUMPRODUCT(--(WEEKDAY($A$1:$S$1)>=2)*(WEEKDAY($A$1:$S$1)<=6)*1) B5 =SUMPRODUCT(--(WEEKDAY($A$1:$S$1)>=2)*(WEEKDAY($A$1:$S$1)<=6)*$A$2:$S$2) B6 =SUMPRODUCT(--(WEEKDAY($A$1:$S$1)>=2)*(WEEKDAY($A$1:$S$1)<=6)) B8 =B5/B6 <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

You may need to change the 2 and 6 to 1 and 5.
I hope that helps.

Ak

 

[Akashwani]

Hi Gerard,

I'm pleased it worked for you and thanks for the feedback.

Ak