+ Reply to Thread
Results 1 to 8 of 8

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
    15

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    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 2019
    Posts
    17,428

    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
    15

    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 2019
    Posts
    17,428

    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.

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

    Re: Pivot table truncate format for average percentage

    Thank you for trying

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Pivot table truncate format for average percentage

    Just finished an online lesson in Power Pivot which brought this thread back to mind.
    This proposal makes use of Power Query and Power Pivot, both of which come standard on the 2016 version I believe.
    1. Select a cell in Table 1
    2. On Data tab select From Table/Range
    3. In the Power Query Editor change the type of the Review Period column to Date
    4. Choose Close and Load to and choose Data Model
    5. On the Data tab select Manage Data Model
    6. Select one of the unnumbered rows, below the thick gray line, paste the following in the formula bar: TruncatedAverageScore:=TRUNC(AVERAGE([Score])) and press Enter
    7. Close the Data Model Manager window
    8. Open a new sheet
    9. Press the Alt + n + v keys
    10. Use this workbooks Data Model and the cell in the sheet should already be selected, if not select them
    11. Set up is the same as in the original Pivot Table except that fx TruncatedAverageScore is placed in the values field.
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. [SOLVED] VBA create pivot table in excel 2010 defaulted to 2003 pivot table format
    By lynnsong986 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2019, 02:05 PM
  3. [SOLVED] How to add average line of percentage in the pivot chart
    By nazdiana in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-31-2017, 05:49 AM
  4. Replies: 4
    Last Post: 09-26-2017, 12:41 PM
  5. [SOLVED] How To Produce A Pivot Table That Shows The Average Commission Percentage?
    By The_Snook in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-31-2013, 05:24 AM
  6. Percentage in a Pivot Table
    By rmikulas in forum Excel General
    Replies: 3
    Last Post: 10-13-2010, 03:59 PM
  7. How do I truncate or hide portions of text in a pivot table?
    By Sean McCloskey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2006, 08:05 PM

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