Manipulating totals and columns in Pivot Tables

[jadeB]

Hi,

I'm constructing a pivot table that has investors as columns,
investments as rows, and the general data is values. Trouble is there
is a column which I want to only display half of each value. I know
that I can make a formula that divides a column "column A/2", and then
hide the unwanted columns. But I would also like the hidden columns
to not be included in the grand totals. How do I make this happen?

Thanks!

 

[ShaneDevenshire]

Hi,

So let me understand what you want, something like this in the Pivot Table:

A B C
Name
Jp 100 50
AA 200 100
BB 300 150
.....
Grand Total 600 ---

In the pivot table the Grand Total under column C you want empty?

You could format that cell to the custom format ;;;

 

[jadeB]

If you start with your table here

A                           B                C Totals
Name
Jp                         100             50 150
AA                        200           100 300
BB                        300           150 450
....
Grand Total           600             300 900

And I want....

A B D Totals
Name
Jp 100 25 125
AA 200 50 250
BB 300 75 375
....
Grand Total 600 150 750

So column D is 1/2 of column C. Column C is either deleted or hidden
(I think it needs to be hidden so I can take out 1/2). The totals
column is just adding columns B & D --- completely ignorning Column
C.

Does that make sense?

 

[ebloch]

If "D" is always "C"/2 why have both "C" and "D" ?

Move the Totals into "D" and "D"= "B"+"C"/2

Eric

If you start with your table here

A B C Totals
Name
Jp 100 50 150
AA 200 100 300
BB 300 150 450
....
Grand Total 600 300 900

And I want....

A B D Totals
Name
Jp 100 25 125
AA 200 50 250
BB 300 75 375
....
Grand Total 600 150 750

So column D is 1/2 of column C. Column C is either deleted or hidden
(I think it needs to be hidden so I can take out 1/2). The totals
column is just adding columns B & D --- completely ignorning Column
C.

Does that make sense?

 

[jadeB]

The data sheet is set up that the full amount imports into Excel. The
full amount is used on several other sheets - if I change the data to
half of the full amount I'll just have the opposite problems elsewhere
- because the number would then need to be doubled.

 

[Shane Devenshire]

Hi,

I note that C=B/2 is this pure accident? If not and D=C/2 then D=B/4
1. In which case you don't need C at all
2. Right click the grand totals if you have any and choose Hide
3. Now to create the Total it is just B+D = B+B/4 = 5*B/4 another calculated
field.

I tested it and it work just fine

 

[jadeB]

C=B/2 was an accident (I actually just copied your table). But your
answer gave me an idea that I think I can run with. Thanks.