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>

 

[Fencliff]

Hi,

I'm sure there are a number of ways to achieve this, but using SUMIF and INDIRECT formulas comes to mind first. I can't attach a workbook for example, so the explanation is a little hard.

Let's say that in the following formula your original data is in columns A:O, starting from row 1. The aggregation table starts in row 10 with headers, and column A contains the dates.

The first formula would be placed in cell B11:
=SUMIF($A$1:$O$1,B$10,INDIRECT("A"&MATCH($A11,$A:$A,FALSE)&":O"&MATCH($A11,$A:$A,FALSE)))

And filled down and right.

Let me know if you need a better explanation.

 

[Oaktree]

B13 =AVERAGE(IF(INT((MONTH($B$2:$O$2)+2)/3)=MID(B$12,2,1)+0,IF(YEAR($B$2:$O$2)=RIGHT(B$12,4)+0,$B3:$O3)))

note that this is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar).

Then, copy B13 down and right.

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Quarter->	Q1-2008	Q1-2008	Q2-2008	Q2-2008	Q2-2008	Q3-2008	Q3-2008	Q3-2008	Q4-2008	Q4-2008	Q4-2008	Q1-2009	Q1-2009	Q1-2009
2	date	8-Feb	8-Mar	8-Apr	8-May	8-Jun	8-Jul	8-Aug	8-Sep	8-Oct	8-Nov	8-Dec	9-Jan	9-Feb	9-Mar
3	1/1/2005	1	1	1	1	1	1	1	1	1	1	1	1	1	1
4	1/2/2005	2	3	4	5	6	7	8	9	10	11	13	14	15	16
5	1/3/2005	3	5	7	9	11	13	15	17	19	21	25	27	29	31
6	1/4/2005	4	7	10	13	16	19	22	25	28	31	37	40	43	46
7	1/5/2005	5	9	13	17	21	25	29	33	37	41	49	53	57	61
8
9
10
11
12	date	Q1-2008	Q2-2008	Q3-2008	Q4-2008	Q1-2009
13	1/1/2005	1	1	1	1	1
14	1/2/2005	2.5	5	8	11.3333	15
15	1/3/2005	4	9	15	21.6667	29
16	1/4/2005	5.5	13	22	32	43
17	1/5/2005	7	17	29	42.3333	57
18
Sheet1

 

[Sukotto]

The first formula would be placed in cell B11:
=SUMIF($A$1:$O$1,B$10,INDIRECT("A"&MATCH($A11,$A:$A,FALSE)&":O"&MATCH($A11,$A:$A,FALSE)))

I'll try it. Presumably I would still have to divide by some sort of COUNTIF() to get the average?

But first, let me see if I understand what you're suggesting

MATCH($A11,$A:$A,FALSE)

Look for the DATE in the current row in the aggregate in the table of Raw data using an exact match. Return the row number of that date. Say it returns row #16

So we get

INDIRECT("A"&"16"&":O"&"16")
>>
INDIRECT("A16:O16")

Make a reference to the range "A16:O16" >> POINTER_TO_ROW_WE_WANT_TO_AGGREGATE

SUMIF($A$1:$O$1,B$10,INDIRECT("A16:O16"))
>>
SUMIF($A$1:$O$1,B$10, POINTER_TO_ROW_WE_WANT_TO_AGGREGATE)

B$10 is the list of Quarters ... eg: Q3-2008
>>
SUMIF($A$1:$O$1,"Q3-2008", POINTER_TO_ROW_WE_WANT_TO_AGGREGATE)

Which means
Look in the Range "A1:O1" (List of Quarters in Raw data) for the Quarter named "Q3-2008" then add up all the values you find in the row pointed to by POINTER_TO_ROW_WE_WANT_TO_AGGREGATE.


Yes?

 

[Sukotto]

Whoa OakTree, I gotta get some of that

Using regex to build the sample tables just doesn't compare to the fancy excel-alike tables in your post.

 

[Sukotto]

Thanks to both of you for the quick replies.

Oaktree's code was drop-in-work so I'm going with that. Now picking it apart as best I can to try and figure out what it's actually doing. I'll need to extend my work for other time ranges (half year average, full year average, multi-year average) so I'll need a really solid understanding.

For the sake of those that come after me I'll write it up (assuming that Oaktree doesn't beat me to it)

 

[Oaktree]

Note that the formula I provided references the actual dates in row 2, not the Quarters in row 1.

If you have/want those Quarters listed, it would be easier and shorter to use something like:

B13 =AVERAGE(IF($B$1:$O$1=B$12,$B3:$O3)), which is still an array formula, copied down and right.

 

[Sukotto]

Thank you, that's extremely helpful

 

[Sukotto]

How do you suggest I modify that to handle the case where the date exceeds one or more of the months in the quarter?

<table border="1"><tbody><tr><td>Quarter-></td><td bgcolor="blue">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/30/2008</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/31/2008</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>2/1/2008</td><td bgcolor="yellow"> </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>2/2/2008</td><td bgcolor="yellow"> </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>2/3/2008</td><td bgcolor="yellow"> </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></tbody></table>

Where I would expect to end up with

<table border="1"><tbody><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/30/2008</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td></tr><tr><td>1/31/2008</td><td>2.5</td><td>5</td><td>8</td><td>11.33333333</td><td>15</td></tr><tr><td>2/1/2008</td><td bgcolor="yellow">5
</td><td>9</td><td>15</td><td>21.66666667</td><td>29</td></tr><tr><td>2/2/2008</td><td bgcolor="yellow">7</td><td>13</td><td>22</td><td>32</td><td>43</td></tr><tr><td>2/3/2008</td><td bgcolor="yellow">9
</td><td>17</td><td>29</td><td>42.33333333</td><td>57</td></tr></tbody></table>

Somehow add IF( <> "") somewhere?
.

 

[Oaktree]

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