INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

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

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:

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

There's probably a neat way to do that with a PT. I've always looked at PTs as a really great way to get quick aggregations, but have shied away from PTs for more comprehensive customized summarization. So I'd make my own pivot and summarize using SUMPRODUCT().

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

If you are ok with 30 days in every month calculated field will do it, add field formula referring to source field divided by 30 and pivot table will sum it up for each domain.

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

Is this ALWAYS going to be ONLY one month's worth of data?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Hi Skip, yes this will be run each month, for one month only.

Dang, ODBC Excel Driver Login Failed
'C:\Users\Skip\Downloads\MR78_-_........ is not a valid path

Many thanks,
D€$

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

See my instructions for correcting the path.

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close