# Add calculated field to pivot table for daily average per month (XL2007)

#### AOB

##### Well-known Member
Hi guys,

I have some source data on which I need to pivot to get some numbers.

The source data shows individual transactions, each of which has, among other attributes, a date and an amount.

I need to create a pivot table which will group everything by month and show me the daily average of the amounts for each month.

Just to clarify, by daily average, I mean : sum for each individual date and get the average of all of those summations (not just the average of all the transactions) for each month

I've set up a basic pivot table with the date ('TransactionDate') as a row field, grouped by month and year. I have the amount ('Amount') as a data / value field using the sum function. So right now, I can see the total summed amount of transactions for each month.

Now I need to get the daily average - i.e., divide each of those amounts by the number of (working) days in their associated month. I want to have it as a field in the pivot table rather than a separate column so that it can be updated dynamically as page filters are changed etc.

I've added a calculated field which I call 'DailyAverage' and which uses the following formula :

Code:
``='Amount'/NETWORKDAYS(DATE(YEAR('TransactionDate'),MONTH('TransactionDate'),1),DATE(YEAR('TransactionDate'),MONTH('TransactionDate')+1,0))``

But it doesn't seem to be quite working?

As a check, I added two independent columns to the right of the pivot and calculated a) the NETWORKDAYS for the month in that row and b) the summed value for the month in that row (from the pivot) divided by the number of days calculated from a)

I've only got 4 months selected at the moment (Dec 2014 to March 2015) and my 'manually' calculated value only matches the 'pivot-calculated' value in 2 of those 4 months (January and March)

They are correct to the penny, which makes me believe I am not a million miles off having the correct formula? But the other two do not match - and I can't figure out why?

** Edit ** On further inspection, it seems the calculated field in the pivot table is evaluating the NETWORKDAYS piece to be 22 for every month, regardless of the actual row / month? Hence January & March are correct as they actually have 22 working days, whereas December should have 23 and February 20??

Can anybody help me?

Thanks!

Al

Last edited:

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Replies
0
Views
122
Replies
1
Views
597
Replies
6
Views
643
Replies
2
Views
550
Replies
2
Views
372

1,195,859
Messages
6,011,989
Members
441,661
Latest member
Pammie007

### 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.

### Which adblocker are you using?

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

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