Excel (2013) Pivot Table assistance required
Posted on 2016-10-06
As may be the obvious, I am in dire need of some assistance with creating a pivot chart which will visualise the data my manager (and thus I) so badly desire. I have tried my best to explain the issue but I apologise if this isn’t as detailed or straight forward as it could or should be – please feel free to call me out on this or ask for more information.
I have simplified a version of the table I have below:
Column A Column B Column C
Outcome 1 Outcome 2 FTE
B A 0.500
B C 0.500
C A 1.000
D A 0.200
A - 0.053
B A 0.875
D - 0.060
A D 0.200
With the above table in mind, there are two rules:
1) Where there is only one Outcome present across both Column A and B, the total FTE value in Column C must be assigned to that one Outcome; or
2) Where there are two Outcomes present across both Column A and B, the FTE value in Column C must be split equally between both Outcomes.
Following the above rules, I require a pivot table to list each Outcome (A-J) and display the total FTE value for that Outcome, e.g:
I’m not sure if there is any way for the above to be completed using only a pivot table or if I must input some hidden formula’s into the table prior to putting this into a pivot table? Please note that I cannot use VBA as this tool has been disabled.
Oh and I may as well throw in there that each row has another assigned value, a team name, which I will be required to use to filter the pivot table, probably using a slicer.
Any help would be greatly appreciated as I have racked my brain trying to come up with a solution for this. Knowing my luck there will have been a simplistic solution right in front of my eyes this entire time!