Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
PJS
 
Posts: n/a
Default 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)




  #2   Report Post  
Posted to microsoft.public.excel.misc
Debra Dalgleish
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
PJS
 
Posts: n/a
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate % variance on previous quarters. Quarter %, etc. pivot DaveC Excel Discussion (Misc queries) 1 August 8th 05 06:45 PM
Pivot Table Subtotals/Variance Analysis NCW Excel Discussion (Misc queries) 1 August 6th 05 07:23 PM
Pivot Tables???? billy2willy Excel Discussion (Misc queries) 1 June 5th 05 01:08 AM
I have two identical pivot tables with different sort order of th. WilliamJFoster Excel Discussion (Misc queries) 1 April 7th 05 03:48 AM
Pivot Tables - Variance and % Variance fields CraigS Excel Discussion (Misc queries) 5 January 6th 05 01:22 AM


All times are GMT +1. The time now is 08:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"