Assuming that you want the MEDIAN for the third column in your named
range 'DATA', try...

A2: 5

(This indicates that you want the MEDIAN for the last 5 numbers.)

B2:

=MATCH(9.99999999999999E+307,INDEX(Data,0,3))

C2:

=MEDIAN(INDEX(Data,B2-MIN(A2,B2)+1,3):INDEX(Data,B2,3))

Note that if there's less than 5 numbers available, the formula will
return the median for those numbers which are available.

Hope this helps!

In article <69ddde2a922ed@uwe, "Sam via OfficeKB.com" <u4102@uwe
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