Pivot table calculated field: multiply Sum of FieldA with Max ofFieldB

[Amedee Van Gasse]

Hello usenetters,

My question concerns Excel 2003.

A user asked me about a calculated field in a pivot table.
Some of the fields are DT_NOW, GW_WCR, GW_LOB.
The formula of the calculated field should depend on the date
(DT_NOW):

Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91
Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28

She added a field to the underlying data called ROT_FACTOR, filled
with 91 until 30th june and filled with 28 starting 1st july.
The current formula is

=IF(ROT_FACTOR>300000;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28)

The cutoff value 300000 is an arbitrary value based on the empirical
observation that the sum of ROT_FACTOR was always larger before 01/07.
In other words, each row in the pivot table sums > 3297 rows of the
underlying data. (row = date)

This formula is of course not future proof. Imagine that some day the
underlying data has more than 10715 to sum per date, then the sum of
all 28's will also be above 300000 and the formula will be wrong.


The following formula gives a completely wrong result:

=GW_WCR/GW_LOB*ROT_FACTOR

because ROT_FACTOR is not the value 91 or 28, but the SUM of those
values.
This conclusion is based on a reply by Debra Dalgleish on another
question: (and of course my own observations)

http://www.tech-archive.net/Archive...cel.worksheet.functions/2006-02/msg02597.html

"You can use a calculated field to divide the Sum of one field by the
Sum of another. Even if the field is summarized by Count in the data
area, its Sum will be used in the calculated field, not the Count.

So, if you're trying to divide sum of FieldA by Count of FieldB, you
could do the calculation outside of the pivot table, referring to the
values in the pivot table.

Or, add a field to the source data that contains a 1 for each record.
Then, use a calculated field to divide FieldA by the Sum of this new
field."

Debra, if you are reading this, I could *REALLY* use your advice.
I prefer not to do the calculations outside the pivot table, because
there is also a pivot chart, and I don't want to make it more
difficult for my user than it already is.

I already considered replacing the 91 and 28 with 0 and 1 in the
underlying data.
That way the formula would have to be:

=IF(ROT_FACTOR=0;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28)

This leaves room for only one more future value of ROT_FACTOR: -1, so
3 possible values in total: -1, 0, 1. It is left as an excercise to
the reader why these are the only 3 values that you can use if you
want to SUM an unknown number of rows.


The ideal formula would be:

=IF(DT_NOW<DATE(2009;7;1);GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28)

but of course this won't work either because DT_NOW is also a sum of
all dates.



I could really use some good advice to solve this inside the pivot
table.
If all else fails, I will take the -1,0,1 approach.


Kind regards,

Amedee
(Thank you for letting me change the magnetic patterns on your hard
disk.)

 

[Herbert Seidenberg]

Excel 2007 Pivot Table.
Calculated field, extended source data.
http://www.mediafire.com/file/1qyewtdyynm/07_06_09b.xlsx
Excel 2003 will not play this file,
nor be able to use the new 2007 features,
but you can probably glean the method from the pictures.

 

[Shane Devenshire]

Hi,

We (at least, I) have asked Microsoft for access to the record level detail
for calculation purposes in the piovt table. At the present time we don't
have that capability, so you are stuck with the two solution approaches you
have discussed.

 

[Amedee Van Gasse]

Excel 2007 Pivot Table.
Calculated field, extended source data.http://www.mediafire.com/file/1qyewtdyynm/07_06_09b.xlsx
Excel 2003 will not play this file,
nor be able to use the new 2007 features,
but you can probably glean the method from the pictures.

Interesting.

(Un)fortunately we won't be upgrading to Office 2007 - company orders.
One of the reasons is our aging hardware, higher hardware requirements
for Office 2007, and the economical crisis.
Upgrading to Office 2007 would mean replacing a few ten thousand
computers. Not to mention the training costs because there are quite a
few differences between Office 2003 and Office 2007. And of course
every custom application that interfaces with Office would have to be
re-evaluated.

 

[Amedee Van Gasse]

Hi,

We (at least, I) have asked Microsoft for access to the record level detail
for calculation purposes in the piovt table.  At the present time we don't
have that capability, so you are stuck with the two solution approaches you
have discussed.

Thank you for your reply.
I have suggested the 0, 1 approach to the user and she agreed.

It helps, but I don't see a Yes button.
Perhaps you are using another web interface for this Usenet group? I
am using Google groups, but I would rather use a real Usenet client
like Thunderbird or Pan or XanaNews.

Cheers,
Shane Devenshire

Kind regards,
Amedee Van Gasse