pivot tables

G

Guest

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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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 ?
 
Upvote 0
On 2002-02-26 00:17, Anonymous wrote:
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 ?

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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top