averaging in a pivot table
 

time conversion wrote:
I have one column in a pivot table contains numerous Doctors. In an adjacent
column I have the minutes that it takes them to do a task.....some have more
then one instance of that task...for example:

Column A Column B Column C Column D
(average)
Doctor 1 Repair Bone 53 49.75
50
29
67

How can I write a formula to find that average for a very long list of
Doctors and tasks??

There's no need to write a formula based on your example (although you
could). Take Column D out out the picture. I'm assuming Columns A and B
are "row area" breaks. It looks like you have Time in the row area also
-- drag this to "data area". It will probably default to "Count of
Time". No matter, simply double click the "Count of Time" button (or
right click an actual count of time value and pick "Field Settings"),
and change it to summarize as Average.

If the subtotals are annoying, double click the "Doctor" button and
select "None" under Subtotals.

averaging in a pivot table
 

thanks smartin...but let me start this from scratch. I will choose for the
pivot table SURGEON......PROCEDURE....and at your suggestion I will place the
TIME in the DATA AREA. When I tried this.....all I received was a count of
procedures for each surgeon.

SURGEON PROCEDURE

Dr Smith Appendix 5
Dr Jones Gall Bladder 10
Dr White Broken Hip 8
Dr Blue Broken Arm 9

Ideally what I want to report on is the average case length time for each
procedure that the surgeon has done in the time period Im looking at.

"smartin" wrote:

time conversion wrote:
I have one column in a pivot table contains numerous Doctors. In an adjacent
column I have the minutes that it takes them to do a task.....some have more
then one instance of that task...for example:

Column A Column B Column C Column D
(average)
Doctor 1 Repair Bone 53 49.75
50
29
67

How can I write a formula to find that average for a very long list of
Doctors and tasks??

There's no need to write a formula based on your example (although you
could). Take Column D out out the picture. I'm assuming Columns A and B
are "row area" breaks. It looks like you have Time in the row area also
-- drag this to "data area". It will probably default to "Count of
Time". No matter, simply double click the "Count of Time" button (or
right click an actual count of time value and pick "Field Settings"),
and change it to summarize as Average.

If the subtotals are annoying, double click the "Doctor" button and
select "None" under Subtotals.

averaging in a pivot table
 

time conversion wrote:
thanks smartin...but let me start this from scratch. I will choose for the
pivot table SURGEON......PROCEDURE....and at your suggestion I will place the
TIME in the DATA AREA. When I tried this.....all I received was a count of
procedures for each surgeon.

Exactly as I predicted it would. The next part of my instruction was
double click the "Count of Time" button (or
right click an actual count of time value and pick "Field Settings"),
and change it to summarize as Average.

[snip sample]
Ideally what I want to report on is the average case length time for each
procedure that the surgeon has done in the time period Im looking at.

You are one step away from that.

When you use pivot tables, the key thing to remember is, fields you want
to do math on (sum, count, average, standard deviation, etc.) go in the
data area, and fields you want to do grouping, summarizing, or filtering
on go in page, row, or column areas.

averaging in a pivot table
 

Thanks smartin...that was an excellent tip regarding the pivot tables....I'll
let you know how that report turns out

"smartin" wrote:

time conversion wrote:
thanks smartin...but let me start this from scratch. I will choose for the
pivot table SURGEON......PROCEDURE....and at your suggestion I will place the
TIME in the DATA AREA. When I tried this.....all I received was a count of
procedures for each surgeon.

Exactly as I predicted it would. The next part of my instruction was
double click the "Count of Time" button (or
right click an actual count of time value and pick "Field Settings"),
and change it to summarize as Average.

[snip sample]
Ideally what I want to report on is the average case length time for each
procedure that the surgeon has done in the time period Im looking at.

You are one step away from that.

When you use pivot tables, the key thing to remember is, fields you want
to do math on (sum, count, average, standard deviation, etc.) go in the
data area, and fields you want to do grouping, summarizing, or filtering
on go in page, row, or column areas.