Working out Percentage over a few hours | General Excel Questions & Answers

Working out Percentage over a few hours

James Niven

Member

Members

Forum Posts: 13

Member Since:
October 25, 2013

Offline

June 25, 2016 - 6:58 am

Hi All,

Love this forum, its great.

Please review the attachment below. This is the result I wish but not too sure how to get there.

I can create the whole pivot with little trouble, what I don't know how to (or if it can be done within a pivot) is the row with the Jan Avg for each month.

I want to know the percentage of tickets completed in the 12am to 6am, 6am to 6pm and 6pm to midnight period over to full month.

I hope this makes sense.

Thanks

James Niven

Member

Members

Forum Posts: 13

Member Since:
October 25, 2013

Offline

June 25, 2016 - 7:19 am

Here I have attached the actual data file and pivot table, I have taken out sensitive data.

The pivot table is on tab "WO Pivot - Austin" and the desired output is on tab "Final Outcome"

Thanks

Mynda Treacy

Admin

Forum Posts: 732

Member Since:
July 16, 2010

Offline

June 25, 2016 - 9:37 am

Hi James,

Thanks for sharing your file. It helps a lot.

Select a cell in your vales area, say D5 > right-click > show values as > % of row Total.

Is that what you're after?

Mynda

James Niven

Member

Members

Forum Posts: 13

Member Since:
October 25, 2013

Offline

June 25, 2016 - 10:58 am

Hi Mynda,

Thanks for the reply!

Actually no, I want to keep the values as they are shown, as per my example on "Final Outcome", I want to sum all the 3 values under 12am to 6am 259, 249 and 294 and on row 5, and then divide by the total 2792 on P3 to get the percentage of tickets for that time period.

I don't know if this is possible within a pivot table, but thought I would throw it out there.

Thanks

Mynda Treacy

Admin

Forum Posts: 732

Member Since:
July 16, 2010

Offline

June 25, 2016 - 3:28 pm

Hi James,

Thanks for clarifying. You need to add these groupings to your source data, then you can add 2 'count of DLWO#' to your values area and set one as Show Values As % of Row Total.

See example attached.

Mynda

James Niven

Member

Members

Forum Posts: 13

Member Since:
October 25, 2013

Offline

June 26, 2016 - 5:44 am

Hi Mynda,

Thanks for your attachment. I did not think about adding a time group column heading with an if formula to each row, but this did the trick.

I adjusted your end result on your attachment and it gives me what I want.