Hi,
Can I get the formula below into a PT please? I'm using Excel07.
=SUM(MAX(D2:D14)-SUM($B2:$C2))/2
I need two extra columns in the PT (Left and Right) and this is the formula I need, but I want to add it to a PT so I can run a chart from it.
The number of rows (D2:D14) will change depending on the report filter used.
<TABLE style="WIDTH: 371pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=493 border=0><COLGROUP><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" span=2 width=65><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 147pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=196 height=17>Payscale2</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=65>Female</TD><TD class=xl73 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 49pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=65>Male</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=74>Grand Total</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 21pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=28>Left</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 49pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=65>Right</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 1</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">25</TD><TD class=xl75 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">25</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">109</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">109</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 2</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">37</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">43</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">100</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 3</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">197</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">45</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">242</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 4</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">89</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">4</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">93</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">75</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">75</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 5</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">165</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">50</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">215</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">14</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 6</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">183</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">58</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">241</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">1</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 7</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">103</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">31</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">134</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">54</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">54</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 9</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">3</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">2</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">5</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">119</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">119</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 10</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">34</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">10</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">44</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">99</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">99</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 11</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">16</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">12</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">28</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">107</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 12</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">3</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">9</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">117</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">117</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 13</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">4</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">2</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">118</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">118</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Non Active</TD><TD class=xl72 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">36</TD><TD class=xl78 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">65</TD><TD class=xl79 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">101</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">71</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">71</TD></TR></TBODY></TABLE>
TIA,
Pete
Can I get the formula below into a PT please? I'm using Excel07.
=SUM(MAX(D2:D14)-SUM($B2:$C2))/2
I need two extra columns in the PT (Left and Right) and this is the formula I need, but I want to add it to a PT so I can run a chart from it.
The number of rows (D2:D14) will change depending on the report filter used.
<TABLE style="WIDTH: 371pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=493 border=0><COLGROUP><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" span=2 width=65><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 147pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=196 height=17>Payscale2</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=65>Female</TD><TD class=xl73 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 49pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=65>Male</TD><TD class=xl74 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=74>Grand Total</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 21pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=28>Left</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 49pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=65>Right</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 1</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">25</TD><TD class=xl75 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">25</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">109</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">109</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 2</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">37</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">43</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">100</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 3</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">197</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">45</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">242</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 4</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">89</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">4</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">93</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">75</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">75</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 5</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">165</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">50</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">215</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">14</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 6</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">183</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">58</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">241</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">1</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 7</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">103</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">31</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">134</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">54</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">54</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 9</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">3</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">2</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">5</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">119</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">119</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 10</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">34</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">10</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">44</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">99</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">99</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 11</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">16</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">12</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">28</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">107</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 12</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">3</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">9</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">117</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">117</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Band 13</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">4</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">2</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">118</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">118</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Non Active</TD><TD class=xl72 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">36</TD><TD class=xl78 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">65</TD><TD class=xl79 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">101</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">71</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">71</TD></TR></TBODY></TABLE>
TIA,
Pete
