+ Reply to Thread
Results 1 to 2 of 2

Calculating a team average (excluding absents) on a pivot table

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    windows 8

    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


    File attached
    Attached Files Attached Files
    Last edited by campelliann; 09-22-2015 at 03:50 PM.

  2. #2
    Forum Guru
    Join Date
    MS-Off Ver

    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.
    Attached Files Attached Files
    My 2?

    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)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-05-2015, 05:17 PM
  2. Pivot table average excluding zeros
    By Dipwind in forum Excel General
    Replies: 1
    Last Post: 12-17-2010, 09:50 PM
  3. Calculating weighted average subtotals in a pivot table
    By Jack10063 in forum Excel General
    Replies: 3
    Last Post: 10-19-2009, 09:00 AM
  4. Calculating an Average excluding Zeros across a workbook
    By KeiranMac in forum Excel General
    Replies: 8
    Last Post: 09-09-2009, 08:18 AM
  5. grouping months in pivot table and calculating average
    By Dien in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2008, 02:15 AM
  6. Calculating the Average of Every Nth Value, Excluding Zeros
    By tsaravan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2006, 06:24 AM
  7. [SOLVED] Calculating the average of a sum in Pivot Table
    By shysong in forum Excel General
    Replies: 2
    Last Post: 05-08-2006, 04:55 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1