Average n numbers after the first not equal to zero

[omerfri]

Hi,
I need to average the first n (let's say 3) numbers in a column, from the first one that is not equal to zero.
For example:
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

In the left column, checking from down up, first there is a zero, and the average of the following 3 numbers is 2, so the answer is 2.
In the center column there is a zero, and then another zero that the formula need to ignore. The average of the three following is 5, so that's the answer. The answer for the right column is 3.

Please anybody can help me with a formula?+

EDIT:
Just one thing - i didnt mention - the checking of a non zero number is from down to up (from the bottom of the list).
Thanks again.

 

[James006]

Hi,

say you data is located in range A2:C6

You could list the letters A B C in cells E1, F1, G1

Then in cell E2 you could test following formula :

=AVERAGE(INDIRECT(E1&IF(ISERROR(MATCH(0,A1:A6,0)-1),MATCH(1E+30,A:A),MATCH(0,A1:A6,0)-1)-2&":"&E1&IF(ISERROR(MATCH(0,A1:A6,0)-1),MATCH(1E+30,A:A),MATCH(0,A1:A6,0)-1)))

And copy it to both B2 and C2 ...

Hope this will help