calculating variances in pivot tables

S

smw2340

i am trying to turn the grand total column into a variance column. I do not
want to sum the hours in my data set i want to show the difference between
the hours from one year to the next. the data in my pivot table looks like
this:

Col A: Col B: Col C: Col D:
Account # 2007 Hours 2008 Hours Grand Total

Any suggestions?

Thanks!
 
C

Charles Williams

How about adding a pivot table calculated field called Variance with a
formula something like:
='2008 Hours' - '2007 Hours'

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
S

smw2340

Thanks for the suggestion Charles. However, i get an error saying i've used
the same field more than once. in my data table that the pivot is pulling
from i have the following columns: Account, Year, Hours. When i create the
pivot the years are in the columns, account in the rows and the hours is the
data item.
 
C

Charles Williams

OK: Try this

Double-click Sum Of Hours label to show the PivotTable field dialogue for
the Hours field
Click Options
Show Data as 'Difference From'
Base Field should be 'Year'
Base Item should be (previous)

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top