Greetings to all -

I am currently struggling with a method to apply a custom rollup
function for an Excel pivot table.  I am trying to calculate a sell
through % which is calculated as Sales $/(Sales $ + Ending Inv $).  I
want to be able to calculate this % at the weekly, monthly, and yearly
level.  I can successfully add a calculated field which works
perfectly when the user has WEEK on the pivot table.  However, if the
user wishes to remove week and see the Sell Thru % at the Monthly
level, then Excel naturally sums the Sell Thru % for each week in the
month.  This is not an accurate Monthly Sell Thru %.  When the user
pivots, I want to recalculate the sell thru %.  For example, when the
user wishes to see the Month To date Sell Thru %, I want to calculate
the value as Monthly Sales $/(Monthly Sales $ + Last week of the
month's Ending Inv $).  I want to use the LAST week's Ending Inventory
position to recalculate the sell through percent rather than adding
each week's Sell Thru.

Other mathematical roll ups (such as AVG, MIN, MAX, etc) will not work
either.  The only way to roll up this calculation it to re-calculate
after each pivot based on the source data.

Below is an example of the data.
Calculation at the weekly level
            Data
Style       Month     Week       Sell Thru %   Sales $    Ending Inventory $
62 - WHITE   DEC    12/2/2006       2.92            2,171    72,293
        12/9/2006       2.7            1,969    70,924
        12/16/2006    2.39            1,736    70,805
        12/23/2006    4.31            2,948    65,391
        12/30/2006    2.7           1,738    62,654
Below is an example of the Pivot table showing the Month To Date Sell
Thru as the SUM of the weekly sell thru %
        Data
Style       Month    Sell Thru %      Sales $    Ending Inventory $
62 - WHITE   DEC    15.02          10,562    342,067

The December Sell Thru % should be  14.44 rather than 15.02.
Total Monthly sales/ (Total Monthly Sales + Last week of month's
Ending Inv $)
(10,562/(10,562 + 62,654) ) * 100 = 14.44%

Any help on this issue would be GREATLY appreciated.  Since the data
is in a pivot table, we don't want to restrict the ways in which the
data can be viewed.  However, we are trying to balance this
flexibility with the data integrity. We are using Excel 2003.

Thanks-
Karen