Golf Handicaps Values

[Richard2542]

I have a list of player names that are divided into 5 Flights (A to E). Each Flight is determined by dividing the list of names with associated scores into 20% or less of the total players. Each player has a previously calculated handicap. I need to find the largest handicap value within each Flight. This needs to be done by formula without having to sort the database.

 

[Oscar Cronquist]

Re: Golf Handicps Values

I believe you are looking for this formula:
The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria.
MAXIFS function

 

[AlanY]

Re: Golf Handicps Values

something like this,

Excel 2013/2016
	A	B	C	D	E	F
1	Player	Handicaps	Flight		Flight	Highest
2	Player1	13	D		A	30
3	Player2	23	C		B	27
4	Player3	28	A		C	23
5	Player4	28	A		D	14
6	Player5	10	E		E	11
7	Player6	5	E
8	Player7	26	B
9	Player8	26	B
10	Player9	6	E
11	Player10	13	D
12	Player11	12	D
13	Player12	29	A
14	Player13	24	B
15	Player14	7	E
16	Player15	14	D
17	Player16	17	C
18	Player17	5	E
19	Player18	6	E
20	Player19	27	B
21	Player20	22	C
22	Player21	11	E
23	Player22	29	A
24	Player23	15	C
25	Player24	22	C
26	Player25	29	A
27	Player26	27	B
28	Player27	23	C
29	Player28	30	A
30	Player29	13	D
31	Player30	15	C
Sheet3

Cell Formulas
Range	Formula
F2	{=MAX(IF($C$2:$C$31=E2,$B$2:$B$31))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Oscar Cronquist]

Re: Golf Handicps Values

Thank you AlanY for providing values. Using your values the regular formula becomes:


=MAXIFS($B$2:$B$31, $C$2:$C$31, E2)


in cell F2. This is not an array formula, however, the function is only available in Excel 2016.

 

[Peter_SSs]

Re: Golf Handicps Values

.. also for AlanY's layout (& Excel 2010 or later)

Excel Workbook
	A	B	C	D	E	F
1	Player	Handicaps	Flight		Flight	Highest
2	Player1	13	D		A	30
3	Player2	23	C		B	27
4	Player3	28	A		C	23
5	Player4	28	A		D	14
6	Player5	10	E		E	11
7	Player6	5	E
8	Player7	26	B
9	Player8	26	B
10	Player9	6	E
11	Player10	13	D
12	Player11	12	D
13	Player12	29	A
14	Player13	24	B
15	Player14	7	E
16	Player15	14	D
17	Player16	17	C
18	Player17	5	E
19	Player18	6	E
20	Player19	27	B
21	Player20	22	C
22	Player21	11	E
23	Player22	29	A
24	Player23	15	C
25	Player24	22	C
26	Player25	29	A
27	Player26	27	B
28	Player27	23	C
29	Player28	30	A
30	Player29	13	D
31	Player30	15	C
Max Handicap

 

[AlanY]

Re: Golf Handicps Values

been informed by OP that he is using Excel 3.0.




.











.








just kidding

 

[Richard2542]

Re: Golf Handicps Values

I believe you are looking for this formula:
The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria.
MAXIFS function

Thanks sooooo much - exactly what I was looking for.

 

[Richard2542]

Re: Golf Handicps Values

Great job...thanks - I'll give it a go.