Tough Pivot Table Calculated Field Question

[ammdumas]

This one has me stumped and from the few posts I've seen, the answer (add another field in source data) doesn;t work for me.

I have a staffing worksheet which shows total hours by month by job class and discipline (months on top, classes, disciplines down side). Hours per month also need to be shown in units or people per month. Each class can be in the field (200 hours = 1 unit) or in-house (174 hours = 1 unit). See table below.

Disicpline...Class...In-house/Field...May...June...July...August...Septemeber
Mech........ENG1........F...............200.....200...100......50..........100
Mech........ENG1........H................87.......87....174.....174.........174
Mech........DES1........H...............174.....174....174
Mech........PM1..........F..........................................200..........200

Now I have a SUMPRODUCT formula above each month to show total people per month, taking into account if they're marked as H or F...

=SUMPRODUCT(--($D$13:$D32="F"),F13:F32)/$F$6+SUMPRODUCT(--($D$13:$D32="H"),F13:F32)/$E$6

(F6 and E6 being where 200 and 174 are housed, respectively)

This formula row at the top allows me to produce a basic bar chart of people per month over the duration of a project. Yay. But I want to take it a step further. In a pivot table I would be able to sub-divide each month by class (and/or by discipline) being used in that month. If I only had one value representing a person/unit (say 200), I could simply create a calculated field of =Month/200. But the Field/In-House bit throws a wrench into that.

Is it possible to somehow create a SUMPRODUCT-type formula as a calculated field in a pivot table? Please note that there are many projects spanning several years and adding a column for each month representing a unit value is not an option.

 

[Jerry Sullivan]

Is it possible to somehow create a SUMPRODUCT-type formula as a calculated field in a pivot table? Please note that there are many projects spanning several years and adding a column for each month representing a unit value is not an option.

I don't think it is possible to use a calculated field to do what you are describing. Even if it were possible, using the source data structure you have, I'm pretty sure you would need to setup a calculated field for each month. This would be more effort than adding a column for each month...which understandably you don't want to do.

Is restructuring your source data an option? If you had one column for the Month, it simplifies the calculation of the Full Time Equivalents (People per Month) for your Pivot Table.

Data Source:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 75px"><COL style="WIDTH: 59px"><COL style="WIDTH: 68px"><COL style="WIDTH: 49px"><COL style="WIDTH: 46px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 36px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Month</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Disicpline</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">In-house/
Field


</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Hours</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">FTE</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: left">May-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>F</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: left">May-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">87</TD><TD style="TEXT-ALIGN: right">0.5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: left">May-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>DES1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">174</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: left">May-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>PM1</TD><TD>F</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: left">Jun-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>F</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: left">Jun-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">87</TD><TD style="TEXT-ALIGN: right">0.5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: left">Jun-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>DES1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">174</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: left">Jun-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>PM1</TD><TD>F</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: left">Jul-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>F</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">0.5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: left">Jul-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">174</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: left">Jul-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>DES1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">174</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: left">Aug-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>F</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">0.3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: left">Aug-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">174</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: left">Sep-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>F</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">0.5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: left">Sep-10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Mech</TD><TD>ENG1</TD><TD>H</TD><TD style="TEXT-ALIGN: right">174</TD><TD style="TEXT-ALIGN: right">1.0</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F13</TD><TD>=E13/IF(D13="F",$F$6,$E$6)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4



Pivot Table

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 77px"><COL style="WIDTH: 71px"><COL style="WIDTH: 82px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 41px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 36px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Sum of FTE</TD><TD> </TD><TD> </TD><TD>Month</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 36px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Class</TD><TD>Disicpline</TD><TD>In-house/ Field</TD><TD style="TEXT-ALIGN: right">May-10</TD><TD style="TEXT-ALIGN: right">Jun-10</TD><TD style="TEXT-ALIGN: right">Jul-10</TD><TD style="TEXT-ALIGN: right">Aug-10</TD><TD style="TEXT-ALIGN: right">Sep-10</TD><TD style="TEXT-ALIGN: right">Total</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>DES1</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD> </TD><TD>Mech</TD><TD> </TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD> </TD><TD> </TD><TD>H</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>ENG1</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">1.25</TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">7.25</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD> </TD><TD>Mech</TD><TD> </TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">1.25</TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">7.25</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD> </TD><TD> </TD><TD>F</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">0.50</TD><TD style="TEXT-ALIGN: right">0.25</TD><TD style="TEXT-ALIGN: right">0.50</TD><TD style="TEXT-ALIGN: right">3.25</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD> </TD><TD> </TD><TD>H</TD><TD style="TEXT-ALIGN: right">0.50</TD><TD style="TEXT-ALIGN: right">0.50</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">4.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>PM1</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD> </TD><TD>Mech</TD><TD> </TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD> </TD><TD> </TD><TD>F</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>Total</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3.50</TD><TD style="TEXT-ALIGN: right">3.50</TD><TD style="TEXT-ALIGN: right">2.50</TD><TD style="TEXT-ALIGN: right">1.25</TD><TD style="TEXT-ALIGN: right">1.50</TD><TD style="TEXT-ALIGN: right">12.25</TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4

 

[Fazza]

The already suggested different data structure, by Jerry, is great. It really is the best way to work.

For the current structure, if I understand correctly, the SUMPRODUCT formulas above each month could be SUMIF formulas.

(Again with the current structure, though much easier if not,) For the pivot table, give the source data a defined name (not dynamic), save the file then - starting from a separate workbook - use the pivot table wizard (ALT-D-P) choosing the external data option at the first step. Follow the wizard to the end and then take the option to edit in MS Query. Within MS Query edit the SQL to suit. This can accommodate the calculation. 'Open door' icon to exit MS Query & complete the pivot table. The completed pivot table worksheet can be moved into the source data file if you like. SQL similar to below: this gives the idea. You'd have to get it right for your headers, which I assume are 'May', 'June', etc.

SELECT Discipline, Class, InHouse, May / IIF(InHouse='F',200,174) AS [May], June / IIF(InHouse='F',200,174) AS [June], etc
FROM YourDataName

Another way. You could re-arrange (normalise) the months within the SQL: convert the current data structure to the efficient normalised form. Such as,

SELECT Discipline, Class, InHouse, 'May' AS [MyMonth], May AS [Hours]
FROM YourDataName
UNION
SELECT Discipline, Class, InHouse, 'June' AS [MyMonth], June AS [Hours]
FROM YourDataName
UNION
etc

Then use that to handle the conversion within SQL such as,

 SELECT Discipline, Class, InHouse, MyMonth, Hours / IIF(InHouse='F',200,174) AS [FTE]
FROM (
SELECT Discipline, Class, InHouse, 'May' AS [MyMonth], May AS [Hours]
FROM YourDataName
UNION
SELECT Discipline, Class, InHouse, 'June' AS [MyMonth], June AS [Hours]
FROM YourDataName
UNION
etc from above)

If the above isn't familiar it will take some time to learn. Suggest googling for similar old posts where I've used SQL to replace calculated pivot table fields.

HTH. Regards

 

[ammdumas]

Hey Guys,

JS411...I like your solution, and agree its the best format, but unfortunately it means duplicating a lot of data entry on the user side of things. Everything is organized by class so that's the 'parent' value for each row if you will, and everything is going to be searched, sorted, calculated field, etc. based on that value. In your method I would have to add a row for every instance of month that class appears in, as opposed to just typing in an hour amount in the predesignated column. My way reduces data entry (and is ul;timately how the big bosses want to see it) but as feared, you lose some pivot table abilities.

Fazza...saw your other posts and was really trying to avoid that because yes, I have no clue how to do that But being the Excel nerd that I am (I'd say 3rd degree to your 7th degree :P), I'll probably start looking into it now.

Long story short, I've resigned my self to adding calculated columns giving me the 'unit' for each month. But I took a short cut and only added four and created a macros to add columns as required

Thanks for the help