Pivot Tables - Variance and Variance %
Hi,
I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% 0% -10% (row) |
Pivot Tables - Variance and Variance %
You can create calculated fields:
Select a cell in the pivot table On the PivotTable toolbar, choose PivotTableFormulasCalculated Field Type a name for the formula, e.g. Variance Enter the formula, e.g.: =Actual -Planned Click Close Do the same for the Variance % calculated field, using the formula: =(Actual-Planned)/Planned PJS wrote: Hi, I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% 0% -10% (row) -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Pivot Tables - Variance and Variance %
Thank you very much for your assistnace ^_^
"Debra Dalgleish" wrote: You can create calculated fields: Select a cell in the pivot table On the PivotTable toolbar, choose PivotTableFormulasCalculated Field Type a name for the formula, e.g. Variance Enter the formula, e.g.: =Actual -Planned Click Close Do the same for the Variance % calculated field, using the formula: =(Actual-Planned)/Planned PJS wrote: Hi, I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% 0% -10% (row) -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 02:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com