Excel and consecutive number

[Shadowkiller361]

Is there a way to have excel locate a set number of consecutive non-zero
numbers and give me an average of those numbers?
trying to get around this problem:
5 largest consecutive non-zero numbers of the last 10 non-zero terms
hope there is some help out there for me

 

[Tom Hutchins]

If I understand what you want correctly, here is one method. Assuming you
have a list of numbers in column A with the first number in row 2:

Enter this formula in B2 and copy down through all rows of data:
=IF(A3=0,0,IF(B2=10,10,B2+1))
This formula counts consectuive non-zero numbers in column A.

Enter this formula in C11 and copy down through all rows of data:
=IF(B11=10,AVERAGE(LARGE(A2:A11,{1,2,3,4,5})),"")
This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
This formula averages the 5 largest in every group of 10 consectuive
non-zero numbers.

Enter this formula in D1 to get your final result:
=INDIRECT("C" & MATCH(1E+300,$C:$C))
This formula returns the last number in column C (the final average computed).

Hope this helps,

Hutch

 

[Shadowkiller361]

Trying it right now with a small set of numbers starting in A2. numbers go
down to A11 in the order of 1,0,2,3, 4, 5, 6. 7, 8, 9. Entering in your
formula in B2 i'm getting a zero and with the zero formula #2 (C11 array)
doesn't show up and formula #3 in showing N/A in D1.
i must be doing something wrong but i don't know what it is...