Average number of times a name occurs during a week in a pivot table

[smakatura]

We ship many trucks for numerous customers each day. Each truck gets a ticket number. so one ticket equals 1 truck. so each day I get something like this (but with about 200 tickets per day)

Date Ticket Company Weeknumber
5/1 1 Mary's Trucking 18
5/1 2 Laura's trucking 18
5/1 3 Carrie's trucking 18
5/1 4 Laura's trucking 18

I want a pivot table that shows the trucking company, week number, and average number of times per day that that name showed up...so if Mary's trucking had 12 trucks on 5/1 and 15 on 5/2 and 13 on 5/3 it would say that for week 18 Mary's trucking had an average of 14.3 trucks per day for that week

Sample pivot style desired
Company Week Number Avg trucks per day
Mary's trucking 16 12
17 16.2
18 14.3
Carrie's Trucking 16 22
17 22
18 20.4
......


I can get the count of all the trucks from the week (so 84 for week 16 of Mary), but not the average.

(I added a second company row so that one company name could be in the "Row labels" and the other could be in the "values" section)

I tried a calculated field but not sure I am putting in the correct formula or doing it correctly.

 

[ChrisR]

Can you not use a the COUNTIF formula to do this in each row and then just summarise that with a pivot? That would be the easiest way?

=COUNTIF($B$1:$B$1000,$B1)

Change B1:B1000 to the list of trucking names..............Cell B1 would look at the trucking name on that row and count how many times it appears in the list. You'd then just copy this down........

I think your original post may have been a bit misleading...........As I think you're looking for a count rather than an average.

 

[smakatura]

Actually I do want an average of the number countif results.

I will try to clarify...but not sure of the right words.

lets say of the 200 lines that laur appears the number of times listed below for each day of the week
- Monday 25 times
- Tuesday 10 times
- Wednesday 15 times
- Thursday 25 times
- Friday 20 times
- Saturday 17 times
- Sunday 23 times

there will always be 7 entries per week when counting. Week number is also filled in on each ofthe lines based on the date so no extra calculation ther and easy to put in a pivot table. so.... I do a pivot table that shows the name I am looking for week number and date and then the count of how many times that name shows up for that day

Name Week Day Count
Laura 18 Monday 25
Tuesday 10
Wednesdy 15
Thursday 25
Friday 20
Saturday 17
Sunday 23
Laura total 135

If I just remove the day and leave the week number the count will read 135... I want to divide the count by 7 or take the average number of counts that occured each day for that week.

so yes I understand the count function but really want I want to do is a calculated filed that says something like = =COUNTIF($B$1:$B$1000,$B1)/7. but the counif formula would be the count of the column as the pivot table calculates for me.

I am trying to make the calculated field part of the pivot table take the total count for that week (135) and divide it by 7.

I am looking to do this all within one pivot table that just ahs the name, week number and then average number of times per day that the name showed up (ie the count per day/7)

so yes looking for average (of the count)

 

[smakatura]

and puting the countif as an extra column and then averaging by the week number that is already a column will not work bc all days are on the same spreadsheet (a main continuous database) so the countif woudl pick up on all of them not just the one day so instead of having a count of 25 it could say 1000 bc of the fact that 3 months of data are on the same sheet.

I am not separating the days to be able to do it...that is what pivot tables are for

so somehow need to figure out how to get the pivot table to do it for me.