Hi All,

I have a dynamic named range called "Data" that houses numeric values and
empty text (""). "Data" spans 55 columns and many rows.

I would like a flexible Formula to exclude the cells with empty text ("") and
calculate the MEDIAN for individual columns say for the LAST 5 rows - number
of rows will vary. When the first row of empty text ("") is found in a column
all others rows below it will be empty text. I need to exclude the empty text
to find the actual LAST row with a numeric value and then start the
calculation of the MEDIAN from there.


I've tried variations on this array entered:

=MEDIAN(IF(NOT(ISERROR(INDEX(OFFSET(Data,0,0,,1),R OWS(Data)-5,1):INDEX(Data,
ROWS(Data),1)))<"",INDEX(OFFSET(Data,0,0,,1),ROWS (Data)-5,1):INDEX(Data,ROWS
(Data),1)))

Thanks Sam

--
Message posted via http://www.officekb.com