Pivot table calculation question

jb00976

New Member
Joined
Oct 23, 2017
Messages
26
I have a pivot table that looks like this

Row Label Appointment Email Phone Call
Joe 148 302 86
Kate 129 88 303
Ron 157 398 157
Paul 125 221 47

I want to have a column between each of Appointment, Email and Phone call that divides the count for Appointment Email or Phone Call by 48 (number of weeks in the period I am looking at).

My fields are setup as follows
Filters - none
Columns - "Activity Type"
Rows "Modified By"
Values "Count of Regarding"

I think I need a calculated field but, can't figure out the formula. Please help

Thank You
Jen
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

XavierXcel

New Member
Joined
Aug 23, 2019
Messages
2
You need to make 3 calculated fields each with the value field divided by 48.

If you are trying to divide the count of the value field by 48. easy way is to add 3 columns in your data set using a If function to return zero or one. and use those fields in the pivot table calculated field function and divide by 48.

I dont think count as a function works well in the calculated field. (ie. =count(field)/48)

you can also make your pivot table a dynamic table by "offset" and "name" function so you can add more columns in the future without the need of creating new pivot table.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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