Pivot table question
Posted by Mudface on November 14, 2001 2:58 AM
I have a raw data sheet listing Product Name, Amount, Colour (on a scale from 0 to 10) and about a dozen other parameters. This is linked to a pivot table for average analyses and total amounts made. Is there a way to sort the pivot table so that only those products with a colour in excess of 3 are shown, along with their total amounts?
Thanks for any help.
Posted by Don C on November 14, 2001 9:37 AM
Two options:
1 - include color in the pivot table; you can set it so that color values 1, 2, and 3 do not show (but you will get a set of values for each color value that is coded).
2 - if you want only those >3, then create a new column that identifies those records that meet the criterion (=if(b2>3,"Yes","No") and include THIS column in your pivot table.
Posted by Mark W. on November 14, 2001 12:34 PM
3rd approach...
Add the 'Colour' field to the COLUMN or ROW area
of your PivotTable. Select cells containing the
'Colour' items that are greater than 3, right
mouse click, and choose Group and Outline | Group...
from the popup menu. This will create a new
field, 'Colour2', with a "Group1" item. Select
the cell containing "Group1" and type ">3". Drag
the 'Colour2' field to your PivotTable's PAGE
area and choose the newly created ">3" item.
There they are!!! Products with a Colour value
greater than 3.
Posted by Mark W. on November 14, 2001 12:56 PM
A 2nd variation of the 3rd approach...
As before... Add the 'Colour' field to the COLUMN
or ROW area of your PivotTable; however, this
time right mouse click the 'Colour' field button
itself and choose the Group and Outline | Group...
command from the popup menu. On the Grouping
dialog enter 4 in the "Starting at" field, 6 in
the "By" field, and press [ OK ]. You may want
to drag the 'Colour' field to the PAGE area and
choose its "4-10" item.
