Pivottable -> 'Others'

[Bobthebuilder]

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

 

[Mark W.]

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

 

[Bobthebuilder]

Not sure what you mean????

 

[Mark W.]

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.