Working average

mikenz1983

New Member
Joined
Oct 2, 2014
Messages
29
Hey Guys

I am drawing a blank here and i know i have done this before, but for the life of me cant remember
i have a worksheet that holds records of all the calls i have received into the business and the different departments, the general format looks like this

Date Day Duration
14/06/2021 Mon 0:02:05
14/06/2021 Mon 0:03:59
14/06/2021 Mon 0:06:09
14/06/2021 Mon 0:06:04

this worksheet has about 25k entries so goes through the other days of the week also and lists the departments that may have recieved the calls
i did a pivot on the data to see if i could see the "average" call volumes received on a certain day of the week, but its returning an error.

so my question is, how do i see the average of calls delivered per day by department, ideally that will be picked up in a pivot table

Thanks
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS
you can just use a pivot table and choose average for the duration

added to dropbox, but will only be online a few days

Book1-etaf.xlsx
ABCDEFG
1DateDepartmentDurationRow LabelsAverage of Duration
26/14/21A0:02:056/14/2100:04:34
36/14/21B0:03:59A00:04:07
46/14/21A0:06:09B00:05:01
56/14/21B0:06:046/15/2100:05:15
66/15/21A0:02:24A00:04:44
76/15/21B0:04:35B00:05:47
86/15/21A0:07:04Grand Total00:04:55
96/15/21B0:06:59
Sheet1
Cell Formulas
RangeFormula
C6:C9C6=C2*1.15
 

mikenz1983

New Member
Joined
Oct 2, 2014
Messages
29
you can just use a pivot table and choose average for the duration

added to dropbox, but will only be online a few days

Book1-etaf.xlsx
ABCDEFG
1DateDepartmentDurationRow LabelsAverage of Duration
26/14/21A0:02:056/14/2100:04:34
36/14/21B0:03:59A00:04:07
46/14/21A0:06:09B00:05:01
56/14/21B0:06:046/15/2100:05:15
66/15/21A0:02:24A00:04:44
76/15/21B0:04:35B00:05:47
86/15/21A0:07:04Grand Total00:04:55
96/15/21B0:06:59
Sheet1
Cell Formulas
RangeFormula
C6:C9C6=C2*1.15

Thanks so much for this
i "think" i maybe misunderstood

the average of the duration is no problem i can do that, its more that i need to know how many "actual calls" on average a department will receive on a given day
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS
will a count do ?
OR are you looking for the day of week


days of week, just have a day column and change to text
TEXT(day(a2),"DDDD")

Book1-etaf-1.xlsx
ABCDEFGHIJKL
1DateDAYDepartmentDurationDAYDepartmentAverage of DurationCount of Duration
26/14/21SaturdayA0:02:05SaturdayA00:04:072
36/14/21SaturdayB0:03:59B00:05:012
46/14/21SaturdayA0:06:09Saturday Total00:04:344
56/14/21SaturdayB0:06:04SundayA00:05:054
66/15/21SundayA0:02:24B00:06:134
76/15/21SundayB0:04:35Sunday Total00:05:398
86/15/21SundayA0:07:04Grand Total00:05:1712
96/15/21SundayB0:06:59
106/22/21SundayA0:02:45
116/22/21SundayB0:05:16
126/22/21SundayA0:08:08
136/22/21SundayB0:08:01
14
Sheet1
Cell Formulas
RangeFormula
B2:B13B2=TEXT(DAY(A2),"DDDD")
D6:D13D6=D2*1.15
 

mikenz1983

New Member
Joined
Oct 2, 2014
Messages
29
will a count do ?
OR are you looking for the day of week


days of week, just have a day column and change to text
TEXT(day(a2),"DDDD")

Book1-etaf-1.xlsx
ABCDEFGHIJKL
1DateDAYDepartmentDurationDAYDepartmentAverage of DurationCount of Duration
26/14/21SaturdayA0:02:05SaturdayA00:04:072
36/14/21SaturdayB0:03:59B00:05:012
46/14/21SaturdayA0:06:09Saturday Total00:04:344
56/14/21SaturdayB0:06:04SundayA00:05:054
66/15/21SundayA0:02:24B00:06:134
76/15/21SundayB0:04:35Sunday Total00:05:398
86/15/21SundayA0:07:04Grand Total00:05:1712
96/15/21SundayB0:06:59
106/22/21SundayA0:02:45
116/22/21SundayB0:05:16
126/22/21SundayA0:08:08
136/22/21SundayB0:08:01
14
Sheet1
Cell Formulas
RangeFormula
B2:B13B2=TEXT(DAY(A2),"DDDD")
D6:D13D6=D2*1.15
so essentially when i have a list of every day of the week since the beggining of the year, and about 400 records for each day ( resembling the amount of calls we recieved )
i am trying to get an average of a particular day

so should be something like

monday = 300 calls average ( for each monday )
Tuesday = xxxxx

i can get a count no worries, i started by adding a day column, the problem i hot is when im trying to average it out

and so on
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS
having same issue as your self, seems it can be done with powerquery which i dont have on my OSX version, some discussion on combining 2 pivot tables - but i cant get that to work either - Sorry
 

Forum statistics

Threads
1,141,049
Messages
5,703,942
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top