Working average

[mikenz1983]

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

 

[etaf]

you can just use a pivot table and choose average for the duration

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

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Book1-etaf.xlsx
	A	B	C	D	E	F	G
1	Date	Department	Duration		Row Labels	Average of Duration
2	6/14/21	A	0:02:05		6/14/21	00:04:34
3	6/14/21	B	0:03:59		A	00:04:07
4	6/14/21	A	0:06:09		B	00:05:01
5	6/14/21	B	0:06:04		6/15/21	00:05:15
6	6/15/21	A	0:02:24		A	00:04:44
7	6/15/21	B	0:04:35		B	00:05:47
8	6/15/21	A	0:07:04		Grand Total	00:04:55
9	6/15/21	B	0:06:59
Sheet1

Cell Formulas
Range	Formula
C6:C9	C6	=C2*1.15

 

[mikenz1983]

you can just use a pivot table and choose average for the duration

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

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Book1-etaf.xlsx
	A	B	C	D	E	F	G
1	Date	Department	Duration		Row Labels	Average of Duration
2	6/14/21	A	0:02:05		6/14/21	00:04:34
3	6/14/21	B	0:03:59		A	00:04:07
4	6/14/21	A	0:06:09		B	00:05:01
5	6/14/21	B	0:06:04		6/15/21	00:05:15
6	6/15/21	A	0:02:24		A	00:04:44
7	6/15/21	B	0:04:35		B	00:05:47
8	6/15/21	A	0:07:04		Grand Total	00:04:55
9	6/15/21	B	0:06:59
Sheet1

Cell Formulas
Range	Formula
C6:C9	C6	=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]

will a count do ?
OR are you looking for the day of week

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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

Book1-etaf-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	DAY	Department	Duration				DAY	Department	Average of Duration	Count of Duration
2	6/14/21	Saturday	A	0:02:05				Saturday	A	00:04:07	2
3	6/14/21	Saturday	B	0:03:59					B	00:05:01	2
4	6/14/21	Saturday	A	0:06:09				Saturday Total		00:04:34	4
5	6/14/21	Saturday	B	0:06:04				Sunday	A	00:05:05	4
6	6/15/21	Sunday	A	0:02:24					B	00:06:13	4
7	6/15/21	Sunday	B	0:04:35				Sunday Total		00:05:39	8
8	6/15/21	Sunday	A	0:07:04				Grand Total		00:05:17	12
9	6/15/21	Sunday	B	0:06:59
10	6/22/21	Sunday	A	0:02:45
11	6/22/21	Sunday	B	0:05:16
12	6/22/21	Sunday	A	0:08:08
13	6/22/21	Sunday	B	0:08:01
14
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=TEXT(DAY(A2),"DDDD")
D6:D13	D6	=D2*1.15

 

[mikenz1983]

will a count do ?
OR are you looking for the day of week

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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

Book1-etaf-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	DAY	Department	Duration				DAY	Department	Average of Duration	Count of Duration
2	6/14/21	Saturday	A	0:02:05				Saturday	A	00:04:07	2
3	6/14/21	Saturday	B	0:03:59					B	00:05:01	2
4	6/14/21	Saturday	A	0:06:09				Saturday Total		00:04:34	4
5	6/14/21	Saturday	B	0:06:04				Sunday	A	00:05:05	4
6	6/15/21	Sunday	A	0:02:24					B	00:06:13	4
7	6/15/21	Sunday	B	0:04:35				Sunday Total		00:05:39	8
8	6/15/21	Sunday	A	0:07:04				Grand Total		00:05:17	12
9	6/15/21	Sunday	B	0:06:59
10	6/22/21	Sunday	A	0:02:45
11	6/22/21	Sunday	B	0:05:16
12	6/22/21	Sunday	A	0:08:08
13	6/22/21	Sunday	B	0:08:01
14
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=TEXT(DAY(A2),"DDDD")
D6:D13	D6	=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]

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