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!

 

[steve the fish]

Try:

=SUM(A1:INDEX(A1:A100,SMALL(IF(A1:A100<>0,ROW(A1:A100)),C1),0))/C1

Enter CTRL-SHIFT-ENTER

 

[AIK8888]

Thanks a lot Steve - works perfectly and saved my day!!

 

[steve the fish]

Your question was ambiguous in that the title asked for non zero and the question itself asked for non negative. The formula is for non zero.

 

[AIK8888]

I see what you mean. The reason I said "including neg values" is due to the fact that NON-zero is sometimes incorrectly mis-interpreted as ">0". What I need is something that excludes zero only, which your formula does.
Thanks again.

 

[Aladin Akyurek]

But is it not unsound to leave out real 0's, while average of numbers > 0 and average of numbers < 0 are admissible?

 

[AIK8888]

Good point. It could be, but not necessarily so. In my case, the output cannot equal zero (and I suppose you could also "cheat" with this function if the probability was extremely small to be exactly 0).

 

[AIK8888]

I just realized that you cannot just change the range in the formula to reflect the actual range in the spreadsheet. For example, if my column values are listed in A10:A50, then how would I adjust the formula?

Thanks again in advance!

 

[AIK8888]

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

 

[Aladin Akyurek]

Good point. It could be, but not necessarily so. In my case, the output cannot equal zero (and I suppose you could also "cheat" with this function if the probability was extremely small to be exactly 0).

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