## Excel 2010 Pivot Table Values Total Column Divided By Days In month

## Excel 2010 Pivot Table Values Total Column Divided By Days In month

(OP)

Good afternoon, I was trying to check the utilisation of hospital wards ("NurseUnit") by just getting occupancy data for November 2016 (from a Business Objects report from our new supplier's Universe) and pivoting it up. It's just the Ward Name (NurseUnit) in the Row Labels and Count of Encounter ID in Values. I know that I can manually enter a formula in the next column to the right of the PT but as this results in a very specific formula:

I can't just copy this down to the end of the column.

I know that I can go down each row and type "= [left arrow] "/30", <CR> but I don't believe there's a way to add a Calculated Field that refers to PT Totals or Sub-Totals. Do I have any 'smart' options? I suppose I could just put:

as that's where the Total column starts.

## Quote:

=GETPIVOTDATA("MILLENNIUM ENCOUNTER SLICE ID",$G$3,"NurseUnit","Birthing Unit")/30

I can't just copy this down to the end of the column.

I know that I can go down each row and type "= [left arrow] "/30", <CR> but I don't believe there's a way to add a Calculated Field that refers to PT Totals or Sub-Totals. Do I have any 'smart' options? I suppose I could just put:

## Quote:

=H5/30

as that's where the Total column starts.

Many thanks,

D€$

## RE: Excel 2010 Pivot Table Values Total Column Divided By Days In month

I'd also calculate the exact days per month via

DATE(YEAR(x),MONTH(x)+1,0)

Or just

=H5/DATE(YEAR(x),MONTH(x)+1,0)

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel 2010 Pivot Table Values Total Column Divided By Days In month

For Skip's solution extract day from the last day in month date: =H5/

DAY(DATE(YEAR(x),MONTH(x)+1,0))combo

## RE: Excel 2010 Pivot Table Values Total Column Divided By Days In month

Many thanks,

D€$

## RE: Excel 2010 Pivot Table Values Total Column Divided By Days In month

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel 2010 Pivot Table Values Total Column Divided By Days In month

If so, check out this solution. This Query Table will refresh each time the workbook is opened.

You WILL need to "correct" the connection string via...

Right-click in ResultSet

Select Table > External Data Properties > Connection Properties (Upper right next to Name) > Definition > Connection String -- Make path corrections in TWO places.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel 2010 Pivot Table Values Total Column Divided By Days In month

Dang, ODBC Excel Driver Login Failed

Many thanks,

D€$

## RE: Excel 2010 Pivot Table Values Total Column Divided By Days In month

Of course, the path could automatically be corrected via event code.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}