Median Value of array with variable start point

wjlbmw

New Member
Help...

I have data in a column that I need to find the median of, but it includes blanks. How do I write a formula to calculate the median of the last 5 cells with values (start with a8 and build the array of 5 values moving up the rows, so I want the median of (8,5,2,3,7):

A1 5
A2 7
A3
A4 3
A5
A6 2
A7 5
A8 8

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi and welcome to the forum,

Maybe you can try a setup something like this:

Excel 2013
ABCD
15Number count6
27Start number position index2
3Start number row index2
43Median5
5
62
75
88
Sheet1
Cell Formulas
RangeFormula
D1=COUNT(A1:A8)
D2=MAX(0, D1 - 5) + 1
D4=MEDIAN(INDEX(A1:A8, D3):INDEX(A1:A8, MATCH(9.99999999999999E+307, A1:A8)))
D3{=SMALL(IF(ISNUMBER(A1:A8), ROW(A1:A8) - ROW(A1) + 1), D2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Also...

Control+shift+enter, not just enter:
Rich (BB code):
``````=MEDIAN(IF(ROW(A1:A8)>=LARGE(IF(ISNUMBER(A1:A8),ROW(A1:A8)),5),
IF(ISNUMBER(A1:A8),A1:A8)))
``````

Hi and welcome to the forum,

Maybe you can try a setup something like this:

Excel 2013
ABCD
15Number count6
27Start number position index2
3Start number row index2
43Median5
5
62
75
88

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D1=COUNT(A1:A8)
D2=MAX(0, D1 - 5) + 1
D4=MEDIAN(
INDEX(A1:A8, D3):INDEX(A1:A8, MATCH(9.99999999999999E+307, A1:A8))
)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D3{=SMALL(
IF(ISNUMBER(A1:A8), ROW(A1:A8) - ROW(A1) + 1),
D2
)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This looks like it works perfectly, can explain to me what it is doing? Specifically D2, D3 and D4?

Thanks again for your help!

Also...

Control+shift+enter, not just enter:
Rich (BB code):
``````=MEDIAN(IF(ROW(A1:A8)>=LARGE(IF(ISNUMBER(A1:A8),ROW(A1:A8)),5),
IF(ISNUMBER(A1:A8),A1:A8)))
``````

thank you Aladin, yours looks like it works as well, but could you please help me understand what the formula is doing?

thank you Aladin, yours looks like it works as well, but could you please help me understand what the formula is doing?

MEDIAN(IF(ROW(A1:A8)>=LARGE(IF(ISNUMBER(A1:A8),ROW(A1:A8)),5),IF(ISNUMBER(A1:A8),A1:A8)))

==> ROW delivers row numbers associated with the cells of the target range

MEDIAN(IF({1;2;3;4;5;6;7;8}>=LARGE(IF({TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE},{1;2;3;4;5;6;7;8}),5),IF(ISNUMBER(A1:A8),A1:A8)))

==> IF evaluates in order to fed LARGE...

MEDIAN(IF({1;2;3;4;5;6;7;8}>=LARGE({1;2;FALSE;4;FALSE;6;7;8},5),IF(ISNUMBER(A1:A8),A1:A8)))

==> LARGE states that the fifth largest row number is row 2 (where the fifth figure to include lies)

MEDIAN(IF({1;2;3;4;5;6;7;8}>=2,IF(ISNUMBER(A1:A8),A1:A8)))

==>

MEDIAN(IF({FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},IF(ISNUMBER(A1:A8),A1:A8)))

==>

MEDIAN(IF({FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},IF({TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE},A1:A8)))

==>

MEDIAN(IF({FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{5;7;FALSE;3;FALSE;2;5;8}))

==>

MEDIAN({FALSE;7;FALSE;3;FALSE;2;5;8})

==>

5

Expressed in a tersed manner: Mark the figures from the row on where the fifth figure is located and feed them to MEDIAN.

Hope the foregoing evaluations make clear how the formula unfolds.

This looks like it works perfectly, can explain to me what it is doing? Specifically D2, D3 and D4?

Thanks again for your help!
Thanks for the feedback.

As Aladin's solution is better and he has given you a detailed explanation, I won't confuse you with mine (unless you really want it for some reason).

Replies
6
Views
383
Replies
4
Views
113
Replies
1
Views
395
Replies
4
Views
126
Replies
5
Views
518

Forum statistics

1,207,195
Messages
6,077,010
Members
446,250
Latest member
Dontcomehereoften

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back