Excel 2007 : How to Get the Average of First 5 Non-Blank Cells in a Column?

Hello!

An example of my data set looks like this

colA B C D E F G
year (annual tree ring width....each column is an individual tree)
1780 0 0 0 1 0 0
1781 0 2 0 1 2 0
1782 3 4 0 5 3 4
1783 3 1 2 4 2 4
1784 3 3 3 3 3 3
1785 3 4 3 1 2 1
1786 5 2 1 3 2 1
etc..etc..etc

What I am trying to do is get the average of the first 5 non-blank data values in each column.

However, some columns have blank cells (0) before the actual data values start. How can I get an average of the first 5 non blank cells in each column?

(I want to know the average tree ring width over the first 5 years of growth for each tree)

Thanks for any help...have looked all over for this answer! I have at least 800 columns of data and def could use a short cut!

In B8 try...or wherever you data stops and adjust ranges

Enter with Crtl + Shift + Enter
=AVERAGE(IF(B$1:B$7<>0,B$1:B$7))

Edit: Sorry, just re-read the post and you want only the last five. This will average all outside of zero.

Let me ask, for column C, what should the answer be with your test data?

2.66 or 2.8

If it should be 2.8 then maybe this will work

Enter with Crtl + Shift + Enter
=AVERAGE(IF(OFFSET(C7,0,0,-5)<>0,OFFSET(C7,0,0,-5)))

I made the text table from A1:G7 with no headers. The formula above I have in C8

If you could attach a workbook with your test data and the expected results that would help tremendously.

Ok try this...

In B1 Enter with Crtl + Shift + Enter

=AVERAGE(IF(OFFSET(B3,MATCH(TRUE,INDEX(ISNUMBER(B3:B96),0),0)-1,0,5)<>0,OFFSET(B3,MATCH(TRUE,INDEX(ISNUMBER(B3:B96),0),0)-1,0,5)))

and drag to the right

If there are never blanks after the first number appears in a column (i.e. you have a reading for every year after that initial reading), then a slightly shorter array formula without using OFFSET:

B1: =AVERAGE(INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)):INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)+4))

This must be confirmed using CTRL+SHIFT+ENTER, not just ENTER. Copy to the right as needed.

Thanks Paul for the help.

I guess I made it a little over complicated, but in the end I did something I have never done before.

I knew the Offset was volatile and I should probably stay away from it but wasn't quite sure how. Now I do...

Post Removed