pivot table: divide data columns with same data field

[fax]

hi all.

how can i divide all values in one data column in a pivottable with the same data value in another column in the same pivot table ? how do you specify the reference in the calculated field so it always takes the same 1 value ?

this is very urgent. thanks in advance.

[austenr]

Hi fax and welcome to VBAX!!

In order to help you better could you please upload a sample of the data you are using (minus any sensitive data)? This would greatly help the person assisting you with your question. You can upload the file as a .zip file if you scroll down and click "Manage Attachments" under Additional Options.

:hi:

[fax]

http://www.faruktugcu.com/tempfiles/ptformula.jpg

the above is a simplified version of the pivot table i have going on.

the sourcedata is set so the production amount is reported under 'manufacturing', although there are costs in other types. i want to have a 3rd column that would, for each product, do cost of that type / production amount subtotal at each row. so it would basically be cost breakdown at each row, with the total cost at the subtotal level.

i cant link to that specific or subtotal production amount, so i always get 0 cost/production where production amount is 0.

if i were to manipulate the data so that production amount was artificially distributed across all types, then the subtotal cost would be wrong.

i tried with calculated formula/item to no avail. do i use consolidation here ?

i need to always reference the production amount field under the manufacturing type...or the subtotal of the production column. (since they are the same)

thanks in advance for the help

[tkaplan]

I needed to do something similar and the only way i came accross to save it was add a column in my source table.
i added a column with the formula =sumif(A$2:A$10,A2,B$2:B$10) and drag down
the example above assumes the type is in colA and amount is in B. col C will now have the subtotals. In colD put =c2/d2.

This worked for me.
If you attach an actual worksheet that has your source data in it, we may be able to help you more.

hope this helps.

[fax]

thanks very much for the reply. im feeling very frustrated over this, ive been trying to get it to work for 2 days now.

unfortunately, i couldnt get the example u gave to work. id prefer to get the calculation done in the pivot table because it will be very dynamic (changed based on month, week, other criteria)

im attaching the sample sheet. my actual data has more columns with other filterable data (date, etc.) with over 200 products. again, all i want (sounds easy!) is to have the pivot table automatically calculate each cost type of the product divided by the production amount of product. it has to be within the pivot because the number of rows is not equal for all products.

[tkaplan]

I may be wrong, but as far as I know, you cannot do the calculation in the table itself. (If I am wrong , someone please let me know because i am trying to figure this out as well.)

I played around with your data a little bit and I came up with a formula that it doesnt matter if the data row amounts, etc, changes.

Take a look at the attached and see if that helps. I added the last two columns.

[fax]

thanks for that. looks good for a datasource of this size. will report back when i try it on my real full data, where i have other criteria/filter groups. the pivot table should take care of that, in theory, but u know ex-hell.

[fax]

problem... :banghead:

this scheme adds up all quantities for the same name product. this causes problems when i have to bring in further data categories, available from my full source, such as dates.

as example, ive put in some months. notice for product a that although full (total) $, quantity, and resulting $/kg calculation is correct; monthly calculations are incorrect. check out month 1 for same product.

do i have to build in another counta function for each possible category/filter ??

see attachment for problem...

[tkaplan]

you would need the condition for the sumif to be if it is equal to both the month and product.
i dont know if you can do this in the sumif formula... maybe someone else can help out here
can you put a sumif formula to be:
sumif(a1:a10,and(a1,b1),c1:c10)
i am trying to say in the above statement that in the cell that i put this formula, i want all of the rows that have the same combination of values as a1 and b1, the corresponding value in c to be added.

if there is no way to accomplish the above, you can add a column that concatenates all of the criteria you want to match by and put the range and criteria in the sumif formula as that column. see attached for example.
this is just a workaround and i would like to believe that excel has something better built in, but i wasnt able to find it either....

[fax]

yup this will work. cumbersome with the number of fields i have, but it will work. thanks.

still awaiting a 'clean' solution from within the pivottable if anyone knows...