Excel Pivot table calculation

[MaxBrit]

Hello

Have data in following format (dynamic range)

year value group acct
2003 200 a acct1
2004 20 a acct1
2003 100 b acct1
2004 15 b acct1
2003 150 a acct2
2004 25 b acct2

Pivot table format (based on dynamic data range as above)

Year
Acct
Group
Sum of value


Would like to show another field for each acct/group as (sum o
value/"number of months")*12 i.e. a year sales forecast.

But only show for year 2004.

year "calc"
acct 2003 2004 Fore
group
acct1
a 200 20 80
b 100 15 60

Many thanks for any help

Maxwel

 

[MaxBrit]

Answering own post.

Adding a calculated item using ('2004'/3)*12 has given the require
column.

But now get data for each group, so pivot table has grown considerabl
in length and takes an age to calculate

 

[Debra Dalgleish]

Instead of calculating this in the pivot table, add a column to your
source table, and calculate there.

Enter the year and month number on the worksheet. For example:
G1: 2004
H1: 3

In row 2 of the source table, enter the following formula, and copy down
to the last row of data:

=IF(A2<>$G$1,B2,B2/$H$1*12)

Add a heading to this column, e.g. ValueFC, and add it to the data area
of the pivot table, instead of the original value field.