Ranking on Countback

[Ka001]

I am using Excel 2013. I am trying to do handicaps for a group of friends golf trip. I want determine a winner based on a countback of the back 9 holes, the back 6 hole and finally the back 3 holes.
I have tried nested ifs, if and , countif rank etc but I cannot get it to work. Can anyone help?

my Excel expertise. Can anyone help?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2		32	11	6	5	32.11061	1
3		27	9	6	4	27.0906	5
4		29	10	7	3	29.1007	2
5		27	9	7	4	27.0907	4
6		27	9	7	5	27.09071	3
Sheet6

Cell Formulas
Range	Formula
F2:F6	F2	=SUMPRODUCT(B2:E2/10^((COLUMN($A$1:$D$1)-1)*2))
G2:G6	G2	=RANK(F2,$F$2:$F$6)

 

[Ka001]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2		32	11	6	5	32.11061	1
3		27	9	6	4	27.0906	5
4		29	10	7	3	29.1007	2
5		27	9	7	4	27.0907	4
6		27	9	7	5	27.09071	3
Sheet6

Cell Formulas
Range	Formula
F2:F6	F2	=SUMPRODUCT(B2:E2/10^((COLUMN($A$1:$D$1)-1)*2))
G2:G6	G2	=RANK(F2,$F$2:$F$6)

Thanks, that partially worked. Here is what I got back for day 1 and day 2. The screenshot wtuh day 3 and day 4 was too big to upload. How do I upload the worksheet?

 

[Fluff]

You cannot upload a workbook, but you can post your sample data including the formulae used.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Ka001]

I added XL2BB to Excel 2013. The minisheet seemed to load OK. Below is what happened when I pasted it the message. Please let me know if this worked.
Basically, what I am trying to do is to go through golf score cards to find a winner.
If the largest score (number of points) is unique this is the winner, the next largest is 2nd etc. If there are several "largest" e.g. 32, I then check who has the best score on the back 9, if that doesn't produce a winner I go to the back 6 and finally the back 3. I don't go beyond the best scores on the back 3 holes. If there are duplicates at the back 3 stage I declare a "draw".
I hope this helps. I have tried using In dex, Match, Large, IF, IF AND but I cannot get any of them to Rank the scores from 1st to 5th.
Thanks

Countback Ranking Query 2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Day1
2	Player	Score	Back 9	Back 6	Back 3			Place	Rank
3	A	32	11	6	5	32.110605	1	1	outright win
4	B	27	10	6	4	27.100604	3	3rd
5	C	29	10	7	3	29.100703	2	2nd
6	D	26	9	5	4	26.090504	4	4th
7	E	24	13	7	6	24.130706	#N/A	5th
8	Day 2
9	Player	Score	Back 9	Back 6	Back 3			Place
10	A	32	11	6	5	32.11061	1	2nd	2nd on back 9
11	B	29	10	6	4	29.1006	#N/A	5th	5th on back 9
12	C	29	10	7	3	29.1007	2	4th	4th on back 9
13	D	32	9	5	4	32.0905	#N/A	3rd	3rd on back 9
14	E	32	13	7	6	32.13071	#N/A	1	won on back 9
15	Day 3
16	Player	Score	Back 9	Back 6	Back 3			Place
17	A	32	8	6	2	32.0806	#N/A	3	3rd on back 9
18	B	29	8	4	4	29.0804	#N/A	4	joint 4th	no way of separating 4th place
19	C	29	8	4	4	29.0804	#N/A	4	joint 4th	no way of separating 4th place
20	D	32	13	6	7	32.13061	#N/A	2	2nd on back 9
21	E	32	13	7	6	32.13071	#N/A	1	won on back 6
22	Day 4
23	Player	Score	Back 9	Back 6	Back 3			Place
24	A	29	11	7	5	29.11071	#N/A	4	4th
25	B	32	13	8	5	32.13081	#N/A	1	won on back 3
26	C	28	10	7	3	28.1007	#N/A	5	5th
27	D	32	13	8	4	32.1308	#N/A	2	2nd on back 3
28	E	32	13	7	3	32.1307	#N/A	3	3rd on back 3
Sheet1

Cell Formulas
Range	Formula
F24:F28,F17:F21,F10:F14,F3:F7	F3	=SUMPRODUCT(B3:E3/10^((COLUMN($A$1:$D$1)-1)*2))
G24:G28,G17:G21,G10:G14,G3:G7	G3	=RANK(F3,$F$2:$F$6)

 

[Fluff]

Thanks for that, the ranges in your Rank functions are wrong in G3 copied down it should be

=RANK(F3,$F$3:$F$7)

and in G10 it should be

=RANK(F10,$F$10:$F$14)

and similar for the other ranges.

 

[Ka001]

Thank you Fluff.
That worked when I used your formulas for each day. I tried to apply the formulas to an Excel sheet that I am likely to use, however blank rows - to indicate people who did not play - caused problems, see the minisheet. I have also included a screenshot of the golf worksheet so that you can see the colour system I used for ranking the winners.
Out of interest, why do you use the functions "Sumproduct" and "Column"? I have never used them and don't understand why you used them instead of say "Match" and "Large". As you can tell I am not particularly proficient with Excel.
Thank you for all your help. I am learning a lot.

Golf Winners using Countback.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Member Name	H/C	Score	Points	Nett	Back 9	Back 6	Back 3	Product	Rank		Prize Winners
2	Allen M	15	27	-5.5	21.5	10	7	3	2.16007E-05	2		5
3	Carroll A	10	14	-1.5	12.5				1.25000E-05	11
4	Conway P	27	19	-1.5	17.5	10	6	4	1.76006E-05	5		9
5	Coyle J	17	20	0.0	20.0	12.0	7.0	5.0	2.01207E-05	3		6
6	Dempsey L	23	15	1.0	16.0				1.60000E-05	6
7	Devery K	21	15	0.0	15.0				1.50000E-05	10
8	Donnelly M	24	15	1.0	16.0				1.60000E-05	6
9	Duggan M	4	23	1.0	24.0	14.0	8.0	6.0	2.41408E-05	1		4
10	Duggan S	12	15	-2.5	12.5				1.25000E-05	11
11	Fitzpatrick G	16	14	1.5	15.5				1.55000E-05	9
12	Flynn P	22	18	1.5	19.5	9.0	6.0	3.0	1.95906E-05	4		7
13	Geraghty B	15	15	1.0	16.0				1.60000E-05	6
14	Goff H	10							#VALUE!	#VALUE!
15	Gorman L	16	29	1.0	30.0				3.00000E-05	#N/A
16	Grealy J	32							#VALUE!	#VALUE!
17	Grehan G	12	14	1.0	15.0				1.50000E-05	10
18	Hall G	18	17	1.0	18.0	9.0	9.0	9.0	1.80909E-05	#N/A		8
19	Holland C	17	12	1.0	13.0				1.30000E-05	#VALUE!
20	Hurley K	6							#VALUE!	#VALUE!
21	Kehily F	27							#VALUE!	#VALUE!
22	Kenneally M	27	24	1.0	25.0	17.0	10.0	7.0	2.51710E-05	#VALUE!		3
23	Kenny F	10	29	1.0	30.0	10.0	5.0	5.0	3.01005E-05	#VALUE!		1
24	Kierans G	21	16	1.0	17.0				1.70000E-05	#VALUE!		10
25	Magee A	18	16	1.0	17.0				1.70000E-05	#VALUE!
26	McDermott F	25	16	1.0	17.0				1.70000E-05	#VALUE!
27	McNally G	18	14	1.0	15.0				1.50000E-05	#VALUE!
28	Molloy B	13	15	1.0	16.0				1.60000E-05	#VALUE!
29	Moran G	14							#VALUE!	#VALUE!
30	Moran M	6	15	1.0	16.0				1.60000E-05	#VALUE!
31	Mullooly S	26	26	1.0	27.0	10.0	6.0	4.0	2.71006E-05	#VALUE!		2
32	Murphy P	22	15	1.0	16.0				1.60000E-05	#VALUE!
33	Murphy T	29							#VALUE!	#VALUE!
34	Murphy S	16	14	1.0	15.0				1.50000E-05	#VALUE!
35	Murphy V	18							#VALUE!	#VALUE!
36	O'Brien D	23	14	1.0	15.0				1.50000E-05	#VALUE!
37	O'Connell P	23	14	1.0	15.0				1.50000E-05	#VALUE!
38	O'Malley C	29	14	1.0	15.0				1.50000E-05	#VALUE!
39	Power K	22							#VALUE!	#VALUE!
Sheet4

Cell Formulas
Range	Formula
I2:I39	I2	=SUMPRODUCT(E2:H2/10^((COLUMN($D$1:$G$1)-1)*2))
J2:J18	J2	=RANK(I2,$I$2:$I$13)
J19:J39	J19	=RANK(I19,$I$2:$I$39)
E16:E39,E2:E14	E2	=IF(C2<>0,C2+D2,"")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Member Name	H/C	Score	Points	Nett	Back 9	Back 6	Back 3	Product	Rank
2	Allen M	15	27	-5.5	21.5	10	7	3	21.510007003	5
3	Carroll A	10	14	-1.5	12.5				0
4	Conway P	27	19	-1.5	17.5	10	6	4	17.510006004	9
5	Coyle J	17	20	0	20	12	7	5	20.012007005	6
6	Dempsey L	23	15	1	16				0
7	Devery K	21	15	0	15				0
8	Donnelly M	24	15	1	16				0
9	Duggan M	4	23	1	24	14	8	6	24.014008006	4
10	Duggan S	12	15	-2.5	12.5				0
11	Fitzpatrick G	16	14	1.5	15.5				0
12	Flynn P	22	18	1.5	19.5	9	6	3	19.509006003	7
13	Geraghty B	15	15	1	16				0
14	Goff H	10							0
15	Gorman L	16	29	1	30				0
16	Grealy J	32							0
17	Grehan G	12	14	1	15				0
18	Hall G	18	17	1	18	9	9	9	18.009009009	8
19	Holland C	17	12	1	13				0
20	Hurley K	6							0
21	Kehily F	27							0
22	Kenneally M	27	24	1	25	17	10	7	25.017010007	3
23	Kenny F	10	29	1	30	10	5	5	30.010005005	1
24	Kierans G	21	16	1	17				0
25	Magee A	18	16	1	17				0
26	McDermott F	25	16	1	17				0
27	McNally G	18	14	1	15				0
28	Molloy B	13	15	1	16				0
29	Moran G	14							0
30	Moran M	6	15	1	16				0
31	Mullooly S	26	26	1	27	10	6	4	27.010006004	2
32	Murphy P	22	15	1	16				0
33	Murphy T	29							0
34	Murphy S	16	14	1	15				0
35	Murphy V	18							0
36	O'Brien D	23	14	1	15				0
37	O'Connell P	23	14	1	15				0
38	O'Malley C	29	14	1	15				0
39	Power K	22							0
Data

Cell Formulas
Range	Formula
I2:I39	I2	=IF(OR(E2="",F2=""),0,SUMPRODUCT(E2:H2/1000^((COLUMN($A$1:$D$1)-1))))
J2:J39	J2	=IF(OR(E2="",F2=""),"",RANK(I2,$I$2:$I$39))
E2:E14,E16:E39	E2	=IF(C2<>0,C2+D2,"")

 

[Ka001]

That worked, but I don't know how!! How does the Sumproduct / Column combination work?
Thanks a million.

 

[Fluff]

It divides E2 by 1000^0 (ie 1) F2 by 1000^2, G2 by 1000^3 etc & then adds them all up.