Returning average of the first N NON-zero values (incl neg values) of a column

[AIK8888]

Hello

New to the forum. I was wondering if somebody could be so kind as to help me with the following, which I have struggled with for hours now wihtout finding a solution to.

I am trying to derive a function which generates the average of the first N non-negative values of a columns.

Example column (where N=5):

0
2
3
0
0
0
-5
3
7
0
0
8

For the column above, the calculation would be [2+3+(-5)+3+7]/5 = 2

Many, many thanks in advance!

 

[AIK8888]

If you have a formula in the relevant range, let that formula not return real 0's. Return text e.g., NA instead.

Thanks Aladin - I will do that.
Would you also have any suggestions on how to adjust the formula if the range does not start in A1? Say B12 or whatever... Thank you!!

 

[Aladin Akyurek]

...obvisously just changing the cell references to A10:A50 "should" do it, but for some reason the calculations are off when I try that...

Is this what you want?

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(A2:A50) <= SMALL(IF(ISNUMBER(1/A2:A50),ROW(A2:A50)),MIN(C1,SUM(IF(ISNUMBER(1/A2:A50),1)))),IF(ISNUMBER(1/A2:A50),A2:A50)))

If you adopt NA instead of 0...

=AVERAGE(IF(ROW(A2:A50) <= SMALL(IF(ISNUMBER(A2:A50),ROW(A2:A50)),MIN(C1,SUM(IF(ISNUMBER(A2:A50),1)))),IF(ISNUMBER(1/A2:A50),A2:A50)))

 

[AIK8888]

Is this what you want?

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(A2:A50) <= SMALL(IF(ISNUMBER(1/A2:A50),ROW(A2:A50)),MIN(C1,SUM(IF(ISNUMBER(1/A2:A50),1)))),IF(ISNUMBER(1/A2:A50),A2:A50)))

If you adopt NA instead of 0...

=AVERAGE(IF(ROW(A2:A50) <= SMALL(IF(ISNUMBER(A2:A50),ROW(A2:A50)),MIN(C1,SUM(IF(ISNUMBER(A2:A50),1)))),IF(ISNUMBER(1/A2:A50),A2:A50)))

YES!!!

Wow. I am impressed. Thank you so, so much!!!!

 

[Aladin Akyurek]

YES!!!

Wow. I am impressed. Thank you so, so much!!!!

You are welcome.