Prev: IF Function Help
Next: Macro for Time Recording
From: Shadowkiller361 on 30 Apr 2010 22:29
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
From: Tom Hutchins on 3 May 2010 15:38
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" wrote:

> 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
From: Shadowkiller361 on 3 May 2010 17:17
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...

"Tom Hutchins" wrote:

> 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" wrote:
>
> > 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
 | 
Pages: 1
Prev: IF Function Help
Next: Macro for Time Recording