Finding average beginning at first cell in row >0?

  • Thread starter Thread starter MJW
  • Start date Start date
M

MJW

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)
 
Array entered:

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

Normally entered:

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

--
Biff
Microsoft Excel MVP


Chip Pearson said:
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 said:
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.
Click to expand...
Click to expand...
 
(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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top