Excel 2016 Pivot Table Formulas

User avatar
Tom54
Lounger
Posts: 26
Joined: 15 Feb 2010, 18:04

Excel 2016 Pivot Table Formulas

Post by Tom54 »

Dear Loungers:

Attached is an Excel 2016 Workbook with a simple Data Table and a Pivot Table Generated by that Table.

The just below the Pivot Table results is the results I desire.

What I am doing right now is to Copy the Pivot Table Generated to another worksheet as values then using several columns of computations to come up with revised results. It works but was hoping there is a better way.

Is there a way to have the results I desire using either Field / Item formulas? Or Perhaps Generate a Pivot Table based on the fist Pivot Table?
Or some other way using Pivot Tables?

Any assistance is most appreciated.

Regards,
Tom54
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel 2016 Pivot Table Formulas

Post by HansV »

I don't see a way to do this in a pivot table, since the formulas in the Apportion Manuals row aren't consistent across columns D to G.
Best wishes,
Hans

User avatar
Tom54
Lounger
Posts: 26
Joined: 15 Feb 2010, 18:04

Re: Excel 2016 Pivot Table Formulas

Post by Tom54 »

Hans:

Thank you for confirming the fact that this cannot be done using Pivot Table formulas.

What I think may get me closer is creating a separate Field for the Manual element. That should allow the Pivot Table to generate the require Revenue Ratios I need. Then perhaps some kind of GetPivotData function formula (not part of the Pivot Report) to do the remainder of the computaitons.


Tom54