finding 1 value from Median

[SeliM]

Thank you for taking time to read my question
Is there an approach through Excel formula to find the next value above and below the median?

I am building a system for clinical governance analysis of clinical events and trying to find the values above and below median for demonstration of trends\patterns to hospital Board. (Current range of values is for 3 years (36 values))

Any advice would be appreciated

R
G

 

[Momentman]

This is a very long approach -but should help you understand it better

I assume the original range is not sorted

Attributes.xlsx
	B	C	D	E	F
1	Range		Median	Closest-below	Closest-Above
2	5		19	17	21
3	16
4	21
5	34
6	25
7	28
8	8
9	14
10	10
11	16
12	24
13	28
14	7
15	29
16	35
17	17
18	20
19
Sheet2

Cell Formulas
Range	Formula
D2	D2	=MEDIAN(B2:B17)
E2	E2	=LET(rng,B2:B17,IF(ISEVEN(COUNT(rng)),INDEX(SORT(rng),COUNT(rng)/2),INDEX(SORT(rng),INT(COUNT(rng)/2))))
F2	F2	=LET(rng,B2:B17,IF(ISEVEN(COUNT(rng)),INDEX(SORT(rng),COUNT(rng)/2+1),INDEX(SORT(rng),INT(COUNT(rng)/2+2))))

 

[Scott Huish]

How about:

Book1
	B	C	D	E	F
1	Range		Median	Closest-below	Closest-Above
2	5		18	17	21
3	16
4	21
5	34
6	25
7	28
8	8
9	14
10	10
11	16
12	24
13	28
14	7
15	29
16	35
17	17
18	18
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MEDIAN(B2:B18)
E2	E2	=MAX(IF(B2:B18<D2,B2:B18))
F2	F2	=MIN(IF(B2:B18>D2,B2:B18))

 

[StephenCrump]

A simplified version of @Momentman's formula, plus an alternative producing different results (depending on what is meant by "values above and below")

	A	B	C	D	E	F
1		Range		Median	Closest-below	Closest-Above
2		5		19	19	19
3		16			16	24
4		19
5		34
6		25
7		28
8		8
9		14
10		10
11		16
12		24
13		28
14		7
15		29
16		35
17		19
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MEDIAN(B2:B17)
E2:F2	E2	=LET(rng,B2:B17,INDEX(SORT(rng),{0,1}+COUNT(rng)/2))
E3	E3	=MAX(FILTER(B2:B17,B2:B17<D2))
F3	F3	=MIN(FILTER(B2:B17,B2:B17>D2))
Dynamic array formulas.

 

[SeliM]

Thank you ever so much Momentum - well above my skill level!
I have been able to replicate using your data in column format however the data i work with is in row format (easy I though simply change the data range!)
Reset data range to J4:Z4

The result is different:

Median	Closest Value Below	Closest Value Above
20​	14​	16​

The only change is the range:
=LET(rng,$J$4:$Z$4,IF(ISEVEN(COUNT(rng)),INDEX(SORT(rng),COUNT(rng)/2),INDEX(SORT(rng),INT(COUNT(rng)/2))))
=LET(rng,$J$4:$Z$4,IF(ISEVEN(COUNT(rng)),INDEX(SORT(rng),COUNT(rng)/2+1),INDEX(SORT(rng),INT(COUNT(rng)/2+2))))

Is there something I have missed.
R
G

 

[Scott Huish]

Kinda curious how the simpler options did not work for you?

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Median	Closest-below	Closest-Above
2	18	17	21
3
4						Range	5	16	21	34	25	28	8	14	10	16	24	28	7	29	35	17	18
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MEDIAN(J4:Z4)
E2	E2	=MAX(IF(J4:Z4<D2,J4:Z4))
F2	F2	=MIN(IF(J4:Z4>D2,J4:Z4))

 

[SeliM]

Many thanks to you all for guiding me through this challenge.

Very much appreciated and I admire your expertise.

Actually the simpler version did work for me - when I got my ranges sorted out

R
G

 

[Cubist]

@Scott Huish
Genuine question - why didn't you use the built-in MIN/MAXIFS (not that it makes much of a difference)?

 

[Scott Huish]

@Scott Huish
Genuine question - why didn't you use the built-in MIN/MAXIFS (not that it makes much of a difference)?

Honestly, because I didn't think of it, but great idea.

=MAXIFS(J4:Z4,J4:Z4,"<"&D2)
=MINIFS(J4:Z4,J4:Z4,">"&D2)

 

[SeliM]

Thanks Cubist - brilliantly simple
Didn't think about it because my mind doesn't work quite like that apparently
Have a terrific day
R
G