# 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

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

• circledchicken
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?

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

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

### Which adblocker are you using?    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

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