+ Reply to Thread
Results 1 to 6 of 6

Pivot table truncate format for average percentage

  1. #1
    Registered User
    Join Date
    11-08-2015
    Location
    florida
    MS-Off Ver
    Home and Student 2016
    Posts
    10

    Pivot table truncate format for average percentage

    I have a dataset and need to do a pivot table with an average.
    I need to truncate the average as follows:

    Say the average is 95.6
    I need to truncate it to 95

    The same if it is 83.23
    I need to truncate it to 83

    So I donít want to round it up or down but to truncate it.
    Any assistance is appreciated.

    Thank you.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    22,622

    Re: Pivot table truncate format for average percentage

    I don't follow you. What's the difference between rounding it down and truncating it?? The TRUNC function may be what you want?? If not, put us out of our misery and show us what you mean...

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    11-08-2015
    Location
    florida
    MS-Off Ver
    Home and Student 2016
    Posts
    10

    Re: Pivot table truncate format for average percentage

    Sorry for the confusion.
    I have attached an example.
    Rounddown would work, but I do not know how to do that in the pivot table.
    The real data will be about 50K records.

    Please let me know if the attached is sufficient.
    Thank you
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,834

    Re: Pivot table truncate format for average percentage

    This may be trivial, however another column (Av. Score) could be added to the source data. The Av. Score column is populated using: =ROUNDDOWN(AVERAGEIFS([Score],[Model],[@Model],[ReviewPeriod],[@ReviewPeriod]),0)
    Place Av. Score in the values field of the pivot table. To display the subtotals and grand totals as truncated integers set the number format of the Av. Score field to show zero decimal places.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-08-2015
    Location
    florida
    MS-Off Ver
    Home and Student 2016
    Posts
    10

    Re: Pivot table truncate format for average percentage

    Thank you.
    That works nicely.

    Although, I will have slicers and charts and possibly other columns from the slicers they may want the average of.

    I am guessing there is no way to rounddown the pivot average then?

    Thank you for your help.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,834

    Re: Pivot table truncate format for average percentage

    "I am guessing there is no way to rounddown the pivot average then?"
    I let this sit for a couple of days to see if anyone else had an idea.
    It may be possible to use VBA to accomplish what you want, however I do not know enough about VBA to be of any help.
    You may want to ask a moderator to move this to the VBA forum and see if one of the contributors has an idea.
    I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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