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.

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.

Array entered:

=AVERAGE(F1:INDEX(A1:F1,MATCH(TRUE,A1:F10,0)))

Normally entered:

=AVERAGE(F1:INDEX(A1:F1,MATCH(TRUE,INDEX(A1:F10,1 ,),0)))

--
Biff
Microsoft Excel MVP


"Chip Pearson" wrote in message
...
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.

(Gotta say, I didn't expect to receive a response from the actual author of
the aforementioned "only salient example on the web" I'd mentioned in my
original post. ;) )

Thanks Chip--any idea why this isn't calculating properly? I have it set up
for a 1-d array (b5-bc5), and the first number 0 occurs at y5; the sum of
all values in the array is 12. So the average (rounded to four decimals)
would be .3871, but the formula is coming up with .3548. Any help you could
give would be great; as a sidenote, your website's been a great learning tool
in the past for me, so I thought I'd throw a thanks over the wall for that as
well!

Sincerely,

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.

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.