Nevermind, got it. The problem was it was beginning one column to the right
of the first 0 value--changing the cell ref portion of OFFSET to one column
prior set it straight. Thanks again!

Jamie W.

"Chip Pearson" wrote:

The following array formula will work for cells A1:F1. Adjust the ranges as
necessary. It will return the average of the numbers in A1:F1 that are to
the right of the last leading zero. If a zero follows a non-zero element,
that zero is included in the average. For example, if A1:F1 has

0 0 1 0 3 4

it will return 2, which is the average of 1, 0, 3, and 4. The zero between 1
and 3 is included in the average.

=AVERAGE(OFFSET(A1,0,MIN(IF(COLUMN(A1:F1)*(A1:F1< 0)<0,COLUMN(A1:F1)*(A1:F1<0),COLUMN(G1)))-1,1,COLUMN(H1)-MIN(IF(COLUMN(A1:F1)*(A1:F1<0)<0,COLUMN(A1:F1)*( A1:F1<0),COLUMN(G1)))+1))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this correctly, Excel will display the formula enclosed in curly
braces { }.

See http://www.cpearson.com/excel/ArrayFormulas.aspx for a lot more
information about array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"MJW" wrote in message
...
Hi All,

I'm currently trying to calculate an average for all cells in a row,
starting with the first cell that is greater than zero. The only salient
example I found on the web has apparently been unable to permeate my
morning
fog, so here I am. The example I located was using a methodology of
=AVERAGE(ADDRESS(MIN(IF(NumRange=MIN(NumRange),ROW (NumRange))),COLUMN(NumRange),1):BD5
[where bd5 signifies the end of the row], but I can't seem to get it
working.
Here's the stupid part of my question--is NumRange an actual valid term,
or
is this supposed to be a named range reference? If not, any ideas where I
went wrong? Appreciate any help anyone can provide.

Thanks,

Jamie W.