Excel Percentage formulas and Pivot table chart displays

kellym9236 · Feb 16, 2023

Hello everyone,

I am new to this so please bare with me and I apologize in advance for my lack of knowledge, I have attached a sheet here and what I've been trying to do is calculate occupancy rate
by using a formula I found online sum of handle time (O2) (R2) (S2) (T2) to see my formulas I don't know if they are even right cause i can't seem to create a pivotal chart or add a simple chart and show the occupancy rate percentages or the utilization rate percentages in a column or line chart, and also add the columns to get an avaerage percetgaes of all the agents, any help would be greatly appreciated My boss has tasked me to figure this out and I don't know where to start troubleshooting what I'm doing

thank you in advance for any help or direction and most importantly your time

kelly

Test1.xlsx

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

ACW Time

Agent1

Extn In Time

Extn Out Time

Avail Time

AUX Time

ACD Calls

Extn In Calls

Extn Out Calls

Agent12

Avg Handle Time

Month

Day

utilization

handle+Aval

Occupancy

2

1:00:47

:08:41

:00:00

3:37:03

0:35:21

27

0

Agent1

3

1:16:32

:07:26

:00:00

:07:26

3:07:54

0:37:21

26

0

3

Agent1

4

1:23:31

:06:53

:00:00

:04:05

2:51:34

0:25:12

21

0

5

Agent1

5

1:57:55

:06:35

:00:00

:20:00

2:15:22

0:29:18

19

0

10

Agent1

6

1:59:53

:06:32

:00:00

:49:49

1:52:10

1:04:36

22

0

5

Agent1

7

2:02:44

:08:26

:00:00

:02:19

1:10:18

0:31:47

35

0

1

Agent1

8

1:18:49

:08:03

:00:00

:03:29

2:45:14

0:33:08

24

0

2

Agent1

9

1:13:39

:07:38

:00:00

:01:28

3:07:33

0:27:00

23

0

1

Agent1

10

0:24:53

:04:13

:00:00

4:28:10

0:11:22

16

0

Agent1

11

1:12:58

:08:47

:00:00

:01:15

4:01:24

1:04:51

15

0

1

Agent1

12

2:53:05

:09:20

:00:00

:11:16

1:28:06

0:36:21

30

0

5

Agent1

13

1:21:16

:10:09

:00:00

:04:24

3:51:19

0:35:31

29

0

4

Agent1

14

0:28:50

:04:14

:00:00

:01:28

3:28:32

0:40:32

10

0

2

Agent1

15

0:30:32

:05:39

:00:00

:13:35

4:24:22

0:38:59

15

0

4

Agent1

16

0:56:10

:09:39

:00:00

:00:03

3:34:55

0:31:49

25

0

1

Agent1

17

0:52:13

:08:28

:00:00

:01:11

3:40:05

0:22:15

21

0

2

Agent1

18

0:49:05

:07:55

:00:00

:05:00

4:02:19

0:25:54

20

0

2

Agent1

19

1:09:46

:07:56

:00:00

:09:19

4:46:58

0:35:49

18

0

2

Agent1

20

1:00:15

:09:57

:00:00

:08:11

3:56:11

0:33:32

28

0

6

Agent1

21

0:55:12

:08:26

:00:00

3:10:22

0:29:22

25

0

Agent1

22

0:46:16

:08:23

:00:00

:04:55

3:59:15

0:29:49

25

0

1

Agent1

23

1:21:47

:10:30

:00:00

:01:03

4:11:59

0:41:13

23

0

1

Agent1

24

0:57:51

:06:37

:00:00

2:35:11

0:11:32

35

0

Agent1

25

0:39:39

:06:58

:00:00

:05:03

3:17:35

0:30:36

23

0

3

Agent1

26

0:29:20

:08:01

:00:00

:13:47

4:19:39

0:30:21

20

0

4

Agent1

27

0:47:30

:07:54

:00:00

:10:58

4:45:27

033:02

22

0

3

Agent1

28

0:58:24

:06:19

:00:00

:28:31

4:19:51

0:42:58

18

0

3

Agent1

29

1:05:39

:04:22

:00:00

2:33:26

0:21:08

6

0

Agent1

30

0:37:30

:07:13

:00:00

:12:59

5:23:22

0:25:04

19

0

3

Agent1

31

0:36:51

:08:21

:00:00

:04:36

4:44:38

0:36:51

23

0

2

Agent1

32

0:42:30

:08:09

:00:00

4:29:14

0:31:51

24

0

Agent1

33

1:22:56

:05:08

:00:00

:01:17

4:11:48

0:23:58

19

0

2

Agent1

34

0:43:47

:08:23

:00:00

:00:31

4:07:21

0:42:48

28

0

1

Agent1

35

1:15:30

:07:47

:00:00

:24:01

3:54:01

0:38:16

25

0

6

Agent1

36

0:43:24

:06:50

:00:00

4:26:34

0:31:40

21

0

Agent1

37

0:57:00

:06:16

:00:00

4:27:06

0:35:20

21

0

Agent1

38

0:55:48

:06:14

:00:00

:20:26

3:34:44

0:51:12

15

0

1

Agent1

39

0:24:01

:03:58

:00:00

4:50:22

0:10:01

17

0

Agent1

40

0:50:02

:08:39

:00:00

:03:50

3:42:01

0:31:54

31

0

3

Agent1

41

0:38:47

:06:10

:00:00

:00:47

4:24:40

0:32:39

25

0

3

Agent1

42

0:32:08

:05:40

:00:00

:00:03

4:58:51

0:31:19

22

0

1

Agent1

43

1:21:35

:06:25

:00:00

4:19:10

0:30:27

19

0

Agent1

44

0:12:12

:01:48

:00:00

:01:58

5:04:24

0:12:51

8

0

2

Agent1

45

0:09:50

:01:27

:00:00

:09:54

5:37:34

0:51:20

6

0

2

Agent1

46

1:31:25

:07:37

:00:00

:19:25

1:47:53

0:23:22

36

0

3

Agent1

47

0:48:06

:07:37

:00:00

:12:23

4:06:38

0:29:55

26

0

12

Agent1

48

1:27:58

:06:32

:00:00

:38:33

3:46:14

0:32:41

21

0

6

Agent1

49

0:37:36

:04:52

:00:00

:41:24

4:48:07

1:10:01

17

0

5

Agent1

50

1:10:53

:05:35

:00:00

:14:12

4:35:52

0:42:34

16

0

4

Agent1

51

0:26:39

:03:59

:00:00

:02:17

4:18:27

0:15:18

18

0

Agent1

52

0:53:05

:08:28

:00:00

:02:23

4:13:51

0:30:58

27

0

4

Agent1

53

0:33:58

:04:59

:00:00

:05:37

4:47:30

0:30:43

16

0

4

Agent1

54

0:24:39

:04:30

:00:00

:01:11

5:40:50

0:25:05

15

0

1

Agent1

55

0:33:43

:04:54

:00:00

:04:12

4:35:38

0:05:54

26

0

2

Agent1

56

0:56:49

:06:05

:00:00

:03:06

3:43:34

0:26:51

21

0

1

Agent1

57

0:54:55

:05:50

:00:00

:12:20

4:44:20

0:45:57

17

0

3

Agent1

58

1:23:22

:05:34

:00:00

:19:03

4:31:40

0:34:10

20

0

2

Agent1

59

0:48:31

:05:32

:00:00

:21:33

3:45:12

0:49:04

17

0

4

Agent1

60

1:06:09

:06:05

:00:00

:12:33

4:25:10

0:41:53

19

0

3

Agent1

61

0:52:08

:07:44

:00:00

:00:07

3:19:42

0:30:38

32

0

3

Agent1

62

0:37:21

:06:08

:00:00

:06:56

4:45:04

0:27:33

21

0

3

Agent1

63

0:36:09

:05:50

:00:00

:32:56

3:56:20

0:50:04

23

0

6

Agent1

64

1:26:23

:04:30

:00:00

:09:50

4:27:47

0:27:55

20

0

7

Agent1

65

1:06:17

:03:33

:00:00

:23:33

3:48:26

0:05:48

19

0

3

Agent1

66

0:54:45

:07:13

:00:00

:53:12

2:45:17

1:18:06

34

0

9

Agent1

67

0:56:30

:05:41

:00:00

:21:53

3:44:32

0:46:33

25

0

5

Agent1

68

1:25:10

:06:32

:00:00

:05:18

3:36:53

0:24:46

23

0

6

Agent1

69

0:45:30

:05:29

:00:00

:04:36

3:34:15

0:24:45

18

0

6

Agent1

70

0:37:18

:04:37

:00:00

:12:06

4:34:16

0:38:00

22

0

6

Agent1

71

0:53:13

:06:01

:00:00

:09:58

4:39:02

0:30:38

26

0

5

Agent1

72

0:50:51

:03:48

:00:00

:14:20

4:29:00

0:27:18

17

0

2

Agent1

73

0:55:52

:03:39

:00:00

:21:24

4:40:23

0:38:42

17

0

5

Agent1

74

0:33:18

:04:02

:00:00

:03:40

5:49:19

0:04:24

18

0

2

Agent1

75

1:09:12

:06:12

:00:00

:09:43

3:09:51

0:30:23

29

0

3

Agent1

76

0:46:29

:04:42

:00:00

:20:28

4:28:14

0:39:21

21

0

3

Agent1

77

1:18:50

:05:17

:00:00

:28:15

3:04:10

0:43:48

25

0

11

Agent1

78

0:46:35

:04:54

:00:00

:06:46

4:06:26

0:31:30

24

0

2

Agent1

79

0:50:44

:04:45

:00:00

:07:27

4:38:37

0:44:25

18

0

3

Agent1

80

0:33:18

:03:58

:00:00

:01:49

6:05:10

0:09:24

21

0

1

Agent1

81

1:00:15

:06:13

:00:00

:11:18

3:08:30

0:40:06

31

0

6

Agent1

82

0:52:17

:04:56

:00:00

:15:21

3:49:36

0:43:18

25

0

12

Agent1

83

0:52:06

:05:51

:00:00

:31:34

3:49:55

0:51:29

29

0

8

Agent1

84

0:50:03

:05:54

:00:00

:01:33

3:34:44

0:26:37

27

0

1

Agent1

85

0:42:16

:03:59

:00:00

:14:43

3:50:53

0:16:23

20

0

4

Agent1

Agent Data

Cell Formulas
Range	Formula
O2:O85	O2	=C2+D2+E2-I2/K2
P2:P85	P2	=TEXT(A2, "MMM")
Q2:Q85	Q2	=TEXT(A2, "DDD")
R2:R85	R2	=E2+D2/C2
S2:S85	S2	=O2+I2/O2
T2:T85	T2	=O2/S2

kellym9236 · Apr 10, 2023

I like that you made the occpancy time, how did you do that? that may work better, what I was trying to do is When I create the pivoit table the Occupancy rate doesn't show in percentages, I'm trying to figure out how to get the Occupancy to display as a percentage in a chart, for example in the original data sheet the Row 2 column R displays 38.78% but when I create the pivota table the same valuse displays as 0.387830431 so is the formula I'm using to calculate Occupancy wrong? I'm using (ACD Time+ACW Time) Divided by Staffed Time, But maybe showing the time for Occupancy and the percentage may work better, would be nice to have both, how did you covert to Time for Occupancy? Thank you for all your Help!!

awoohaw · Apr 10, 2023

@kellym9236 , 38.78% and 0.387830431 are the same number. It is just that one is in the PERCENTAGE (rounded to 2 decimals) and one is problably in the general format. In your pivot table, go to "Value Field Settings" (right click any value in the column you want to format) and click number format in the dialog box that pops up.

regarding the pct vs decimal format, you can see that the same formula is in the two Utilization Columns in the table:

mr excel questions 22.xlsm

A

B

C

D

E

S

T

U

V

W

1

Estimation %:

0.8

2

3

Date

Year

Staffed Time

ACD Time

ACW Time

Utilization (Decimal)

Utilization (Pct)

handle+Aval

Occupancy2

Estimated Occupancy

4

3/1/2023

2023

07:27:01

02:03:54

00:49:28

5

3/2/2023

2023

07:37:43

02:20:48

01:10:04

6

3/3/2023

2023

06:24:25

01:03:54

00:49:23

7

3/6/2023

2023

07:47:47

03:25:10

01:41:28

8

3/7/2023

2023

07:21:36

01:47:34

01:04:58

9

3/8/2023

2023

07:37:00

02:41:14

01:02:07

10

3/9/2023

2023

07:21:01

01:47:06

01:14:56

11

3/10/2023

2023

06:38:23

01:48:38

01:15:33

12

13

Row Labels

Sum of Utilization (Decimal)

Sum of Utilization (Pct)

14

3/1/2023

0.387830431

15

3/10/2023

0.462326905

16

3/2/2023

0.460692568

17

3/3/2023

0.294688923

18

3/6/2023

0.655502904

19

3/7/2023

0.390700483

20

3/8/2023

0.488730853

21

3/9/2023

0.412758399

22

Grand Total

3.553231466

23

kellym9236

Cell Formulas
Range	Formula
S4:T11	S4	=([@[ACD Time]]+[@[ACW Time]])/[@[Staffed Time]]
U4:U11	U4	=O4+I4/O4
V4:V11	V4	=O4/U4
W4:W11	W4	=[@[Staffed Time]]*$V$1

Here is how to format the pivot columns:

kellym9236 · Apr 10, 2023

Thank you, That worked great , one more question If I can, how did you get the double zeros in the Agent Ring time, Extn In Time and Extn out time, In your screen show above you show

How did you get those 00: I couldn't figure that out in my sheet, Thank you

awoohaw · Apr 10, 2023

well, I don't know how you got a text value there in the first place, it seemed very weird to see things like " :06:13 " which is pretty strange format for a number (time is a number), and its a weird format for text. So, I just figured it was a number entered as a strange text representation of a time, and to convert that to a number I used the
TIMEVALUE function, which if you have a time in a text format, for instance 11:15:25 (as text, not number) you would convert it to text like this:

Excel Formula:

=TIMEVALUE("11:15:25")

But your text was a little tricky as you did not have the hours amount in front of the first colon. So I concatenated the text "00" in front of the text in the cell. So, if cell G3 had ":06:13", I assumed it was 6 minutes and 13 seconds.
So in another cell somewhere in the workbook I did this formula:

Excel Formula:

=TIMEVALUE("00" & G3)

which excel then converts to this:

Excel Formula:

TIMEVALUE("00:06:13")

Which then converts it into the value of that time.
I then copy and pasted as values into cell G3.
(the cell may not have been in the correct time format, it may have looked like this: = 0.00431713, which is the decimal value of 00:06:13.)
If it was just a decimal value, then you would need to format the cell as TIME.

kellym9236 · Apr 10, 2023

awoohaw said:
well, I don't know how you got a text value there in the first place, it seemed very weird to see things like " :06:13 " which is pretty strange format for a number (time is a number), and its a weird format for text. So, I just figured it was a number entered as a strange text representation of a time, and to convert that to a number I used the
TIMEVALUE function, which if you have a time in a text format, for instance 11:15:25 (as text, not number) you would convert it to text like this:

Excel Formula:

=TIMEVALUE("11:15:25")

But your text was a little tricky as you did not have the hours amount in front of the first colon. So I concatenated the text "00" in front of the text in the cell. So, if cell G3 had ":06:13", I assumed it was 6 minutes and 13 seconds.
So in another cell somewhere in the workbook I did this formula:

Excel Formula:

=TIMEVALUE("00" & G3)

which excel then converts to this:

Excel Formula:

TIMEVALUE("00:06:13")

Which then converts it into the value of that time.
I then copy and pasted as values into cell G3.
(the cell may not have been in the correct time format, it may have looked like this: = 0.00431713, which is the decimal value of 00:06:13.)
If it was just a decimal value, then you would need to format the cell as TIME.

You have been Amazing Awoohaw, Thank you so much for al of your help and Especailly your Time!! I can't Thank You Enough!!!

kellym9236 · Apr 10, 2023

awoohaw said:
well, I don't know how you got a text value there in the first place, it seemed very weird to see things like " :06:13 " which is pretty strange format for a number (time is a number), and its a weird format for text. So, I just figured it was a number entered as a strange text representation of a time, and to convert that to a number I used the
TIMEVALUE function, which if you have a time in a text format, for instance 11:15:25 (as text, not number) you would convert it to text like this:

Excel Formula:

=TIMEVALUE("11:15:25")

But your text was a little tricky as you did not have the hours amount in front of the first colon. So I concatenated the text "00" in front of the text in the cell. So, if cell G3 had ":06:13", I assumed it was 6 minutes and 13 seconds.
So in another cell somewhere in the workbook I did this formula:

Excel Formula:

=TIMEVALUE("00" & G3)

which excel then converts to this:

Excel Formula:

TIMEVALUE("00:06:13")

Which then converts it into the value of that time.
I then copy and pasted as values into cell G3.
(the cell may not have been in the correct time format, it may have looked like this: = 0.00431713, which is the decimal value of 00:06:13.)
If it was just a decimal value, then you would need to format the cell as TIME.

One last quetion, do you know where I can find a table of percentages vs time for example, I've seen online they mention Occupancy of 80-85% represents an agent will be on the phones for 51 minutes out of 60 minutes, what would the time value be for 30,40 50, 60 and 70 percent? do you know how I can calulate this? Thank you

awoohaw · Apr 11, 2023

I have that already done for you as well in cell V1. When you change the value there (.80 = 80%) to another number it will update the values in column V with that percentage.

kellym9236 · Apr 11, 2023

awoohaw said:
I have that already done for you as well in cell V1. When you change the value there (.80 = 80%) to another number it will update the values in column V with that percentage.

Hi Awoohaw, can you send me the sheet you have back so I can download it and take a look at what you've done Please!! Thank you

awoohaw · Apr 11, 2023

Is this what you need:

mr excel questions 23.xlsm

C

U

V

W

X

1

Estimation %:

0.8

2

3

Staffed Time

handle+Aval

Occupancy2

Estimated Occupancy

4

07:27:01

19:21:25

00:00:16

05:57:37

5

07:37:43

06:32:14

00:00:24

06:06:10

6

06:24:25

07:33:00

00:00:15

05:07:32

7

07:47:47

02:19:42

00:00:37

06:14:14

8

07:21:36

09:40:37

00:00:18

05:53:17

9

07:37:00

19:40:23

00:00:34

06:05:36

10

07:21:01

03:55:50

00:00:26

05:52:49

11

06:38:23

06:30:34

00:00:23

05:18:42

12

kellym9236

Cell Formulas
Range	Formula
U4:U11	U4	=O4+I4/O4
V4:V11	V4	=O4/U4
W4:W11	W4	=[@[Staffed Time]]*$V$1

kellym9236 · Apr 11, 2023

awoohaw said:
Is this what you need:

mr excel questions 23.xlsm
C U V W X
1 Estimation %: 0.8
2
3 Staffed Time handle+Aval Occupancy2 Estimated Occupancy
4 07:27:01 19:21:25 00:00:16 05:57:37
5 07:37:43 06:32:14 00:00:24 06:06:10
6 06:24:25 07:33:00 00:00:15 05:07:32
7 07:47:47 02:19:42 00:00:37 06:14:14
8 07:21:36 09:40:37 00:00:18 05:53:17
9 07:37:00 19:40:23 00:00:34 06:05:36
10 07:21:01 03:55:50 00:00:26 05:52:49
11 06:38:23 06:30:34 00:00:23 05:18:42
12
kellym9236
Cell Formulas
Range Formula
U4:U11 U4 =O4+I4/O4
V4:V11 V4 =O4/U4
W4:W11 W4 =[@[Staffed Time]]*$V$1

I think so? Thank you, I will try and compare to my sheet to figure out where those formulas go, what is kind of formula is this? =[@[Staffed Time]]*$V$1

Excel Percentage formulas and Pivot table chart displays

kellym9236

New Member

kellym9236

New Member

Excel Facts

awoohaw

Well-known Member

kellym9236

New Member

awoohaw

Well-known Member

kellym9236

New Member

kellym9236

New Member

awoohaw

Well-known Member

kellym9236

New Member

awoohaw

Well-known Member

kellym9236

New Member

Similar threads

Forum statistics

Share this page

Excel Percentage formulas and Pivot table chart displays

New Member

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock