If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
averaging in a pivot table
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?? |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|