For the first column of data

=MEDIAN(OFFSET(INDEX(INDEX(data,0,1),COUNT(INDEX(d ata,0,1))),,,MAX(-COUNT(INDEX(data,0,1)),-5),))

This gives the median of the last 5 numbers in the column, assuming the
rules you outlined are obeyed. If there are less than 5 values it gives the
median of whatever numbers there are.

For column 2 change all 1s to 2s or you could use some sort of COLUMN
reference to apply one formula which can be copied across

"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