Simple pivot table question

B

bkissick

New Member
Joined
Oct 23, 2009
Messages
32
I am building a simple pivot table off of a list of imported data (date something happened, date it was resolved) with a calculated field (time to resolve) included. I would like the pivot table to display, for a given item, the average time to resolve within that item type. I don't see a way to make the table calculate the average days, only give the sum of days per item type.Is there an easy answer to this problem? Thanks!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
If you double click on the header labled "Sum of Difference", it should give you a dialog box labled "value field settings" that you can choose "average of difference."
 
Upvote 0
Since you're using 2 different fields, I don't believe you will be able to calculate the average of a TYPE & populate it on each item using a pivot table. Is there a reason you need to use a pivot table to calculate this?

Why couldn't you use the data sheet & add a columns w/ formulas calculating the length of time to resolve and average resolution time FOR TYPE as shown in formula below?

<TABLE style="WIDTH: 399pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=531 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Ticket</TD><TD class=xl63 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=64>Type</TD><TD class=xl63 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 50pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=66>Date Rec'd</TD><TD class=xl63 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 63pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=84>Date Resolved</TD><TD class=xl63 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 68pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=90>Days to resolve</TD><TD class=xl63 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 122pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=163>Avg days to resolve for Type</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1234</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">Repair</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">1/1/2011</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">1/5/2011</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">4</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5678</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">Repair</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">2/3/2011</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">2/10/2011</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">7</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>9101</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">Repair</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">1/15/2011</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">1/25/2011</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">10</TD><TD class=xl64 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">=AVERAGEIF($B:$B,B4,$E:$E)</TD></TR></TBODY></TABLE>

You could then create a pivot table based on all of these field if you need to summarize by Type.
 
Upvote 0
Haldoug said:
If you double click on the header labled "Sum of Difference", it should give you a dialog box labled "value field settings" that you can choose "average of difference."
Click to expand...

Haldoug, this appears to be just what I was looking for. Not sure why I didn't see it myself, but I'm a relative noob when it comes to pivot tables. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
pivot table formatting error
Replies
1
Views
309
GraH
GraH
SandsB
  • Question Question
Pivot Table showing just averages
Replies
4
Views
146
SandsB
SandsB
M
  • Question Question
Mass Change of Pivot "Values" from Sum to Average
Replies
4
Views
573
RoryA
RoryA
SandsB
  • Question Question
Chart Type changes when updated
Replies
0
Views
68
SandsB
SandsB
N
  • Question Question
Column with Min/Average values at the end of Pivot Table
Replies
0
Views
158
Nurces
N

Forum statistics

Threads
1,224,550
Messages
6,179,463
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top