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

[AOB]

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 :

='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