Calculate MEDIAN of Last x Rows in a Column

[Sam via OfficeKB.com]

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),ROWS(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

 

[Domenic]

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!

 

[daddylonglegs]

For the first column of data

=MEDIAN(OFFSET(INDEX(INDEX(data,0,1),COUNT(INDEX(data,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

 

[JMB]

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.

 

[JMB]

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.

 

[Sam via OfficeKB.com]

Hi Domenic,

That's Great! Thank you so very much.

Cheers,
Sam

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.)




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!

Hi All,

[quoted text clipped - 15 lines]

Thanks Sam

 

[Sam via OfficeKB.com]

Hi daddylonglegs,

Perfect! Thank you very much for your time and assistance.

Cheers,
Sam