+ Reply to Thread
Results 1 to 6 of 6

average calculation in pivot

  1. #1
    Registered User
    Join Date
    09-08-2020
    Location
    TR
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    22

    average calculation in pivot

    Hi guys,

    I have a headcount data. I groupped them according to dates (months/quarters and year) in pivot table. I have personnel numbers and dates. I want to see average personnel numbers for quarters and total, I dont want pivot to sum each month. I found that it can be done through pivot table-new measure but I could not write the correct formula for this. Can you please help? Thanks in advance.


    foto.JPG

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: average calculation in pivot

    Post a sample sheet ( see yellow banner) Thx

  3. #3
    Registered User
    Join Date
    09-08-2020
    Location
    TR
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    22

    Re: average calculation in pivot

    I've attached, thank you.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: average calculation in pivot

    In the sample sheet the value is always 20, so does not give any variation. What is different to selecting a number in your table and choosing value field settings, and then choosing average

  5. #5
    Registered User
    Join Date
    09-08-2020
    Location
    TR
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    22

    Re: average calculation in pivot

    20 is not value, it is personnel number. I want pivot to count personnel numbers instead of summing them and then it should calculate average personnel head counts which should be for example 17332 for the first quarter.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: average calculation in pivot

    See the attached pivot table with the result 17.332 for Q1.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. Want Pivot table's average to show average by number of month.
    By jp16 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-16-2019, 06:07 AM
  2. Replies: 2
    Last Post: 05-26-2014, 11:12 AM
  3. weighted average % calculation in pivot table
    By maymano in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-28-2013, 10:33 PM
  4. Replies: 1
    Last Post: 01-15-2013, 08:51 AM
  5. Three month average calculation in pivot table
    By Aland2929 in forum Excel General
    Replies: 1
    Last Post: 09-26-2012, 04:26 AM
  6. Pivot 'Average' calculation disappeared
    By lvalenta in forum Excel General
    Replies: 0
    Last Post: 05-17-2010, 03:40 PM
  7. weighted average calculation in pivot table
    By carlossaltz in forum Excel General
    Replies: 0
    Last Post: 06-06-2008, 08:13 PM

Tags for this Thread

Bookmarks

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