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!
Last edited by BEL20; 01-05-2011 at 12:39 PM.
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.
Last edited by jeffreybrown; 01-04-2011 at 04:12 PM.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Jeff!
you have no idea how awesome you are! I have spent 4 hours trying to figure this out!
THANK YOU! You have saved me days of work! Thank you for taking the time to help me!!!!!!!!!!
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
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
well,
the numbers I posted were just made up ....I wanted to make it easier to read. the actual numbers are posted below. My real data starts with the year 1633 and go to 2009. So I can have anywhere from no blank cells ( the oldest tree) up to 300 blank cells (the youngest tree) before getting to an actual data value. Impossible to post here. The value for column C would be 0.5196 or 0.52.
Column C
0.346
0.523
0.574
0.815
0.34
Okay...with column C using the original formula (=AVERAGE(IF(B$1:B$7<>0,B$1:B$7)))
I got 0.421 instead of 0.52. I will try the second one you suggested.
with the second formula (=AVERAGE(IF(OFFSET(C7,0,0,-5)<>0,OFFSET(C7,0,0,-5)))
I got 0.47325.
I will put up a shorter version with actual tree rings widths and the answers I am looking for. Thanks again!
If you could attach a workbook with your test data and the expected results that would help tremendously.
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
This is a portion of the actual data.
so RLD09a is a tree that started growing in 1928
and the average ring width (first five years) would be 0.3448
RLD09c started growing in 1944 with the average ring width being 0.888
The numbers in red are the answers I am trying to get.
the main goal is to estimate the initial (average) five years of growth for each tree. if the average is above 1.2 mm then it indicates the tree was growing in an opening. If it is less than 1.2 mm, the tree was suppressed (or had other trees blocking out sunlight above it).
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
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
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...
HTH
Regards, Jeff
If you like the answer(s) provided, why not add some reputation by clicking the * below
Please use [ Code ] tags when posting [ /Code ]
Please view/read the Forum rules --- How to mark a thread as solved
Thank you Jeff and Paul! I hope you have a wonderful new year!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks