Median Value of array with variable start point

[wjlbmw]

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

 

[circledchicken]

Hi and welcome to the forum,

Maybe you can try a setup something like this:

Excel 2013
	A	B	C	D
1	5		Number count	6
2	7		Start number position index	2
3			Start number row index	2
4	3		Median	5
5
6	2
7	5
8	8
Sheet1

Cell Formulas
Range	Formula
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.

 

[Aladin Akyurek]

Also...

Control+shift+enter, not just enter:

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

 

[wjlbmw]

Hi and welcome to the forum,

Maybe you can try a setup something like this:

Excel 2013

	A	B	C	D
1	5		Number count	6
2	7		Start number position index	2
3			Start number row index	2
4	3		Median	5
5
6	2
7	5
8	8

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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 Cell Formula 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!

 

[wjlbmw]

Also...

Control+shift+enter, not just enter:

=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?

 

[Aladin Akyurek]

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.

 

[circledchicken]

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