A

#### Amedee Van Gasse

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.)