Pivot Tables - Variance and Variance %

[PJS]

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]

You can create calculated fields:

Select a cell in the pivot table
On the PivotTable toolbar, choose PivotTable>Formulas>Calculated 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

[PJS]

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 PivotTable>Formulas>Calculated 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
>
>