Ytd Calculation In Pivot

S

SPIRONIK

Hello everybody.
I am trying to simplifie a report, comparing sales and number o
customers for 2 years, refreshing it every week (52 weeks per year).
I tried to do it using a pivot and i have the following problem:
If i add a calculated field, adding week 1+week 2+...+current wee
(having in mind to add every week the running one), excel does not le
me add more than 25 weeks because it can handle a specific number o
characters in the calculated field,
Does anyone has an idea how I can overcome this stupid thing?
Any other suggstion would be greatly appreciated
 
D

Debra Dalgleish

You could do the calculation outside of the pivot table.

Enter the current week number at the top of the worksheet
(cell B1 in this example)
In a column far to the right of the pivot table, enter the
years that you want to total. For example, in cell DK7,
type 2003 and in DL7 type 2004.
In cell DK8, enter the following formula, to total the
first row of data in the pivot table:
=SUM(OFFSET($B8,0,MATCH(DK$7,$B$6:$CN$6,0)-1,1,$B$1))
Copy the formula down to the last row of data in the pivot table. and
across to column DL.
 
Top