Limitation to % of column in PT?

[Pierre]

Have numerous project numbers, labor hours by categories (such as
drafting, technician,, managment, etc.)
In the PT and reporting on a single project, the labor totals can be
broken into a "percentage of the total" by that category for that
project, as it relates to the total of hours in the PT.
However, if there are multiple projects in the database and more than
one project is summarized and totaled in the PT, then the "percentage
of column" will reflect a percentage; not of that item, but as a
percentage of "all" the projects.
I guess what we're looking for in the PT is a subtotal percentage for
that project, and every projects' percentage would be equal to 100%.

TIA for your thoughts.
Pierre

 

[Ashish Mathur]

Hi,

You can try this:

Suppose your data is set up as follows in range D7:F13:

Project No. Category Hours
1 WE 12
2 ER 23
3 WE 34
2 WE 45
5 WE 56
1 ER 67

in cell G7, type % and in cell G8, use the following formula
=F8/SUMIF($D$8:$D$13,D8,$F$8:$F$13) and copy down. Now create a simple
pivot table with Project No. and Category in the row area and hours & % in
the data area.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Pierre]

Hi,

You can try this:

Suppose your data is set up as follows in range D7:F13:

Project No.     Category                         Hours
1                            WE                    12
2                            ER                    23
3                            WE                    34
2                            WE                         45
5                            WE                   56
1                            ER                    67

in cell G7, type % and in cell G8, use the following formula
=F8/SUMIF($D$8:$D$13,D8,$F$8:$F$13) and copy down.  Now create a simple
pivot table with Project No. and Category in the row area and hours & % in
the data area.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

Ashish. . .you are a GENIUS!!

Thanks so very much.

Pierre

 

[Ashish Mathur]

Hi,

Thank you very much. Glad I could help.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com