Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table: Variance formula
Within a pivot table, how can I calculate a VARIANCE between 2 columns,
rather than the default "Grand Total" as shown below (i.e., I need to see a variance of 8 on the Dog/Male row, rather than the "total" of 12) ? Animal Gender Born Sold Grand Total Dog Male 10 2 12 Female 5 2 7 Cat Male 1 1 2 Female 3 2 5 Hamster Male 6 3 9 Female 2 1 3 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table: Variance formula
Hi
Right click on PTtable optionsDeselect Grand Total by row. Create a calculated field for your value. From the dropdown on the PT ToolbarFormulasCalculated FieldName Variance Formula = Born - Sold -- Regards Roger Govier "Grunt_13" wrote in message ... Within a pivot table, how can I calculate a VARIANCE between 2 columns, rather than the default "Grand Total" as shown below (i.e., I need to see a variance of 8 on the Dog/Male row, rather than the "total" of 12) ? Animal Gender Born Sold Grand Total Dog Male 10 2 12 Female 5 2 7 Cat Male 1 1 2 Female 3 2 5 Hamster Male 6 3 9 Female 2 1 3 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table: Variance formula
Add another copy of the Born/Sold field to the data area.
Right-click on a cell in that column, and click on Field Settings Click Options, and from the dropdown list choose Difference From As the Base Field, select the Born/Sold field As the Base Item, select Sold Click OK Grunt_13 wrote: Within a pivot table, how can I calculate a VARIANCE between 2 columns, rather than the default "Grand Total" as shown below (i.e., I need to see a variance of 8 on the Dog/Male row, rather than the "total" of 12) ? Animal Gender Born Sold Grand Total Dog Male 10 2 12 Female 5 2 7 Cat Male 1 1 2 Female 3 2 5 Hamster Male 6 3 9 Female 2 1 3 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Variance help | Excel Discussion (Misc queries) | |||
Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
Calculate % variance on previous quarters. Quarter %, etc. pivot | Excel Discussion (Misc queries) | |||
Pivot Table Subtotals/Variance Analysis | Excel Discussion (Misc queries) | |||
Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) |