Results 1 to 10 of 10

Thread: Average of monthly count

  1. #1

    Average of monthly count



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'm trying to recreate a table using a pivot table but am struggling with one final element. The normal table shows the number of reports written by analyst per month for each year, with a total and average for the year.
    2012
    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Average
    Analyst1 0 0 7 3 3 3 4 4 4 6 5 0 39 4
    Analyst2 13 21 32 17 19 14 21 19 22 33 23 0 234 20
    Analyst3 9 18 21 12 15 12 16 17 18 23 22 0 183 15
    Analyst4 6 12 16 8 13 8 12 13 14 19 13 0 134 11
    Total 28 51 76 40 50 37 53 53 58 81 63 0 590 12

    I have recreated this whole table successfully apart from the last column - the average for the year. In the normal table it is an average formula of the Jan-Dec values but I can't work out how to do this in the pivot table - any pointers?

    Thanks,

    Emmy

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    The easiest way is to add a helper column to the table to get the average: e.g =AVERAGE(B3:M3) copied down.


  3. #3
    Hi,
    Thanks for looking at this.
    I'm not sure if I've misunderstood you but I'm looking to get rid of the normal table altogether so that I have the underlying data and the pivot table only.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Isn't the "normal table" and the "underlying data" the same thing?

    I was suggesting you add the formula to the right side of the pivot table and reference the pivot table rows.


  5. #5
    No, the underlying data and the normal table aren't the same - the underlying data is stored in this format:

    Date Company Writer
    03/01/2012 Company 1 Analyst3
    05/01/2012 Company 2 Analyst3
    17/01/2012 Company 3 Analyst2
    19/01/2012 Company 4 Analyst2
    20/01/2012 Company 5 Analyst3
    23/01/2012 Company 6 Analyst2
    23/01/2012 Company 7 Analyst3
    23/01/2012 Company 8 Analyst4
    23/01/2012 Company 9 Analyst2
    23/01/2012 Company 10 Analyst2
    25/01/2012 Company 11 Analyst2
    25/01/2012 Company 12 Analyst2
    25/01/2012 Company 13 Analyst4
    25/01/2012 Company 14 Analyst4
    26/01/2012 Company 15 Analyst4
    26/01/2012 Company 16 Analyst3
    26/01/2012 Company 17 Analyst4
    27/01/2012 Company 18 Analyst3
    27/01/2012 Company 19 Analyst3
    27/01/2012 Company 20 Analyst2

    The normal table was using countif formulas to get the number of reports by analyst for each month.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Ok, so you are creating the PT from the Underlying table. The Average() function is in the PT.


  7. #7
    So how would I get the average per month for each year?

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Post a sample workbook with fake data, and show a sample of expected results.


  9. #9
    I can't seem to upload the file (even though it's only 53KB). I've created a pivot table which exactly mirrors my table but I can't get the average column to work as I can only average the underlying data and not the average of the number of reports per month which are in the pivot table.

  10. #10
    Any ideas anyone? My pivot tables show the count of reports per month for each year - is there anyway I can get the average of these in a subtotal column for each year?

Posting Permissions

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