PDA

View Full Version : Pivot table trouble



Anomandaris
03-25-2009, 09:42 AM
Hi guys,

I'm having problems displaying the right information in a pivot table....I've got the data on the file, and two different pivot tables from that info.

As you can see I'm trying to get Average Price and Total quantity/lots for each section.

The first table is quite ugly and inefficient for obvious reasons, the second one shows price and quantity in separate columns but fails to add up the quantity for each 'CODE'.

Any idea how i can fix this? I've been at it for 4 hrs and this is what I've got.....
it would be ideal if I could plug in a formula into a cell but these pivot tables dont allow that


Thanks

Anomandaris
03-26-2009, 02:16 AM
I guess this isn't a vba issue, but still any help would be greatly appreciated. Just making a post to bring this post back to the list of most recent ones :) so ppl can see it.

Anomandaris
03-26-2009, 08:24 AM
This thing is driving me nuts.....please if anyone can help me that'd be great....or if it isn't possible please tell me, i'm wasting too much time on this.

Anomandaris
03-26-2009, 09:55 AM
I've thought of another way to do this, just small adjustment required now using VBA, but im not sure how to code it

This is what the table will look like:

P ProductB/SCodeQTotalGoldBH912890 13900 45860 Avg.Price 872.5714286 X820950 30980 60900 Avg.Price 930.9090909 SZ82950 10920 70970 Avg.Price 963.4146341PlatinumBH951150 671200 Avg.Price 1196.527778 SZ821250 151300 231280 Avg.Price 1286SilverBZ81012 1519 1618 Avg.Price 16.90243902 SH91420 3016 4613 8015 Avg.Price 15.04705882 X86016 Avg.Price 16

Q is quantity....

What i'm trying to get is the total of Q's for each of the codes (eg H9, X9,etc) displayed on the same line as the average price of that coded area.

So the code would be something like:

For each cell in Column C, If x = Average Price then in Column G SUM(Qs)
so for the first one it should say '70 lots' in the G column next to Average Price. And so on continuing for the whole table, the lenghth of which will vary.

Does that sound do-able?

Anomandaris
03-26-2009, 09:56 AM
The info mentioned in my previous post is with reference to the 2nd pivot table in the file 'Pivo'

many thanks