Limitation to % of column in PT?
 

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

Limitation to % of column in PT?
 

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" wrote in message
...
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

Limitation to % of column in PT?
 

On Mar 4, 6:45*pm, "Ashish Mathur" wrote:
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

Limitation to % of column in PT?
 

Hi,

Thank you very much. Glad I could help.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Pierre" wrote in message
...
On Mar 4, 6:45 pm, "Ashish Mathur" wrote:
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