Pivot Table Average

[Nikesh Valji]

Hey everyone,

I have a pivot table with billable hours worked per agent per day.

Agents work a different number of days throughout the month; however I need to calculate the cumulative running average number of hours worked per day and then rank the top agents who have worked the most amount of billable hours.

Any ideas of how i can go about doing this?

I have attached a sample file which shows a few agents and their working hours by day; some of which have 15 days, and some have 17 days.

As always, any help from the community is appreciated.

Thanks,
Nik

 

[Chihiro]

Version of Excel?

Also what's your expected result? I assume you are after avg. WFM per agent?
If you don't have PowerPivot. You'll need to add helper column to data table.
Ex: [Count] =([@[WFM hours]]>0)*1

Then calculated field.
='WFM hours'/Count

 

[Nikesh Valji]

Hi Chihiro,

I am using Excel 2010 so needed the helper column which worked fine. I would like to learn how to apply it in the powerpivot scenario too. But that can be for another day.

Thanks for the reply!

N