Average if weekday

[bhorvati]

Need formula to average numbers for weekdays Mon-Fri.
Need formula to average number for weekday Sat-Sun.

Column A = Dates
Column B = Data

 

[adam087]

Does this work for you?

Excel Workbook
	A	B
1	Date	Value
2	20-Jan-12	32
3	21-Jan-12	57
4	24-Jan-12	9
5	25-Jan-12	47
6	27-Jan-12	51
7	27-Jan-12	13
8	27-Jan-12	88
9	28-Jan-12	86
10	30-Jan-12	35
11	31-Jan-12	53
12	01-Feb-12	76
13	02-Feb-12	62
14	04-Feb-12	9
15	09-Feb-12	64
16	09-Feb-12	80
17	10-Feb-12	57
18	11-Feb-12	85
19	11-Feb-12	24
20	12-Feb-12	35
21	16-Feb-12	15
22
23	Weekdays	48.7
24	Weekends	49.3
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


Regards
Adam

 

[Asala42]

Sumproduct approach (if you want to avoid CSE formulas)

=SUMPRODUCT(--(WEEKDAY($A$2:$A$20,2)<6),$B$2:$B$20)/SUMPRODUCT(--(WEEKDAY($A$2:$A$20,2)<6))

=SUMPRODUCT(--(WEEKDAY($A$2:$A$20,2)>5),$B$2:$B$20)/SUMPRODUCT(--(WEEKDAY($A$2:$A$20,2)>5))

 

[bhorvati]

Brilliant. Thank you both.

 

[barry houdini]

You can also use AVERAGE function, for example

=AVERAGE(IF(WEEKDAY(A2:A100,2)< 6,B2:B100))

confirmed with CTRL+SHIFT+ENTER