Aggregate multiple lookups (maybe "yet another multiple lookup" post)

[Sukotto]

<p>Given data like <b>Daily Data</b>
<table border="1">
<tr><td>Quarter-></td><td>Q1-2008</td><td bgcolor="blue">Q1-2008</td><td bgcolor="green">Q2-2008</td><td bgcolor="green">Q2-2008</td><td bgcolor="green">Q2-2008</td><td bgcolor="orange">Q3-2008</td><td bgcolor="orange">Q3-2008</td><td bgcolor="orange">Q3-2008</td><td bgcolor="aqua">Q4-2008</td><td bgcolor="aqua">Q4-2008</td><td bgcolor="aqua">Q4-2008</td><td>Q1-2009</td><td>Q1-2009</td><td>Q1-2009</td></tr>
<tr><td>date</td><td>Feb-08</td><td>Mar-08</td><td>Apr-08</td><td>May-08</td><td>Jun-08</td><td>Jul-08</td><td>Aug-08</td><td>Sep-08</td><td>Oct-08</td><td>Nov-08</td><td>Dec-08</td><td>Jan-09</td><td>Feb-09</td><td>Mar-09</td></tr>
<tr><td>1/1/2005</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr>
<tr><td>1/2/2005</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td><td>8</td><td>9</td><td>10</td><td>11</td><td>13</td><td>14</td><td>15</td><td>16</td></tr>
<tr><td>1/3/2005</td><td>3</td><td>5</td><td>7</td><td>9</td><td>11</td><td>13</td><td>15</td><td>17</td><td>19</td><td>21</td><td>25</td><td>27</td><td>29</td><td>31</td></tr>
<tr><td>1/4/2005</td><td>4</td><td>7</td><td>10</td><td>13</td><td>16</td><td>19</td><td>22</td><td>25</td><td>28</td><td>31</td><td>37</td><td>40</td><td>43</td><td>46</td></tr>
<tr><td>1/5/2005</td><td>5</td><td>9</td><td>13</td><td>17</td><td>21</td><td>25</td><td>29</td><td>33</td><td>37</td><td>41</td><td>49</td><td>53</td><td>57</td><td>61</td></tr>
</table>
(This data extends way out to the right and down... this is just a sample)
</p>
<p>I'm trying to get the Average for each date, by Quarter... like so
<b>Quarterly Data</b>
<table border="1">
<tr><td>date</td><td bgcolor="blue">Q1-2008</td><td bgcolor="green">Q2-2008</td><td bgcolor="orange">Q3-2008</td><td bgcolor="aqua">Q4-2008</td><td>Q1-2009</td></tr>
<tr><td>1/1/2005</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr>
<tr><td>1/2/2005</td><td>2.5</td><td>5</td><td>8</td><td>11.33333333</td><td>15</td></tr>
<tr><td>1/3/2005</td><td>4</td><td>9</td><td>15</td><td>21.66666667</td><td>29</td></tr>
<tr><td>1/4/2005</td><td>5.5</td><td>13</td><td>22</td><td>32</td><td>43</td></tr>
<tr><td>1/5/2005</td><td>7</td><td>17</td><td>29</td><td>42.33333333</td><td>57</td></tr>
</table>
Where each cell is "Average of all cells for the given date, in the given quarter"</p>

<p><b>It seems like I need to use some combination of MATCH and/or INDEX and/or OFFSET but I'm really having trouble getting my head around the Excel-way of doing things. Would someone please point me in the right direction?</b></p>

<p>The data will slowly change over time. (ie. the range of dates will change as the oldest ones fall off. Also the column headers will have the oldest fall off so I can not say that, for example, columns [B:D] will always be a specific quarter, nor even all in the same quarter)</p>

<p>I'm a experienced programmer (mostly perl and java) so I'm comfortable with things like references and arrays in general... Don't be afraid to use real programming lingo </p>

 

[Sukotto]

Just for my own understanding, and hopefully to help anyone else who comes across this later, here's what I think is going on here...

B13 =average(if($b$1:$o$1=b$12,if($b3:$o3<>"",$b3:$o3)))

Pseudocodes to

FOR EACH header IN range(B1:O1) DO
 IF value = B12.Value THEN
   PASS any non-blank values from range(B3:O3) into AVERAGE() 
    and put the result into B13
 END IF
END FOR

Note: the dollar signs are relevant if you want to copy this formula into other cells

Note: this formula must be entered with "CTRL-SHIRT-ENTER" key chord and not just "ENTER" since it's an array formula

 

[Sukotto]

[FIXED] Aggregate multiple lookups (maybe "yet another multiple lookup" post)

Hmm... I can't seem to edit the title of the thread anymore.
I wanted to add "FIXED".

Oh well