Averging non-zero cells only
 

Hi everyone,

I'll give an example to show you what I want to do. I have the
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 result
of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of
these to be in the 1st row somewhere else.

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

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

Any help at all would be most appreciated.

Thanks

Zak


--
Alienator
------------------------------------------------------------------------
Alienator's Profile: http://www.excelforum.com/member.php...o&userid=31013
View this thread: http://www.excelforum.com/showthread...hreadid=506866

Averging non-zero cells only
 

A little convoluted but works

=AVERAGE((INDIRECT("A"&SMALL(IF(A1:A1000<0,ROW(A1 :A1000)),1))),INDIRECT("A"&SMALL(IF(A1:A1000<0,RO W(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


"Alienator" wrote
in message ...

Hi everyone,

I'll give an example to show you what I want to do. I have the
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 result
of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of
these to be in the 1st row somewhere else.

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

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

Any help at all would be most appreciated.

Thanks

Zak


--
Alienator
------------------------------------------------------------------------
Alienator's Profile:
http://www.excelforum.com/member.php...o&userid=31013
View this thread: http://www.excelforum.com/showthread...hreadid=506866

Averging non-zero cells only
 

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


"Alienator" wrote:


Hi everyone,

I'll give an example to show you what I want to do. I have the
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 result
of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of
these to be in the 1st row somewhere else.

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

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

Any help at all would be most appreciated.

Thanks

Zak


--
Alienator
------------------------------------------------------------------------
Alienator's Profile: http://www.excelforum.com/member.php...o&userid=31013
View this thread: http://www.excelforum.com/showthread...hreadid=506866

Averging non-zero cells only
 

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


"Alienator" wrote:


Hi everyone,

I'll give an example to show you what I want to do. I have the
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 result
of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of
these to be in the 1st row somewhere else.

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

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

Any help at all would be most appreciated.

Thanks

Zak


--
Alienator
------------------------------------------------------------------------
Alienator's Profile: http://www.excelforum.com/member.php...o&userid=31013
View this thread: http://www.excelforum.com/showthread...hreadid=506866

Averging non-zero cells only
 

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!

In article ,
Alienator
wrote:

Hi everyone,

I'll give an example to show you what I want to do. I have the
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 result
of the 1st should be 1.2179+1.2140+1.2115 /3. I need the results of
these to be in the 1st row somewhere else.

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

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

Any help at all would be most appreciated.

Thanks

Zak