Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-09-2018, 07:46 AM
HFLO HFLO is offline pivot table calculated field: quotient and mod Windows 7 64bit pivot table calculated field: quotient and mod Office 2013
Novice
pivot table calculated field: quotient and mod
 
Join Date: Jun 2017
Posts: 7
HFLO is on a distinguished road
Default pivot table calculated field: quotient and mod

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.
Reply With Quote
  #2  
Old 06-09-2018, 08:31 AM
ArviLaanemets ArviLaanemets is offline pivot table calculated field: quotient and mod Windows 8 pivot table calculated field: quotient and mod Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #3  
Old 06-09-2018, 09:49 PM
HFLO HFLO is offline pivot table calculated field: quotient and mod Windows 7 64bit pivot table calculated field: quotient and mod Office 2013
Novice
pivot table calculated field: quotient and mod
 
Join Date: Jun 2017
Posts: 7
HFLO is on a distinguished road
Default

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.

Reply With Quote
  #4  
Old 06-09-2018, 11:38 PM
ArviLaanemets ArviLaanemets is offline pivot table calculated field: quotient and mod Windows 8 pivot table calculated field: quotient and mod Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

An example about my posting. It is based on your opening post - it looks like your second post is about something different!
Attached Files
File Type: xlsx Pivot.xlsx (13.6 KB, 12 views)
Reply With Quote
  #5  
Old 06-10-2018, 04:21 AM
HFLO HFLO is offline pivot table calculated field: quotient and mod Windows 7 64bit pivot table calculated field: quotient and mod Office 2013
Novice
pivot table calculated field: quotient and mod
 
Join Date: Jun 2017
Posts: 7
HFLO is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 06-10-2018, 08:45 AM
ArviLaanemets ArviLaanemets is offline pivot table calculated field: quotient and mod Windows 8 pivot table calculated field: quotient and mod Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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).
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding the same field in the column field list of pivot table nanka Excel 4 03-19-2017 08:12 AM
pivot table calculated field: quotient and mod Show calculated fields within the Pivot Table John in DSM Excel 2 09-07-2016 02:12 PM
Creating new field in pivot table based off existing field laceymarie1987 Excel 0 06-26-2014 12:13 PM
Pivot Table:delete zero values when using calculated objects Serge 007 Excel 1 06-05-2013 11:47 AM
Excel Pivot Table Calculated Field BertLady Excel 0 05-21-2012 10:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:45 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft