Calculating a team average (excluding absents) on a pivot table
I need to calculate in different weeks a sort of team average that is given by this formula "Total of sales during the week" / "Number of presences" (count of presences, i.e. in a team of 5 if nobody was sick/vacations that would be 5x5=25). My problem is getting into the pivot table the presences since some workers sell for 3 different products ( I though of adding a column with 1s, but in this case some would get 3 presences in a single day).
Any ideas? This is for a huge data base, and I would like to do this by a pivot table. I also need the number of presences for other ratios
Last edited by campelliann; 09-22-2015 at 03:50 PM.
Re: Calculating a team average (excluding absents) on a pivot table
Okay, what constitutes a team? All people selling product A is team 1, product B is team 2, etc?
How do you determine an absence versus just didn't sell that product on that day?
Attached is your data with a second pivot table which might be more enlightening. The days can be grouped to show weeks. It would help if you showed an example (manually filled in) of what you were looking for.
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
: How to use code tags, mark a thread solved, and keep yourself out of trouble
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By am37 in forum Excel General
Last Post: 03-05-2015, 05:17 PM
By Dipwind in forum Excel General
Last Post: 12-17-2010, 09:50 PM
By Jack10063 in forum Excel General
Last Post: 10-19-2009, 09:00 AM
By KeiranMac in forum Excel General
Last Post: 09-09-2009, 08:18 AM
By Dien in forum Excel Formulas & Functions
Last Post: 07-07-2008, 02:15 AM
By tsaravan in forum Excel Formulas & Functions
Last Post: 08-31-2006, 06:24 AM
By shysong in forum Excel General
Last Post: 05-08-2006, 04:55 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1