Averging non-zero cells only

[Alienator]

Hi everyone,

I'll give an example to show you what I want to do. I have th
following data:

0
1.2179
0
0
0
0
0
0
1.2140
0
0
0
1.2115
1.2165

I want to average the first 3 numbers that are non-zero. So the resul
of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results o
these to be in the 1st row somewhere else.

The rows with zeros, and non-zeros keep changing, so you might get 1
0's in a row followed by 10 non-zero numbers. I just need to extrac
the first 3 non-zero numbers then average them

I'm good with the basic functions, but I can't figure this out

Any help at all would be most appreciated.

Thanks

Za

 

[N Harkawat]

A little convoluted but works

=AVERAGE((INDIRECT("A"&SMALL(IF(A1:A1000<>0,ROW(A1:A1000)),1))),INDIRECT("A"&SMALL(IF(A1:A1000<>0,ROW(A1:A1000)),2)),INDIRECT("A"&SMALL(IF(A1:A1000<>0,ROW(A1:A1000)),3)))
array entered(ctrl+shift+enter)
assuming that the data is in the range a1:a1000

 

[Guest]

Say the range you mentioned was into A1:A14, you can enter the following into
B1:
=IF(A1,1,0)
Then the following into B2:
=IF(SUM(B$1:B1)=3,0,IF(A2,1,0))
and copy it all the way down to B14.

Then in C1, use:
=SUMIF(B1:B14,1,A1:A14)/SUM(B1:B14)

It might not be ideal (you need an extra column), but it does the work.

If your list is very very long (say 1500 rows), the sum in column B might be
long to run towards the bottom. One way around it would be to change the
formula in B2 (and the ones below it) by:
=IF(B1=2,2,IF(SUM(B$1:B1)=3,2,IF(A2,1,0)))
===> Note that as soon as the sum of B1 to B_row-1_ is equal to 3, the
formula returns a 2 to that cell, and the cells below it will then
immediately return a 2 without passing by the sum(b$1:b_row-1_), accelerating
the process quite a bit. If you wanted to sum more than only the first 3
(say the first 100) non-zero cells, simply change the 3 for a 100 in that
formula.

Obviously, you will need to change C1 as well:
=SUMIF(B1:B14,1,A1:A14)/SUMIF(B1:B14,1)

Hope that helps,
Félix

 

[Guest]

See if one of these works for you:

With values (or blanks) in cells A1:A100

B1:
=SUMPRODUCT((ROW(A1:A100)=SMALL(((A1:A100<>0)*ROW(A1:A100))+((A1:A100=0)*10^10),{1,2,3}))*A1:A100)/3

OR

B1:
=SUMPRODUCT((ROW(A1:A100)=SMALL(((A1:A100<>0)*ROW(A1:A100))+((A1:A100=0)*10^10),{1,2,3}))*A1:A100)/MIN(3,SUMPRODUCT(--(A1:A100<>0)))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Domenic]

Here's another way...

=AVERAGE(SUBTOTAL(9,OFFSET(A1:A14,SMALL(IF(A1:A14>0,ROW(A1:A14)-MIN(ROW(A
1))),{1,2,3}),0,1)))

....confirmed with CONTROL+SHIFT+ENTER, not ENTER.

Hope this helps!