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_FACTOR300000;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/.../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.)