I have only been able to write the formula for: Prospect/Time worked (or
Client/Time worked or Admin/Time worked) for a row of a pivot report...which
always returns 100% for each of the above categories. I am trying to get
Prospect Time as a percent of an employee's total time. Is there a different
calculation I should be using?

My pivot table reports an employee's time worked and categorized by client,
prospecting, admin. Then all the employee's time is summed at the employee
level. Each employee is summed to an office and the offices sum to a
department. Columns & Rows as follows

Dept Office Empl Client Total Time Client Prospect Admin
CS CM John AAA 40 40
CS CM John BBB 30 30
CS CM John CCC 20 20
CS CM Jane AAA 80 80
CS SF Tom CCC 50 50
CS SF Tina DDD 60 60

I would like to create a calculated field for each of client time, prospect,
admin as a percentage of the Employee's total time. Then, for the office,
the
sum of each of client time, prospect, admin as a percentage of all the
Office's Employees' Total Time. Then, for the Dept, the sum of each of
client time, prospect, admin as a percentage of all the Dept's Employees'
Total time.

If you can have a list of just rep names in say, column A of a sheet, then:

=sumproduct(--(pivot!$B$2:$B$10000=$A2),(pivot!$F$2:$F$10000))/sumproduct(--(pivot!$B$2:$B$10000=$A2),(pivot!$D$2:$D$10000))

sums prospect number totals per employee name then divides by total time per
employee name...

"HL Questions" wrote:

I have only been able to write the formula for: Prospect/Time worked (or
Client/Time worked or Admin/Time worked) for a row of a pivot report...which
always returns 100% for each of the above categories. I am trying to get
Prospect Time as a percent of an employee's total time. Is there a different
calculation I should be using?

My pivot table reports an employee's time worked and categorized by client,
prospecting, admin. Then all the employee's time is summed at the employee
level. Each employee is summed to an office and the offices sum to a
department. Columns & Rows as follows

Dept Office Empl Client Total Time Client Prospect Admin
CS CM John AAA 40 40
CS CM John BBB 30 30
CS CM John CCC 20 20
CS CM Jane AAA 80 80
CS SF Tom CCC 50 50
CS SF Tina DDD 60 60

I would like to create a calculated field for each of client time, prospect,
admin as a percentage of the Employee's total time. Then, for the office,
the
sum of each of client time, prospect, admin as a percentage of all the
Office's Employees' Total Time. Then, for the Dept, the sum of each of
client time, prospect, admin as a percentage of all the Dept's Employees'
Total time.

I don't think I was clear enough, I am trying identify the formula for
multiple % fields as part of "insert a calculated field" so that it will
result in the following example:

Dept Office Empl Client Total Time Client C% Prospect P%
Admin A%
CS CM John AAA 40 40 44%
CS CM John BBB 30 30 33%
CS CM John CCC 20 20 22%
John (a "SUM of" row) 90 40 44% 30
33% 20 22%

"Sean Timmons" wrote:

If you can have a list of just rep names in say, column A of a sheet, then:

=sumproduct(--(pivot!$B$2:$B$10000=$A2),(pivot!$F$2:$F$10000))/sumproduct(--(pivot!$B$2:$B$10000=$A2),(pivot!$D$2:$D$10000))

sums prospect number totals per employee name then divides by total time per
employee name...

"HL Questions" wrote:

I have only been able to write the formula for: Prospect/Time worked (or
Client/Time worked or Admin/Time worked) for a row of a pivot report...which
always returns 100% for each of the above categories. I am trying to get
Prospect Time as a percent of an employee's total time. Is there a different
calculation I should be using?

My pivot table reports an employee's time worked and categorized by client,
prospecting, admin. Then all the employee's time is summed at the employee
level. Each employee is summed to an office and the offices sum to a
department. Columns & Rows as follows

Dept Office Empl Client Total Time Client Prospect Admin
CS CM John AAA 40 40
CS CM John BBB 30 30
CS CM John CCC 20 20
CS CM Jane AAA 80 80
CS SF Tom CCC 50 50
CS SF Tina DDD 60 60

I would like to create a calculated field for each of client time, prospect,
admin as a percentage of the Employee's total time. Then, for the office,
the
sum of each of client time, prospect, admin as a percentage of all the
Office's Employees' Total Time. Then, for the Dept, the sum of each of
client time, prospect, admin as a percentage of all the Dept's Employees'
Total time.