Averging non-zero cells only

[Alienator]

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

Any help at all would be most appreciated.

Thanks

Zak

[FiluDlidu]

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
>
> 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
>
>

[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


"Alienator" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
> 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
>

[Ron Coderre]

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
>
> 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
>
>

[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!

In article <[email protected]>,
Alienator <[email protected]>
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
>
> Any help at all would be most appreciated.
>
> Thanks
>
> Zak