Finding the middle number

[Marvo]

Hi, can anyone come up with a formula I could place in C76 that would give me the mid point between a set of numbers? At the moment I'm doing it manually and sometimes I forget..

What I need is to spilt the numbers as evenly as possible in Column D into two groups, either high or low dependant on the mid point. I tried adding them together and dividing but that doesn't give a correct answer, at least it didn't in the test I ran. In the mini sheet example, 18 in C76 splits the 34 numbers into 17 high and 17 low.

Many thanks.

Barkway Park.xlsm
	A	B	C	D
1	✔			HOLE
2	✔	PLAYER		PAR
3	✔			S/I
4		Brian ADAMS	High
5		Linda ADAMS	High
6		Jeff BATTERBEE	High	34
7		Nick BAXTER	Low	17
8		Chris BENNETT	High
9		Richard BROMLEY	High
10		Kellie CURT	High	30
11		Sean CURT	Low	13
12		Rob DAVEY	High
13		Paul DERBYSHIRE	High	19
14		Rob DONNELLY	Low	15
15		Nic DOWNER	High
16		Bob FORDE	Low	17
17		Darryl FORNES	High	20
18		Rick GARVEY	Low	3
19		Dan GELLMAN	Low	10
20		Peter GIBOIN	High
21		Tony GROUT	High	20
22		David HAMMOND	High
23		Mark HARVEY	Low	13
24		Brian HIMSWORTH	Low	16
25		Chris HODGES	High
26		Shane JENKINS	Low	17
27		Tony JUN	High	33
28		Andrew KEEN	Low	14
29		Steve KIDD	High	19
30		Mike KNOTT	High	23
31		Darren KOLEK	High	20
32		James KOLEK	High
33		Kevin LEECH	Low	11
34		Steve LEECH	High	19
35		John LEONARD	Low	8
36		Glenn LEWIS	High
37		Scott LOOM	High	21
38		Trevor MANKTELOW	Low	16
39		John MARVESLEY	Low	16
40		Dan MCCARTHY	Low	18
41		Andy MCGILLIVRAY	High	22
42		Frank MCKENZIE	High
43		Alan MITCHELL	High
44		Daniel MOSS	High
45		Tony MUNTING	Low	18
46		Steve PEARCE	High	35
47		Stewart PEARCE	High	30
48		Keith PYM	High	21
49		Steve ROGERS	High
50		Richard SMITH	High
51		Steve SMITH	High	22
52		John TAYLOR	High
53		Gary THORPE	High
54		Peter VALENTINE	High
55		Martin VANN	High
56		Kevin WHARTON	High
57		Stuart WILLS	Low	18
58		Duncan Clarke	High
59		Jim Clewley	High
60		Denis Cronin	High
61		James Curt	High
62		Debbie Kolek	High
63		Tony Magana	High
64		Stewart McClean	High
65		Martin Noons	High
66		Martin Pickles	High
67		Peter Simmonds	High
68		Derek Warren	High
69		Ron Weeks	High	34
70		Julian Wood	High
71		Simon Woodford	High
72
73	34	Number of Players
74	0	SKINS	17	High
75	68		17	Low
76			18
Master

Cell Formulas
Range	Formula
C4:C71	C4	=IF(D4:D71<=$C$76,"Low","High")
A1:A3	A1	="✔"
A4:A71	A4	=IF(COUNTIF(playlist!$D$6:$D$63,B4)=1,1,0)
D4:D71	D4	=IF(A4=1,Handicaps!M4,"")
A73	A73	=SUM(A4:A71)
A74	A74	=COUNTIF(AQ4:AQ71,">0")
A75	A75	=COUNT(A4:A71)
C74	C74	=COUNTIFS($A$4:$A$71,"1",$C$4:$C$71,"High")
C75	C75	=COUNTIFS($A$4:$A$71,"1",$C$4:$C$71,"Low")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AR1,B2:AQ2,B3:AZ3,BB2:BD3,BA1:BE1,BF1:BI3,B4:BI71	Expression	=ROW()=CELL("row")	text	NO
A4:A71	Other Type	Icon set		NO

 

[etaf]

as you are using another sheet to load the values - i can not copy into excel
just get value

 

[Marvo]

Sorry, workbook is quite large. I've copied and changed the numbers to values. Does that help?

Book2
	A	B	C	D
4	0	Brian ADAMS	High
5	0	Linda ADAMS	High
6	1	Jeff BATTERBEE	High	34
7	1	Nick BAXTER	Low	17
8	0	Chris BENNETT	High
9	0	Richard BROMLEY	High
10	1	Kellie CURT	High	30
11	1	Sean CURT	Low	13
12	0	Rob DAVEY	High
13	1	Paul DERBYSHIRE	High	19
14	1	Rob DONNELLY	Low	15
15	0	Nic DOWNER	High
16	1	Bob FORDE	Low	17
17	1	Darryl FORNES	High	20
18	1	Rick GARVEY	Low	3
19	1	Dan GELLMAN	Low	10
20	0	Peter GIBOIN	High
21	1	Tony GROUT	High	20
22	0	David HAMMOND	High
23	1	Mark HARVEY	Low	13
24	1	Brian HIMSWORTH	Low	16
25	0	Chris HODGES	High
26	1	Shane JENKINS	Low	17
27	1	Tony JUN	High	33
28	1	Andrew KEEN	Low	14
29	1	Steve KIDD	High	19
30	1	Mike KNOTT	High	23
31	1	Darren KOLEK	High	20
32	0	James KOLEK	High
33	1	Kevin LEECH	Low	11
34	1	Steve LEECH	High	19
35	1	John LEONARD	Low	8
36	0	Glenn LEWIS	High
37	1	Scott LOOM	High	21
38	1	Trevor MANKTELOW	Low	16
39	1	John MARVESLEY	Low	16
40	1	Dan MCCARTHY	Low	18
41	1	Andy MCGILLIVRAY	High	22
42	0	Frank MCKENZIE	High
43	0	Alan MITCHELL	High
44	0	Daniel MOSS	High
45	1	Tony MUNTING	Low	18
46	1	Steve PEARCE	High	35
47	1	Stewart PEARCE	High	30
48	1	Keith PYM	High	21
49	0	Steve ROGERS	High
50	0	Richard SMITH	High
51	1	Steve SMITH	High	22
52	0	John TAYLOR	High
53	0	Gary THORPE	High
54	0	Peter VALENTINE	High
55	0	Martin VANN	High
56	0	Kevin WHARTON	High
57	1	Stuart WILLS	Low	18
58	0	Duncan Clarke	High
59	0	Jim Clewley	High
60	0	Denis Cronin	High
61	0	James Curt	High
62	0	Debbie Kolek	High
63	0	Tony Magana	High
64	0	Stewart McClean	High
65	0	Martin Noons	High
66	0	Martin Pickles	High
67	0	Peter Simmonds	High
68	0	Derek Warren	High
69	1	Ron Weeks	High	34
70	0	Julian Wood	High
71	0	Simon Woodford	High
72
73	34	Number of Players
74	0	SKINS	17	High
75	68		17	Low
76			18
Master

Cell Formulas
Range	Formula
C4:C71	C4	=IF(D4:D71<=$C$76,"Low","High")
A4:A71	A4	=IF(COUNTIF('[Barkway Park.xlsm]playlist'!$D$6:$D$63,B4)=1,1,0)
A73	A73	=SUM(A4:A71)
A74	A74	=COUNTIF(AQ4:AQ71,">0")
A75	A75	=COUNT(A4:A71)
C74	C74	=COUNTIFS($A$4:$A$71,"1",$C$4:$C$71,"High")
C75	C75	=COUNTIFS($A$4:$A$71,"1",$C$4:$C$71,"Low")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AR1,B2:AQ2,B3:AZ3,BB2:BD3,BA1:BE1,BF1:BI3,B4:BI71	Expression	=ROW()=CELL("row")	text	NO

 

[barry houdini]

Have you tried MEDIAN function?

That will give you the midpoint

 

[etaf]

=IF(D2="","",IF(D2<$C$76,"Low","High"))
C76 = median(D2:D68)

so 18.5 will be HIGH
you can change to use < = to include 18.5 as low

Book2
	A	B	C	D	E
1	#VALUE!	Brian ADAMS	Low
2	#VALUE!	Linda ADAMS	Low
3	#VALUE!	Jeff BATTERBEE	Low	34	High
4	#VALUE!	Nick BAXTER	High	17	Low
5	#VALUE!	Chris BENNETT	Low
6	#VALUE!	Richard BROMLEY	Low
7	#VALUE!	Kellie CURT	High	30	High
8	#VALUE!	Sean CURT	Low	13	Low
9	#VALUE!	Rob DAVEY	Low
10	#VALUE!	Paul DERBYSHIRE	Low	19	High
11	#VALUE!	Rob DONNELLY	High	15	Low
12	#VALUE!	Nic DOWNER	Low
13	#VALUE!	Bob FORDE	Low	17	Low
14	#VALUE!	Darryl FORNES	Low	20	High
15	#VALUE!	Rick GARVEY	High	3	Low
16	#VALUE!	Dan GELLMAN	Low	10	Low
17	#VALUE!	Peter GIBOIN	Low
18	#VALUE!	Tony GROUT	Low	20	High
19	#VALUE!	David HAMMOND	Low
20	#VALUE!	Mark HARVEY	Low	13	Low
21	#VALUE!	Brian HIMSWORTH	High	16	Low
22	#VALUE!	Chris HODGES	Low
23	#VALUE!	Shane JENKINS	High	17	Low
24	#VALUE!	Tony JUN	High	33	High
25	#VALUE!	Andrew KEEN	High	14	Low
26	#VALUE!	Steve KIDD	Low	19	High
27	#VALUE!	Mike KNOTT	Low	23	High
28	#VALUE!	Darren KOLEK	High	20	High
29	#VALUE!	James KOLEK	Low
30	#VALUE!	Kevin LEECH	Low	11	Low
31	#VALUE!	Steve LEECH	High	19	High
32	#VALUE!	John LEONARD	Low	8	Low
33	#VALUE!	Glenn LEWIS	Low
34	#VALUE!	Scott LOOM	Low	21	High
35	#VALUE!	Trevor MANKTELOW	High	16	Low
36	#VALUE!	John MARVESLEY	Low	16	Low
37	#VALUE!	Dan MCCARTHY	Low	18	Low
38	#VALUE!	Andy MCGILLIVRAY	Low	22	High
39	#VALUE!	Frank MCKENZIE	Low
40	#VALUE!	Alan MITCHELL	High
41	#VALUE!	Daniel MOSS	High
42	#VALUE!	Tony MUNTING	High	18	Low
43	#VALUE!	Steve PEARCE	Low	35	High
44	#VALUE!	Stewart PEARCE	Low	30	High
45	#VALUE!	Keith PYM	High	21	High
46	#VALUE!	Steve ROGERS	Low
47	#VALUE!	Richard SMITH	Low
48	#VALUE!	Steve SMITH	Low	22	High
49	#VALUE!	John TAYLOR	Low
50	#VALUE!	Gary THORPE	Low
51	#VALUE!	Peter VALENTINE	Low
52	#VALUE!	Martin VANN	Low
53	#VALUE!	Kevin WHARTON	Low
54	#VALUE!	Stuart WILLS	Low	18	Low
55	#VALUE!	Duncan Clarke	Low
56	#VALUE!	Jim Clewley	Low
57	#VALUE!	Denis Cronin	Low
58	#VALUE!	James Curt	Low
59	#VALUE!	Debbie Kolek	Low
60	#VALUE!	Tony Magana	Low
61	#VALUE!	Stewart McClean	Low
62	#VALUE!	Martin Noons	Low
63	#VALUE!	Martin Pickles	High
64	#VALUE!	Peter Simmonds	Low
65	#VALUE!	Derek Warren	Low
66	#VALUE!	Ron Weeks	Low	34	High
67	#VALUE!	Julian Wood	Low
68	#VALUE!	Simon Woodford	High
Sheet1

Cell Formulas
Range	Formula
C1:C68	C1	=IF(D4:D71<=$C$76,"Low","High")
A1:A68	A1	=IF(COUNTIF('[Barkway Park.xlsm]playlist'!$D$6:$D$63,B4)=1,1,0)
E2:E68	E2	=IF(D2="","",IF(D2<$C$76,"Low","High"))
Dynamic array formulas.

 

[Marvo]

Okay, so it looks like all I had to do was allow a decimal point to the C76 cell. A result of 18.5 (like 18) would split the field in half perfectly but without the decimal point you'd get a return of 19 which wouldn't work. Interesting. Thank you BOTH.

 

[etaf]

you are welcome