Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Pivottable -> 'Others'

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Telephone City
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Using pivottables, is there a way of showing the top 15 of something and then have the reaminder tallied up under and 'Other' row?

    Also, using the Autoshow method, is there a way of preserving the total for the original p.table? (Using autoshow, shows a grand total for the number of entries selected under autoshow!)

    Thanks a bunch

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, you'd have to incorporate those categories into your data list.

  3. #3
    Board Regular
    Join Date
    May 2002
    Location
    Telephone City
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure what you mean????

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Suppose that A1:B5 contains...

    {"Field1","Field2"
    ;"Apples",1
    ;"Bananas",9
    ;"Grapes",6
    ;"Oranges",4}

    ...and you want a PivotTable to Sum 'Field2' based on the top 2 items of Field1 and include an "Others" category. Just add a new dimension, 'Field3', to your data set by entering the formula...

    =IF(OR(B2=LARGE($B$2:$B$5,{1,2})),A2,"Others")

    ...into C2 and copying down to C5. Now your data set looks like...

    {"Field1","Field2","Field3"
    ;"Apples",1,"Others"
    ;"Bananas",9,"Bananas"
    ;"Grapes",6,"Grapes"
    ;"Oranges",4,"Others"}

    You can now create a PivotTable with Field3 in your ROW area and 'Sum of Field2' in the DATA area.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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