Help needed in Pivot table

[ashita]

Hi Alll,
I have a question in pivot table

<TABLE style="WIDTH: 250pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=333><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 84pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17 width=112>P.No#</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 61pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=81>serial_nbr</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 76pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl66 width=101>real_date</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 29pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=39>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17>66587124</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">AB9291</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right>1/4/2011 9:15</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right>1/6/2011 14:41</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right>1/24/2011 14:43</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>18</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right>1/26/2011 14:32</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right>3/8/2011 14:46</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>41</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 align=right>3/21/2011 13:53</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-ignore: colspan" colSpan=2>AB9291 Average</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67></TD></TR></TBODY></TABLE>

In the above example i calculated the diffrence between the dates in the total column.
What i am trying to get is average of (2,18,2,41,13) values in the row AB 9291 Average. Is it possible to get the average value? if not is there any other way to find the average of the differnce values because i have very large data set and i need to calucalte the avg days for all different serial numbers. Can any one please help me out in this.

Thanks
A****a

 

[Akashwani]

Hi A****a,

Welcome to MrExcel.

I'm not sure if this is the correct answer for you, but here goes.
In Excel 2007, Right click on the Grand Total figure, click Value Field Settings, select Average from the Summarize By box, click OK.

Take a look here at MrExcel doing Pivot Tables, type Pivot Table into the Search box.....

http://www.youtube.com/user/bjele123

Good luck

Ak