pivot table calculated field: quotient and mod

[HFLO]

I have a table with columns:


item,qty,unit1,ratio,unit2

the qty here are referring to qty in unit1
for the same item, the ratio and unit2 are always the same

I then have a pivot table with:
item,unit1,sum of qty

I would like to add calculated field (qty2) where
qty2=quotient(sum of qty,ratio)
qty3=mod(sum of qty,ratio)

how to do it?
thanks.

[ArviLaanemets]

I don't have Excel available at moment to test it, but probably this may work:
1. In your table, add a column SumQty, where you calculate the sum like in pivot using SUMIFS(). You get the same sum repeated for every row with same item and unit1;
2. In your table, add another 2 columns, where you calculate qty2 and qty3. Again, you get the same values repeated for every row with same item and unit1;
3. In pivot, along with sum of qty, calculate also average of qty2 and average of qty3.

[HFLO]

Hi,
thanks for sharing.
I tried, but maybe I did not understand it correctly,I didn;t get the result I needed.

refer attached pic for what I am trying to achieve.
those columns in light blue is my pivot table, where column totalSMALL is the total quantity in smallest unit of the item, column ratio is the ratio of small package to big package of that item.
the 2 green column is what I am trying to add to my pivot table result, showing the total small package qty of the item being converted to big packaging (QUOTIENT function) and small packaging (MOD function)

thank again for all advice.

[ArviLaanemets]

An example about my posting. It is based on your opening post - it looks like your second post is about something different!

[HFLO]

Hi,
I'm sorry if I didn't make it clear enough in my first post.
my second post if what I am trying to achieve.
thanks

[ArviLaanemets]

It looks so long you keep rows in in your pivot based on single column (Item, or ItemName only), the solution I provided will work anyway (you can calculate Average of ratio as ratio, Sum of qty as totalSMALL, Average of qty2 as resultBIG, and Average of qty3 as resultSMALL).

It will getting ugly, when you try add more columns to pivot rows (Item, item name, packSmall, packBig) - pivot interprets them as data groping fields, calculating subtotals for all of them. And there is nothing you can do about this - this is how pivot works.

One way to get all additional columns to be displayed, is to use an ODBC query instead of pivot table.

Another way is possible when you have a separate Items sheet, where all items are described (and which may serve as source for Data Validation List to select item in your tables) - item codes, item names, packSMALL, packBIG and Ratio. Then you can make all calculations in this Items table instead of pivot (using SUMIFS() to calculate totalSmall there, and calculating resultBIG and resultSMALL using your formulas).