Average

[gregkendrick]

I need to average the sales of an employee. The part I'm having trouble with is weekends. If the employee works on a Sat or Sun I need the number of sales he made on those days to count but not for the day to count against the average.

This is how it works now.

8/1 1
8/2 2
8/3 0
8/4 1
8/5 0
8/6 1
Tot 5
Avg .83 But I would like it to show 1.0 because the number submitted on Sat should count but the day itself should not be added to the number of days worked.

Any help is greatly appreciated.

[daddylonglegs]

If dates are in A2:A100 and sales in B2:B100 then try this for average

=SUM(B2:B100)/SUMPRODUCT((WEEKDAY(A2:A100,2)<6)+0)

[gregkendrick]

This doens't seem to be working for me. The average showing is much lower than it should be. Here is an example of what the spreadsheet looks like. The PRPD, Per rep per day, should be over one since there are 10 sales in only 7 working days. I changed the formula to only average A2:A32 and the same for column B since those are the only days needed.
1-Aug 1
2-Aug 2
3-Aug 3
4-Aug 0
5-Aug 2
6-Aug 2
7-Aug 0
8-Aug 0
9-Aug 0
10-Aug
11-Aug
12-Aug
13-Aug
14-Aug
15-Aug
16-Aug
17-Aug
18-Aug
19-Aug
20-Aug
21-Aug
22-Aug
23-Aug
24-Aug
25-Aug
26-Aug
27-Aug
28-Aug
29-Aug
30-Aug
31-Aug
Totals 10
PRPD 0.434782609
PRPM 10

[gregkendrick]

I've also tried leaving A:33 - A:100 blank and the same in column B to no avail. Anyone have an idea what went wrong?