Ron Coderre Wrote:
Nicely done, Domenic.

Thanks Ron! I came across the second formula a couple of days ago.
It's definitely an interesting way to deal with non-contiguous cells.
The advantage is that it doesn't use the INDIRECT function, which as
you know is volatile. But the disadvantage is that the formula, as it
stands, doesn't allow for empty cells. In this case, the formula could
be changed as follows...

=AVERAGE(IF(SMALL((C32,C58,C84,H32,H58,H84),ROW(IN DIRECT("1:"&COUNT(C32,C58,C84,H32,H58,H84))))0,SM ALL((C32,C58,C84,H32,H58,H84),ROW(INDIRECT("1:"&CO UNT(C32,C58,C84,H32,H58,H84))))))

I think wrapping either of them in a MAX, MIN, or SUMPRODUCT function
will eliminate the need for CSE:

=MAX(AVERAGE(IF(N(INDIRECT({"C32","C58","C84","H32 ","H58","H84"}))0,N(INDIRECT({"C32","C58","C84"," H32","H58","H84"})))))

=MIN(AVERAGE(IF(SMALL((C32,C58,C84,H32,H58,H84),{1 ,2,3,4,5,6})0,SMALL((C32,C58,C84,H32,H58,H84),{1, 2,3,4,5,6}))))

While that would certainly work, my preference is to exclude the MAX
and MIN functions and confirm the formula with CONTROL+SHIFT+ENTER.
But that's just my personal preference. :)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=388759