Trying to average average from several non-contiguous columns

[eschr]

What I am trying to accomplish is this:
I have construction quantities in every third column (beginning with Col N) that I wish to group in together and average based on 3 size catagroies (Small, Med & Large). Right now I have the upper limit of the Small and the lower limit of the Large (anything between will be Medium) in 2 columns (I & J) which may need to be adjusted in the future. Each row will have unique limits.
It seems easy enough...but I've been spinning my wheels for 2 days. It's the skipping columns that's getting me stuck.
Thanks.

 

[cornflakegirl]

Hi, and welcome to the board.

Could you post a representative sample of your data using Excel Jeanie, and explain the result(s) that you would like you see?

 

[Kaps_mr2]

do a search on sum alternate columns and you'll get some good pointers

 

[eschr]

cornflakegirl:

Here's what I have. What I'd ultimately like to do is have the user choose the Project Size and Age of Data (via dropdown choices) and the spreadsheet would calc the average unit cost for each Description (based on Size and Age). I have range names for both of those set up. The Ages would be calculated from row 1 vs. the current year. Project size would be dependent upon the limits set (which may be changed in the future) in Col G&H. The quantities ("QNTY") will fall within one of 3 ranges - smaller than Col G or larger than Col H else neither (Medium). Each row of data will have unique size limits.
As new data comes in, a new set of columns will be inserted between J&K (always keeping the most current furthest to the left - ie most visible). Many more rows will be added too.
If at all possible, I'd like to stick with functions rather than macros.
Thanks for your help.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 26px"><COL style="WIDTH: 199px"><COL style="WIDTH: 41px"><COL style="WIDTH: 99px"><COL style="WIDTH: 70px"><COL style="WIDTH: 73px"><COL style="WIDTH: 58px"><COL style="WIDTH: 38px"><COL style="WIDTH: 47px"><COL style="WIDTH: 75px"><COL style="WIDTH: 98px"><COL style="WIDTH: 72px"><COL style="WIDTH: 75px"><COL style="WIDTH: 79px"><COL style="WIDTH: 73px"><COL style="WIDTH: 81px"><COL style="WIDTH: 89px"><COL style="WIDTH: 73px"><COL style="WIDTH: 81px"><COL style="WIDTH: 83px"><COL style="WIDTH: 73px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></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>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD></TR><TR style="HEIGHT: 24px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD style="TEXT-ALIGN: right">Project Year:</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=3>2008</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=3>2007</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=3>2007</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=12>2002</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD style="TEXT-ALIGN: right">Project No.:</TD><TD style="COLOR: #0000ff; TEXT-ALIGN: center; TEXT-DECORATION: underline" colSpan=3 rowSpan=2>JMB#932</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=3 rowSpan=2>JMB#1216B</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=3 rowSpan=2>JMB# ???</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=12 rowSpan=2>JMB# 935 Bloom</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD>Choose Size of Project:</TD><TD style="BACKGROUND-COLOR: #99cc00">Small</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" colSpan=2>To be hidden</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt"> </TD><TD style="TEXT-ALIGN: right">Project Name:</TD><TD style="FONT-SIZE: 14pt; TEXT-ALIGN: center" colSpan=3 rowSpan=2>Hampden Twp. Center</TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD>Choose Age of Data:</TD><TD style="BACKGROUND-COLOR: #99cc00">Last 2 years</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD><TD style="FONT-SIZE: 14pt"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">AVG</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">S</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">L</TD><TD style="FONT-WEIGHT: bold"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center" colSpan=3>Average from Bids</TD><TD style="TEXT-ALIGN: center" colSpan=3>Lib Ex</TD><TD style="TEXT-ALIGN: center" colSpan=3>WWK</TD><TD style="TEXT-ALIGN: center" colSpan=3>DEB</TD><TD style="TEXT-ALIGN: center" colSpan=3>SI</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">DESCRIPTION</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNITS</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNIT COST</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Upper Limit</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Lower Limit</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">QNTY</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNIT</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">TOTAL</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">QNTY</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNIT</TD><TD style="FONT-WEIGHT: bold">TOTAL</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">QNTY</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNIT</TD><TD style="FONT-WEIGHT: bold">TOTAL</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">QNTY</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNIT</TD><TD style="FONT-WEIGHT: bold">TOTAL</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">QNTY</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNIT</TD><TD style="FONT-WEIGHT: bold">TOTAL</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">QNTY</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UNIT</TD><TD style="FONT-WEIGHT: bold">TOTAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left; TEXT-DECORATION: underline">Grading and Excavation</TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </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><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD>Strip TS to Stock for Respread</TD><TD>CY</TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">5000</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">100000</TD><TD> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD style="TEXT-ALIGN: right">56,996</TD><TD style="TEXT-ALIGN: right">$2.44</TD><TD style="TEXT-ALIGN: right">$139,070.24</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: right">999</TD><TD style="TEXT-ALIGN: right">$5.00</TD><TD style="TEXT-ALIGN: right">$4,995.00</TD><TD style="TEXT-ALIGN: right">20,901</TD><TD style="TEXT-ALIGN: right">$2.00</TD><TD style="TEXT-ALIGN: right">$41,802.00</TD><TD style="TEXT-ALIGN: right">12,345</TD><TD style="TEXT-ALIGN: right">$12.34</TD><TD style="TEXT-ALIGN: right">$152,337.30</TD><TD style="TEXT-ALIGN: right">987</TD><TD style="TEXT-ALIGN: right">$6.00</TD><TD style="TEXT-ALIGN: right">$5,922.00</TD><TD style="TEXT-ALIGN: right">543</TD><TD style="TEXT-ALIGN: right">$2.10</TD><TD style="TEXT-ALIGN: right">$1,140.30</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD>Strip TS to Fill Wetland Area</TD><TD>CY</TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">4,945</TD><TD style="TEXT-ALIGN: right">$4.00</TD><TD style="TEXT-ALIGN: right">$19,780.00</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD>Respread TS @ Bldg</TD><TD>CY</TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">7,145</TD><TD style="TEXT-ALIGN: right">$10.00</TD><TD style="TEXT-ALIGN: right">$71,450.00</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD> </TD><TD>Respread TS @ Basin/Lawn</TD><TD>CY</TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">13,756</TD><TD style="TEXT-ALIGN: right">$2.00</TD><TD style="TEXT-ALIGN: right">$27,512.00</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </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>M8</TD><TD>=IF(K8=0,"",K8*L8)</TD></TR><TR><TD>P8</TD><TD>=IF(N8=0,"",N8*O8)</TD></TR><TR><TD>S8</TD><TD>=IF(Q8=0,"",Q8*R8)</TD></TR><TR><TD>M9</TD><TD>=IF(K9=0,"",K9*L9)</TD></TR><TR><TD>P9</TD><TD>=IF(N9=0,"",N9*O9)</TD></TR><TR><TD>S9</TD><TD>=IF(Q9=0,"",Q9*R9)</TD></TR><TR><TD>V9</TD><TD>=IF(T9=0,"",T9*U9)</TD></TR><TR><TD>Y9</TD><TD>=IF(W9=0,"",W9*X9)</TD></TR><TR><TD>AB9</TD><TD>=IF(Z9=0,"",Z9*AA9)</TD></TR><TR><TD>M10</TD><TD>=IF(K10=0,"",K10*L10)</TD></TR><TR><TD>P10</TD><TD>=IF(N10=0,"",N10*O10)</TD></TR><TR><TD>S10</TD><TD>=IF(Q10=0,"",Q10*R10)</TD></TR><TR><TD>V10</TD><TD>=IF(T10=0,"",T10*U10)</TD></TR><TR><TD>Y10</TD><TD>=IF(W10=0,"",W10*X10)</TD></TR><TR><TD>AB10</TD><TD>=IF(Z10=0,"",Z10*AA10)</TD></TR><TR><TD>M11</TD><TD>=IF(K11=0,"",K11*L11)</TD></TR><TR><TD>P11</TD><TD>=IF(N11=0,"",N11*O11)</TD></TR><TR><TD>S11</TD><TD>=IF(Q11=0,"",Q11*R11)</TD></TR><TR><TD>M12</TD><TD>=IF(K12=0,"",K12*L12)</TD></TR><TR><TD>P12</TD><TD>=IF(N12=0,"",N12*O12)</TD></TR><TR><TD>S12</TD><TD>=IF(Q12=0,"",Q12*R12)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 70px"><COL style="WIDTH: 138px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; TEXT-ALIGN: center">Size</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; TEXT-ALIGN: center">Age</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">Small</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">This year only</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">Med.</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">Last 2 years</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">Large</TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">Last 3 years</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD style="COLOR: #ff0000; TEXT-ALIGN: center">All years</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[eschr]

Can anyone help with the above issue? Thanks.

 

[cornflakegirl]

eschr - are you still trying to do this? You're trying to put a result in column F, right? Can you specify what the results should be for the rows shown, so that we're working from the same basis? Are you also saying that you will continue to add columns of data? Is there a maximum number of columns of data?

 

[eschr]

Sorry for the delay – on vacation.
Yes the result will appear in Col F. For the example, the user chose a “small” project and wants to use data from the “last 2 years” (current year and one year old). So only data from 2008 and 2007 will be used which can be found in the 1<SUP>st</SUP> col of each data set (each set is 3 cols wide). The 2002 Project Year is incorrectly shown – there should actually be 3 separate 2002 labels shown there – this can be ignored for now.
In row 9, the upper limit of a “Small Project” is set at 5000 and the lower limit of a “Large Project” is set at 100000 (everything between 5000 and 100000 will be a "Medium Sized Project". Therefore, to get the result in Col F: Only the 2008 and 2007 data sets with quantities (“QNTY”) less than 5000 will be averaged. In this case, only QNTY=999 fits both conditions. So the result is 999.
I hadn’t entered any other project size limits in cols G&H, therefore no result can be found in Col F.

Yes more columns will be inserted/added between J & K as new data comes in - always keeping the most recent info on the left side of the data. I'd like to have about 15 data sets minimum - this is not set in stone though.
Thanks.