Formula using a named range, where name is in a cell

[AusSteelMan]

Hi everyone,

Problem:
Would like to be able to build a formula where one of the cell reference contains the Name of the range I would like to use in the formula.

Context:
I have a real workbook which has 32 series of values which were collected with a process control system logger at 1 minute intervals for 365 days (yes 525600 rows of data). I need to make some sense of the data. I have a second worksheet were I can extract hourly and daily averages for a selected month (much more manageable chunks of data). I would now like to interogate this data set for certain parameters, such as Max and Min for example.
As the length of the data set varies (days in month, for example), I have named the series dynamically so the length only contains values and no #N/As. I have a strict naming convention for these Names.

I have attached a mock worksheet of the data set which shows the range names and how I would like to use them in the formulae.

Is this possible?
(I realise, while I set this thread up that I could have copied and edited the formulas individually, but this may be useful in the future since I usually have a strict - basically formula derived - naming convention.

Mock_Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 141px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Mock_Sheet1</TD><TD></TD><TD>Max</TD><TD>=max(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">10</TD><TD>#REF!</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD>Min</TD><TD>=min(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD>Average</TD><TD>=average(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">6.58333333</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">Dynamic Range Name</TD><TD style="BACKGROUND-COLOR: #99cc00">rngdyn_Pen1</TD><TD>rngdyn_Pen2</TD><TD>rngdyn_Pen3</TD><TD>rngdyn_Pen4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Data point</TD><TD></TD><TD>Pen</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">7</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">9</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">11</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">12</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">4</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E1</TD><TD>=MAX(E9:E20)</TD></TR><TR><TD>F1</TD><TD>=MAX(INDIRECT(F6))</TD></TR><TR><TD>E2</TD><TD>=MIN(E9:E20)</TD></TR><TR><TD>E3</TD><TD>=AVERAGE(E9:E20)</TD></TR><TR><TD>D6</TD><TD>="rngdyn_"&$C$8&D8</TD></TR><TR><TD>E6</TD><TD>="rngdyn_"&$C$8&E8</TD></TR><TR><TD>F6</TD><TD>="rngdyn_"&$C$8&F8</TD></TR><TR><TD>G6</TD><TD>="rngdyn_"&$C$8&G8</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Note: "Formulae" in D1 to D3 is formatted as Text


I tried INDIRECT and fished around for other solutions, but with no luck (obliviously).

Any help, even confirmation it cannot be done, would be greatly appreciated.

Thanks and regards,
Darren

 

[AusSteelMan]

Hi again,

mikerickson,
Thankyou for your input, I will have a go at working this method too.

JKP,
Thanks for the tip on Tables. Not only did it make everything much more simple, I also found in further testing that it made the results accurate.
The original results were accurate ONLY IF there were values for every data point. The Table method produced accurate results no matter the amount of data point values.





As you can see below:

• Added 3 columns of data (Pens 7, 8 & 9) and made a table of ONLY this new data
• I have added COUNT to my list of desired results at the top (ROW4).
• Deleted some data from Pens 4 and 6 (PEN4 is the completely manually set range, PEN6 is dynamic)
• Data in PEN7 is the same as PEN5 - and so are the results
• Data in PEN9 is the same as PEN6 - but only PEN9 has correct results. Even though I knew about OFFSET dynamic range limitations, my MockSheet data point values did not contain any blank cells. When it does, the results are wrong.

Mock_Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 141px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 88px"><COL style="WIDTH: 88px"><COL style="WIDTH: 88px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Mock_Sheet1</TD><TD></TD><TD>Max</TD><TD>=max(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">10</TD><TD>#REF!</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD>Min</TD><TD>=min(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">2</TD><TD>#REF!</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD>Average</TD><TD>=average(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">6.58333333</TD><TD>#REF!</TD><TD style="TEXT-ALIGN: right">4.81818182</TD><TD style="TEXT-ALIGN: right">4.08333333</TD><TD style="TEXT-ALIGN: right">6.1</TD><TD style="TEXT-ALIGN: right">4.083333333</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5.636363636</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD>Count</TD><TD>=count(rngdyn_Pen1)</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">Dynamic Range Name</TD><TD style="BACKGROUND-COLOR: #99cc00">rngdyn_Pen1</TD><TD>rngdyn_Pen2</TD><TD>rngdyn_Pen3</TD><TD>rng_Pen4</TD><TD>rngdyn_Pen5</TD><TD>rngdyn_Pen6</TD><TD>rngtbl_Pen7</TD><TD>_8</TD><TD>_9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Same as Pen5</TD><TD></TD><TD>Same as Pen6</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Data point</TD><TD></TD><TD>Pen</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD></TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">8</TD><TD></TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4</TD><TD></TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">7</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">4</TD><TD></TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">9</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD></TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">11</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">2</TD><TD></TD><TD style="TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">12</TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">10</TD><TD></TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">6</TD><TD></TD><TD style="TEXT-ALIGN: center">1</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E1</TD><TD>=MAX(E9:E20)</TD></TR><TR><TD>F1</TD><TD>=MAX(INDIRECT(F$6))</TD></TR><TR><TD>G1</TD><TD>=MAX(INDIRECT(G$6))</TD></TR><TR><TD>H1</TD><TD>=MAX(MyDynRng_Max)</TD></TR><TR><TD>I1</TD><TD>=MAX(MyDynRng_Max)</TD></TR><TR><TD>J1</TD><TD>=MAX(INDIRECT(J$6))</TD></TR><TR><TD>K1</TD><TD>=MAX(INDIRECT(K$6))</TD></TR><TR><TD>L1</TD><TD>=MAX(INDIRECT(L$6))</TD></TR><TR><TD>E2</TD><TD>=MIN(E9:E20)</TD></TR><TR><TD>F2</TD><TD>=MIN(INDIRECT(F$6))</TD></TR><TR><TD>G2</TD><TD>=MIN(INDIRECT(G$6))</TD></TR><TR><TD>H2</TD><TD>=MIN(MyDynRng_Min)</TD></TR><TR><TD>I2</TD><TD>=MIN(MyDynRng_Min)</TD></TR><TR><TD>J2</TD><TD>=MIN(INDIRECT(J$6))</TD></TR><TR><TD>K2</TD><TD>=MIN(INDIRECT(K$6))</TD></TR><TR><TD>L2</TD><TD>=MIN(INDIRECT(L$6))</TD></TR><TR><TD>E3</TD><TD>=AVERAGE(E9:E20)</TD></TR><TR><TD>F3</TD><TD>=AVERAGE(INDIRECT(F$6))</TD></TR><TR><TD>G3</TD><TD>=AVERAGE(INDIRECT(G$6))</TD></TR><TR><TD>H3</TD><TD>=AVERAGE(MyDynRng_Average)</TD></TR><TR><TD>I3</TD><TD>=AVERAGE(MyDynRng_Average)</TD></TR><TR><TD>J3</TD><TD>=AVERAGE(INDIRECT(J$6))</TD></TR><TR><TD>K3</TD><TD>=AVERAGE(INDIRECT(K$6))</TD></TR><TR><TD>L3</TD><TD>=AVERAGE(INDIRECT(L$6))</TD></TR><TR><TD>E4</TD><TD>=COUNT(E9:E20)</TD></TR><TR><TD>F4</TD><TD>=COUNT(INDIRECT(F$6))</TD></TR><TR><TD>G4</TD><TD>=COUNT(INDIRECT(G$6))</TD></TR><TR><TD>H4</TD><TD>=COUNT(MyDynRng_Count)</TD></TR><TR><TD>I4</TD><TD>=COUNT(MyDynRng_Count)</TD></TR><TR><TD>J4</TD><TD>=COUNT(INDIRECT(J$6))</TD></TR><TR><TD>K4</TD><TD>=COUNT(INDIRECT(K$6))</TD></TR><TR><TD>L4</TD><TD>=COUNT(INDIRECT(L$6))</TD></TR><TR><TD>E6</TD><TD>="rngdyn_"&$C$8&E8</TD></TR><TR><TD>F6</TD><TD>="rngdyn_"&$C$8&F8</TD></TR><TR><TD>G6</TD><TD>="rng_"&$C$8&G8</TD></TR><TR><TD>H6</TD><TD>="rngdyn_"&$C$8&H8</TD></TR><TR><TD>I6</TD><TD>="rngdyn_"&$C$8&I8</TD></TR><TR><TD>J6</TD><TD>="rngtbl_"&$C$8&J8</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Names 2011-Nov-22


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 119px"><COL style="WIDTH: 334px"><COL style="WIDTH: 48px"><COL style="WIDTH: 77px"><COL style="WIDTH: 45px"><COL style="WIDTH: 82px"><COL style="WIDTH: 56px"><COL style="WIDTH: 45px"><COL style="WIDTH: 42px"><COL style="WIDTH: 38px"><COL style="WIDTH: 77px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">RefersToLocal</TD><TD style="FONT-WEIGHT: bold">Visible</TD><TD style="FONT-WEIGHT: bold">Local name</TD><TD style="FONT-WEIGHT: bold">Error</TD><TD style="FONT-WEIGHT: bold">External link</TD><TD style="FONT-WEIGHT: bold">Relative</TD><TD style="FONT-WEIGHT: bold">3-D</TD><TD style="FONT-WEIGHT: bold">Areas</TD><TD style="FONT-WEIGHT: bold">Cells</TD><TD style="FONT-WEIGHT: bold">UnUsed</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>_8</TD><TD>=Mock_Sheet1!$K$9:$K$20</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>_9</TD><TD>=Mock_Sheet1!$L$9:$L$20</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>MyDynRng_Average</TD><TD>=EVALUATE(INDIRECT("R[3]C",0))</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>MyDynRng_Count</TD><TD>=EVALUATE(INDIRECT("R[2]C",0))</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>MyDynRng_Max</TD><TD>=EVALUATE(INDIRECT("R[5]C",0))</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>MyDynRng_Min</TD><TD>=EVALUATE(INDIRECT("R[4]C",0))</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>rng_Pen4</TD><TD>=Mock_Sheet1!$G$9:$G$20</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>rngdyn_Pen3</TD><TD>=OFFSET(Mock_Sheet1!$F$8,1,0,COUNT(Mock_Sheet1!$F$9:$F$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>rngdyn_Pen5</TD><TD>=OFFSET(Mock_Sheet1!$H$8,1,0,COUNT(Mock_Sheet1!$H$9:$H$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>rngdyn_Pen6</TD><TD>=OFFSET(Mock_Sheet1!$I$8,1,0,COUNT(Mock_Sheet1!$I$9:$I$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">11</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>rngdyn_Pen7</TD><TD>=OFFSET(Mock_Sheet1!$J$8,1,0,COUNT(Mock_Sheet1!$J$9:$J$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 40px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>rngdyn_Pen8</TD><TD>=OFFSET(Mock_Sheet1!$K$8,1,0,COUNT(Mock_Sheet1!$K$9:$K$30),1)</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD>Not checked</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>rngtbl_Pen7</TD><TD>=Mock_Sheet1!$J$9:$J$20</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Not checked</TD></TR></TBODY></TABLE>


The range names for Pens 7, 8 & 9 are located within the defined table. As you described, the range is automatically resized with the table size.
Now the COUNT is correct for any series that has missing data points, therefore the others calcs are also correct.

Now that I have some confidence in what I'm doing will provide quality results, I will have a shot at the real data set. Although this data set is actually truncated from the original (as described in the first post).
The one I will use has only 1 1/2 months of 1 minute interval readings for 32 series.
I foresee some problems around table size as I plan only being able to select a period of time (eg data for September) that will extract data from the raw data and populate cells on a new sheet (hence the use of dynamic ranges). Right now I am thinking that I could just get away having a large table area that will fit the maximum amount of data I would want to return at once. This would also cater for any smaller amounts of data. That is, I think the table will work just fine with blank cells.
Anyway, I digress. If I can't work them out myeslf, I'm sure I'll be back for some more advice.

By the way, thanks too for Name Manager. It has been a handy utility over the time.


Thankyou jkpieterse, mikerickson and anyone else who pondered a solution for me too. Greatly appreciated.

Darren

 

[jkpieterse]

Now that you've converted your range to a table, why not get rid of the dynamic range names alltogether, they seem wrong anyway?

 

[AusSteelMan]

Jan Karel,
I will be removing all the dynamic ranges.
For this mock-up book, I simply added new columns for Pens 7, 8 & 9 and made them the Table. I left the rest as they were for posterity while I determined the best way to work on the data.

For the real workbook, I will only have the table and each pen will have a named range that will grow & shrink as required if I need to change the table size.

The main reason I listed the range names in row 6, is so that I could use the INDIRECT fx to build the formulae for MAX, MIN & AVERAGE and simply copy across all the Pens (since the real book will have 32 pens) I could have just written each formula individually (it is only 32 formulae x the number of results I need (Max, Min, Average etc)). However, I thought I'd see if I could figure out how to do it more efficiently, so I tried INDIRECT. That didn't work for dynamic ranges, so I asked here. one thing lead to another and now I think that I have a better and more efficient way of getting the results through the use of tables and regular ranges that become dynamic if the table size changes.

If I got that last paragraph right, then I think I have learnt something! yay!

Even still, I figure if I put the name of each range above the range itself, then I can use INDIRECT to use that name in each formulae with out having to write each one. That is my plan anyway - I hope it works.
Did that all make sense?

Regards,
Darren

 

[jkpieterse]

Just a small tip: if you're building the file in Excel 2007, make sure you do not use "structured referencing" (the new table referencing syntax) when defining names, but just regular cell references.