Cell D3 has the "IF" formula that looks for the "Grand Total" row then it uses a formula to give minutes per transaction which is used for transactions per hour. If I use a large set of motions then I have to keep building IF statements to search down the B* column. The raw data is pulled from another android tablet application which is exported into Excel. Is there another way to find "Grand Total" row and use the corresponding column E number to populate cell D3?
Pivot Table Calculations Outside of Pivot TableUse Index? Use Match?
Posted 14 August 2017 - 06:46 PM
You can solve this quite easily by using the GETPIVOTDATA function. This allows you to extract data directly from the pivot table's internal data store, meaning the correct data will be returned even if the number of rows in the pivot table changes.
You can see how to create a GETPIVOTDATA function in the video lesson: Expert Skills Lesson 6-4 Use the GETPIVOTDATA function.
I think the formula you were looking for is:
=(GETPIVOTDATA("Sum of Unit Duration (seconds)",$B$4)/60)/GETPIVOTDATA("Count of Motion",$B$4)
Note that GETPIVOTDATA functions are usually inserted automatically when you try to create a formula that refers to a cell in a pivot table, but this feature can be switched off.
To switch on automatic GETPIVOTDATA functions, click: File > Options > Formulas > Use GetPivotData functions for PivotTable references
I have attached a copy of your workbook with the new formulas applied:
Rate Validation2017.xlsx 52.1KB 60 downloads
I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.
Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.
Posted 19 August 2017 - 06:40 PM
Thank you for your advice, I used the getpivotdata for the row "Grand Total" to retrieve minutes then use $C$5 to pull the actual tasks to measure rate by hour. It works with any slicer study that I want to see. I make sure my element I want to measure by hour and the number 1 in front of the element so it's always at the top of the pivot table. The element is different with each study but when I use the 1, it's always at the top.
=(GETPIVOTDATA("Sum of Unit Duration (seconds)",$B$4)/60)/$C$5
1 user(s) are reading this topic
0 members, 1 guests, 0 anonymous users