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?

 

[Ka001]

Fluff,
Please forgive my ignorance. I am trying to understand the formula in English.
I accept "divides E2 by 1000^0 (ie 1) F2 by 1000^2, G2 by 1000^3 etc & then adds them all up".
Why divide E2, F2, G2 and H2 by 1000^COLUMN? "COLUMN($A$1:$D$1)" returns 1., how does it translate to ^1, ^2, ^3 etc.?
By trying to execute the formula step by step I can't get the same result as you.
I obviously don't know enough about how the formula works but I am trying to understand it.
I don't want to impose on you too much but hopefully you can explain it in layman's terms.
Thanks.

Golf Ranking using Countback - New.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Member Name	H/C	Score	Points	Nett	Back 9	Back 6	Back 3	Product - Weighted Average						divides E2 by 1000^0 (ie 1) F2 by 1000^2, G2 by 1000^3 etc & then adds them all up.	Columns
2	Allen M	15	27	-5.5	21.5	10	7	3	2.15100E+01		0.0215	0.0215	21.5			1
3											0.1	0.00001	10			0
4											0.007	0.000000007	7
5											0.003	0.000000003	3
6											0.1315	0.02151001	41.5
Sheet2

Cell Formulas
Range	Formula
E2	E2	=IF(C2<>0,C2+D2,"")
I2	I2	=IF(OR(E2="",F2=""),0,SUMPRODUCT(E2:H2/1000^((COLUMN($A$1:$D$1)-1))))
K2:L2	K2	=21.5/1000^1
M2	M2	=21.5/1000^((COLUMN($A$1:$D$1)-1))
K3	K3	=10/100^1
L3	L3	=10/1000^2
M3	M3	=10/1000^((COLUMN($A$1:$D$1)-1))
K4	K4	=7/1000^1
L4	L4	=7/1000^3
M4	M4	=7/1000^((COLUMN($A$1:$D$1)-1))
K5	K5	=3/1000^1
L5	L5	=3/1000^3
M5	M5	=3/1000^((COLUMN($A$1:$D$1)-1))
K6:M6	K6	=SUM(K2:K5)
P2	P2	=COLUMN($A$1:$D$1)
P3	P3	=COLUMN($A$1:$D$1)-1

 

[Fluff]

COLUMN($A$1:$D$1) returns an array of numbers {1,2,3,4,5} from which we subtract 1 giving {0,1,2,3,4} which in turn gives 1000^0, 1000^1, 1000^2 etc & then E2:H2 is divided by those numbers.
So E2 is divided by 1 F2 by 1000, G2 by 1000000 etc

 

[Ka001]

COLUMN($A$1:$D$1) returns an array of numbers {1,2,3,4,5} from which we subtract 1 giving {0,1,2,3,4} which in turn gives 1000^0, 1000^1, 1000^2 etc & then E2:H2 is divided by those numbers.
So E2 is divided by 1 F2 by 1000, G2 by 1000000 etc

Brilliant. Thanks for your solution and your patience.

 

[Fluff]

You're welcome & thanks for the feedback.