Lookup formula question

[FJMD1003]

Hi all,

I'm trying to create a lookup formula that will enable me to populate the table below. Say, for instance, I don't have information for sep-10, I would like the formula to move across the array to the first month that there is data for, then return a three month average for from that date onwards in the Q1 cell and the end of the table. I'd then like the Q2 cell to create the next three month average using the next three months along.

For instance, Business A's 3 month average for Q1 would be Sep-10 to Nov -10, then Dec - 10 to Feb -11 for Q2 whereas Business C's average would be Jan-11 to Mar -11 for Q1, then April - 11 to June - 11 for Q2 etc etc.

Flexibility is the key, as the data may be retrospectively populated, and any graphs need to reflect amendments to prior months' data.

Any input very gratefully received!

Many thanks,

Fred

<TABLE style="WIDTH: 1275pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1699><COLGROUP><COL style="WIDTH: 48pt" span=16 width=64><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" span=2 width=156><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 5558" width=152><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68 height=20 width=64>Business</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Sep-10</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Oct-10</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Nov-10</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Dec-10</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Jan-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Feb-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Mar-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Apr-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>May-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Jun-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Jul-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Aug-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Sep-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Oct-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl71 width=64>Nov-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl66 width=54>Dec-11</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68 width=157>Q1 (Avg. Last 3 months)</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 117pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68 width=156>Q2 (Avg. Last 3 months)</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; WIDTH: 117pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl68 width=156>Q3 (Avg. Last 3 months)</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 114pt; BORDER-TOP: #7f7f7f 0.5pt solid; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl67 width=152>Q4 (Avg. Last 3 months)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl69 height=20>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>10%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>11%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>12%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>13%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>14%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>15%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>16%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>17%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>18%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>19%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>20%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>21%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>22%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>23%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>24%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>25%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl74></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl74></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl76></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl69 height=20>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>3%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>4%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>5%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>6%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>7%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>8%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>9%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>10%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>11%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>12%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>13%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>14%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl72 align=right>15%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>16%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl74></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl74></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl76></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl70 height=20>C</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>5%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>6%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>7%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>8%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>9%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>10%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>11%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>12%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>13%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>14%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl73 align=right>15%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 align=right>16%</TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f 0.5pt solid; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl77></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl77></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #7f7f7f; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl78></TD><TD style="BORDER-BOTTOM: #7f7f7f 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #f2f2f2; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #7f7f7f 0.5pt solid" class=xl79></TD></TR></TBODY></TABLE>

 

[Andrew Poulsom]

In R2:

=AVERAGE(OFFSET(INDEX($B2:$Q2,MATCH(TRUE,INDEX($B2:$Q2<>"",),FALSE)),0,(COLUMN(A1)-COLUMN($A1))*3,1,3))

copied down and across.