Pivot Table Question +

[Connie Martin]

Sometimes when I post a question here I never see it, but one time when
Googling the subject I found my question posted in this newgroup, but when I
go through the Microsoft website, it's not visible there. I posted a
question this morning about where to find the option to insert a calculated
field, and that question has never appeared here. I did find the answer to
it, however. I have another question now, and hope this will show up.

I have this pivot table:
Requested Ship Date CLEAR STOCK GREEN STOCK CLEAR GREEN
STOCK 4025 177
Mar-10-09 150
Mar-12-09 600 75
Mar-13-09 75
Mar-16-09 375
Mar-20-09 450 75
Grand Total 4025 177 1650 150


I hope those figures will remain intact and won't be all over the place once
this is posted. I want to insert two fields that will calculat a decreasing
balance of the clear and the green. Results of those two fields based on the
pivot table above would be:

Clear Balance Green Balance
3875 177
3275 102
3200 102
2825 102
2375 27

Hope I haven't asked too much. Hope this will post visibly to me, and hope
the tables will post without running all over the screen. My apologies if
they do and I won't blame anyone for not answering. Thank you. Connie

 

[Roger Govier]

Hi Connie

I think I would try to tackle it a slightly different way - if you have that
option.
I would have my source data as
Date Clear Green
Stock 4025 177
Mar 10 -150
Mar 12 -600 -75
Mar 13 -75
Mar 16 -375 -75
Mar 20 -450

Then in Pivot Table Layout
Drag Date to Row area
Drag Clear to Data area
Drag Clear to Data area again
Drag Green to Data area
Drag Green to Data area again

Ensure all are set to Sum
On Clear2 and Green 2>Options>Show data as>Running Total in>Base field>Date

Change the Titles form Sum of Clear and Sum of Clear2 to Clear Items and
Clear Balance or whatever terminology suits you.

If you need to convert your existing data to make the daily transactions
negative, just enter -1 in a spare cell>Copy that cell>Select the range of
transactions in D and E>Paste Special>Multiply.

 

[Connie Martin]

It amazes me that you were able to figure out how my pivot table was laid
out. I see that after I posted it, it went all over the place. It looks
nothing like when I posted it. Is there a secret to posting proper tables in
this newsgroup??? Anyway, I followed all your instructions and everything
works like a charm except for the -1 part. No matter where I copy -1 from,
when I do the multiply from paste special I get this response: "You cannot
change, move a part of, or insert cells in a Pivot Table report.....ETC." I
need a decreasing balance, not an increasing one. Up to that point, this
works beautifully. Now just to get the running total to decrease. Any
ideas? Connie

 

[Roger Govier]

Hi Connie

No, you cannot change any values in a Pivot Table.
What I was suggesting, was a way of changing your SOURCE data, by Paste
Special>Multiply.
The Initial Stock values would still be positive, but all usage of product
would be negative, then you will get the results you require.

is there a secret to posting proper tables in this newsgroup???

No, there isn't.
Because I have been using PT's for a long while (and with the table showing
the results you wanted to see), I was able to figure out what your Source
data must have looked like.

The sample data I posted back was typed, with Tabs between the the columns.
Copying and pasting data will almost invariably get "mashed" like yours.

 

[Connie Martin]

Yes, I finally realized that! Duh! I should've known that!! I did change
the source data columns to negative and formatted the pivot table to red
negative numbers. Somehow I like the look of that better than the minus sign
or the brackets. Thank you again for your help. I have printed this post
and keeping in my binder of Excel "helps". Connie