pivot tables

Dear sirs,

multiplying figures with percentages in a pivot table leads to the right result in the line, but the total total provides a wrong value; example:
10 * 20% = 2 => o.k.
20 * 30% = 6 => o.k.

30 * 50% = 15 => this would be ttl total,
because the percentages would be added.
How is it able to hide the ttl of the percentages (50%) and to get the right result of 8 in the end ??
Many thanks in advance..

[Mark W.]

Are you using a Calculated Field? Where are your percentage values?

On 2002-02-25 08:57, Mark W. wrote:
Are you using a Calculated Field? Where are your percentage values?

yes, it's a calculated field;
raw data would be
-a- -b-
1st line: 10 ! 20%
2nd line: 20 ! 30%

does this help ?

[Mark W.]

On 2002-02-26 00:17, Anonymous wrote:

yes, it's a calculated field;
raw data would be
-a- -b-
1st line: 10 ! 20%
2nd line: 20 ! 30%

does this help ?

Yes, it helps! As it states in the Help topic for "Syntax for calculated field and item formulas in PivotTables", "Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula." You're getting incorrect results because your order of operations isn't correct! To preserve the proper order of operations you need a new data field (in column C) containing the formula, =A2*B2. It's this new field that should be used in your PivotTable.

[ This Message was edited by: Mark W. on 2002-02-26 06:43 ]

Thanks Mark,
it works now,

[Henry]

I tool have a calculate field that returns percents, but the wind up beng summed.

I fixed that by adding new fields, but now, I get #DIV. How can I set the Cal formulat to get rid of the #div?

Mike

[Mark W.]

On 2002-03-20 08:12, Henry wrote:
I tool have a calculate field that returns percents, but the wind up beng summed.

I fixed that by adding new fields, but now, I get #DIV. How can I set the Cal formulat to get rid of the #div?

Mike

If you're calculating percentages using...

=A1/B1

...use this instead...

=IF(B1,A1/B1,0)