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

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