Averaging Number above Zero
It needs to be array entered, Ctrl-Shift-Enter, not just enter.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Mike McLellan" wrote in message
...
Ron,
Many thanks for your help.
I've input the following ...
=AVERAGE(IF(ISNUMBER(MATCH(ROW(J10:J107),{15,23,32 ,40,48,57,65,73,82,90,98,1
07},0))*(J10:J1070),J10:J107))
and yet it is coming up with an #VALUE error. All the cells in the
specified list (15,23,32 etc) contain a valid number (0 or greater) but
the
other cells in the range contain (mostly) spaces - is this what is causing
the problem?
Mike
"Ron Coderre" wrote:
Here are two thoughts (both are array formulas*)....
If your list of cells is not very extensive:
B1:
=AVERAGE(IF(ISNUMBER(MATCH(ROW(A1:A16),{1,6,11,16} ,0))*(A1:A160),A1:A16))
OR..if you want row numbers that end in 1 or 6:
B1: =AVERAGE(IF((MOD(ROW(A1:A16),10)={1,6})*(A1:A160) ,A1:A16))
*Note: To commit array an formula, hold down [Ctrl] and [Shift] when you
press [Enter].
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
"Mike McLellan" wrote:
How do I average the number in a non-contiguous range (e.g. A1, A6,
A11, A16
etc) that are above zero?
|