Average of column from a subset in a table

[guyinthetie]

I am using Excel 2007. I am using a table with headers.
I have about 500 rows of data with a column heading called CI (competitive index) - a number from 0.00 to 1.00.
I also have a column heading called category.
So my table looks something like:

<table border="0" cellpadding="0" cellspacing="0" width="423"><tbody><tr style="height:15.0pt" height="20"><td style="height:15.0pt;width:48pt;font-size:11.0pt; color:black;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid black; border-right:none;border-bottom:.5pt solid black;border-left:none" height="20" width="64">name</td> <td style="width:56pt;font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none" width="74">category</td> <td style="width:48pt;font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none" align="center" width="64">CI</td> <td style="width:62pt;font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none" align="center" width="83">averageCI</td> <td style="width:104pt;font-size:11.0pt;color:black;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid black;border-right:none;border-bottom: .5pt solid black;border-left:none" align="center" width="138">categoryaverageCI</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">Tom </td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none">friend </td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">0.5</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">1</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" height="20">Randy </td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri">foe</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">0.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">1.2</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">Bob </td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none">na</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">0.7</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">1.4</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" height="20">George</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri">friend </td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">0.8</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">1.6</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">Chip</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none">foe</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">0.9</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">1.8</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" height="20">Fred</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri">na</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">0.4</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">0.8</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">Alan</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none">friend </td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">0.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">0.6</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" height="20">Brady</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri">foe</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">0.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri" align="center">0.4</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:none;border-right:none;border-bottom:.5pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none" height="20">Rick</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:none;border-right:none;border-bottom:.5pt solid black;border-left: none;background:#D8D8D8;mso-pattern:#D8D8D8 none">na</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:none;border-right:none;border-bottom:.5pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">0.1</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:none;border-right:none;border-bottom:.5pt solid black; border-left:none;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">0.2</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:none;border-right:none;border-bottom:.5pt solid black;border-left: none;background:#D8D8D8;mso-pattern:#D8D8D8 none" align="center">
</td> </tr> </tbody></table>
I already have a working column that shows:
=MyTable[[#This Row],[CI]]/AVERAGE([CI]))

I would like a table that shows the average CI for each Row for that Row's Category
I've been trying versions of these two approaches:

=MyTable[[#This Row][Category],[CI]]/AVERAGE([CI])

=GETPIVOTDATA("CI",$N$2,"Group",MyTable[[#This Row],[CI]])

Thanks in advance for sharing your expertise.

 

[T. Valko]

I am using Excel 2007. I am using a table with headers.
I have about 500 rows of data with a column heading called CI (competitive index) - a number from 0.00 to 1.00.
I also have a column heading called category.
So my table looks something like:

<TABLE cellSpacing=0 cellPadding=0 width=423 border=0><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: medium none; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: medium none; WIDTH: 48pt; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" width=64 height=20>name</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: medium none; WIDTH: 56pt; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" width=74>category</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: medium none; WIDTH: 48pt; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle width=64>CI</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: medium none; WIDTH: 62pt; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle width=83>averageCI</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: medium none; WIDTH: 104pt; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle width=138>categoryaverageCI</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" height=20>Tom </TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none">friend </TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>0.5</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>1</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Randy </TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">foe</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>0.6</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>1.2</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" height=20>Bob </TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none">na</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>0.7</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>1.4</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>George</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">friend </TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>0.8</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>1.6</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" height=20>Chip</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none">foe</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>0.9</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>1.8</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Fred</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">na</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>0.4</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>0.8</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" height=20>Alan</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none">friend </TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>0.3</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>0.6</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Brady</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">foe</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>0.2</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>0.4</TD><TD style="FONT-WEIGHT: 400; FONT-SIZE: 11pt; COLOR: black; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=middle>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; BORDER-LEFT: medium none; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" height=20>Rick</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; BORDER-LEFT: medium none; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none">na</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; BORDER-LEFT: medium none; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>0.1</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; BORDER-LEFT: medium none; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>0.2</TD><TD style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #d8d8d8; BORDER-LEFT: medium none; COLOR: black; BORDER-BOTTOM: black 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #D8D8D8 none" align=middle>

</TD></TR></TBODY></TABLE>
I already have a working column that shows:
=MyTable[[#This Row],[CI]]/AVERAGE([CI]))

I would like a table that shows the average CI for each Row for that Row's Category
I've been trying versions of these two approaches:

=MyTable[[#This Row][Category],[CI]]/AVERAGE([CI])

=GETPIVOTDATA("CI",$N$2,"Group",MyTable[[#This Row],[CI]])

Thanks in advance for sharing your expertise.

Maybe this:

=AVERAGEIF([category],MyTable[[#This Row],[category]],[CI])

For the life of me, I don't see how anyone would prefer that string of gobbledygook over simple range addresses:

=AVERAGEIF(B$2:B$10,B2,C$2:C$10)

 

[guyinthetie]

I use tables and sort by column often. Columns with formula that reference range addresses don't work; they point to their old row address.

 

[guyinthetie]

This solved the issue. Thanks! Got it in 1 and a record response time.

=AVERAGEIF([category],MyTable[[#This Row],[category]],[CI])

 

[T. Valko]

This solved the issue. Thanks! Got it in 1 and a record response time.

You're welcome. Thanks for the feedback!