Sorry - I did not read closely enough, you wanted the last 5 nonempty rows,
for individual columns - my suggestion did the last nonempty row for all
columns. Please disregard.


"JMB" wrote:

This seemed to work okay for me:

=MEDIAN(INDEX(Data,MATCH(0,SUBTOTAL(3,OFFSET(INDEX (Data,1,0),ROW(INDIRECT("1:"&ROWS(Data)))-1,0)),0)-1,0))

array entered.

"Sam via OfficeKB.com" wrote:

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