Average occurance in a day?

[ianripping]

This problem is too hard to explain in words so the file is attactched.
Would appreciate any help!

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=466139

 

[Ken Wright]

This problem is too hard to explain in words so the file is attactched.

Which takes out 75% of your target audience straight away. Now what is hard
about the following:-

Name Date
A 02-Mar-06
B 02-Mar-06
B 02-Mar-06
B 02-Mar-06
B 02-Mar-06
A 04-Mar-06
A 04-Mar-06
E 05-Mar-06
D 05-Mar-06
D 05-Mar-06
E 06-Mar-06


Col A list of salesman names
Col B list of dates - each date represents a referral for that salesman on that
day
Data not in any particular order

Have already determined the salesman with the most referrals, but would now like
to know his average number of referrals.
(Oh by the way, you listed A as having the most referrals in your file - why - B
has the same!!)

BUT, you also need to be clear about what it is you are looking for, so tell us
what exact average are you looking for:-

Average for that salesman across all the days listed?
Average for that salesman across every day from the earliest day to the latest
day, including all days between?
Average for that salesman across all days that only he himself has a referral
on?

Are you including/excluding weekends/holidays etc?

Given that you are asking someone to give up their personal time to help you out
on an answer for what appears to be a work-related issue, the very least you can
do is to take some time to try and state the case as clearly as possible, and
help out the people you are looking to for a solution.

 

[ianripping]

ok sorry i meant to ask for: -

Average for that salesman across all days that only he himself has a
referral on?

Calculation will be:--


Day 1 - Average
Day 2 - Average

etc

Then an average of all these averages.
Does this help?

not including holidays etc

 

[Ken Wright]

If you are looking for a table of data in that manner, then I would honestly
suggest you take a look at Pivot tables. Everything you have asked can be done
with formulas, but, given that dates will change (Number of dates as well as
actual dates), and you want values for each individual date, you will have a
mountain of work ahead of to set this up each time, whreas a Pivot Table will do
this in a minute or two, eg

Select your name and date data, including the headers, and then do

Data / PivotTable & PivotChart report, then hit Next / Next / Finish

Now drag NAME to the left of the table and DATE to the middle. This replicates
the table you already have. Now also drag NAME between the two sets of data you
have, right clcik on any the totals, choose field settings and change subtotals
from automatic to none - this gives you the number of refererals for each day
they had one:-

Count of Date
Name Date Total
A 02-Mar-06 3
04-Mar-06 1
B 02-Mar-06 4
C 02-Mar-06 2
04-Mar-06 1
D 02-Mar-06 2
E 02-Mar-06 2
F 04-Mar-06 2
Grand Total 17

and there loads of other easy reports you can generate in the same way

A good intro to these can be found here;-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Ken Wright]

If the PivotTable option doesn't appeal, then I'm thinking you would need to set
up a table with every date in it down the left, and every salesman across the
Top, eg:-


A B C D E F
1 SmA SmB SmC SmD SmE
2 Date1
3 NextDay
4 NextDay
5 NextDay
6 NextDay
etc

Assume that your Saleman name range is K1:K100 and the dates are in L1:L100

then in say cell B2 above, you could use a formula such as :-

=SUMPRODUCT(($K$1:$K$100=$A2)*($L$1:$L$100=B$1))

Then just copy down and across. This will give you a count for each salesman
for each day

If you don't like the 0s then make that:-

=IF(SUMPRODUCT(($K$1:$K$100=$A2)*($L$1:$L$100=B$1))=0,"",SUMPRODUCT(($K$1:$K$100
=$A2)*($L$1:$L$100=B$1)))

 

[Ken Wright]

Note - the names in row 1 of that table would have to be exactly the same as
they appear in col L

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

If the PivotTable option doesn't appeal, then I'm thinking you would need to set
up a table with every date in it down the left, and every salesman across the
Top, eg:-


A B C D E F
1 SmA SmB SmC SmD SmE
2 Date1
3 NextDay
4 NextDay
5 NextDay
6 NextDay
etc

Assume that your Saleman name range is K1:K100 and the dates are in L1:L100

then in say cell B2 above, you could use a formula such as :-

=SUMPRODUCT(($K$1:$K$100=$A2)*($L$1:$L$100=B$1))

Then just copy down and across. This will give you a count for each salesman
for each day

If you don't like the 0s then make that:-

=IF(SUMPRODUCT(($K$1:$K$100=$A2)*($L$1:$L$100=B$1))=0,"",SUMPRODUCT(($K$1:$K$100
=$A2)*($L$1:$L$100=B$1)))