Looking for IF statement formula

[JTL9161]

I am trying to create a spreadsheet that will tabulate the dollar amount a golfer would win in a league using the Skins format.

I already have a spreadsheet that lists each golfer score per hole then, I have it calulate the net difference between the 2 golfers per hole. The net is a result of an handicap (if needed) being assed on a golfer. For example Golfer A's handicap is a 10 and Golfer B's handicap is a 11 that means golfer B will get a stroke deduction one hole. So, if golfer A gets a 4 and golfer B gets a 5 then they halve or tie the hole with a score of 4.

I am now on a different spreadsheet I am looking for an IF statement (i think) that would look at the net difference per hole and reward $1 to the golfer with the lowest score on that hole. Now if the net score on a hole ties the hole I need that $1 to carry over to the next hole making it worth $2, and so on so on until there is a distict winner on a hole thus winning the carried over amount. For example, Golfer A and B tie the first 3 holes so now hole for is worth $4 ($3 carried over and $1 for the actual value of the hole) If Golfer A wins the hole by a score of 4 to 5 the net difference is -1 and Golfer A wins the $4. Then hole #5 starts over and is worth $1. If a tie runs through till the 9th hole then that money stays in the pot and no one wins it.

Please suggest how I can accomplish this. My spreadsheet to calculate the Skins won is a mirror of the one that shows the scores and differences per hole. Here is an example:
For the sake of the example lets say that both golfers have the same handicap so no strokes are awarded.

GOLFER A SCORES 5 - 5 - 4 - 3 - 6 - 5 - 4 - 3 - 5
GOLFER B SCORES 6 - 6 - 4 - 4 - 5 - 5 - 3 - 4 - 5
Difference -1 -1 0 -1 +1 0 +1 -1 0

In this example Golfer A wins holes 1,2,4 & 8. Golfer B wins holes 5 & 7. Holes 3,6 & 9 are ties or carryovers.

Total dollars won for Golfer A is $1 for hole 1 & 2 each, $2 for winning hole 4 (carryover from #3) and he wins $1 on hole #8 for a total won of $5
Golfer B wins dollars on holes #5 and wins $2 for winning hole #7 (carrryover from #6) for a total of $3. $1 dollar is left in the pot because of the tie on hole #9

Hope I explained this good enough to understand what I looking for. This is something new we are doing this year for our 32 man league and I need something that will clarfy each golfers winnings week to week.

Thank you in advance.
James

 

[awoohaw]

For the top solution when they tie on the 1st hole I get an erro

to get rid of the error in row 4, put this in B4: =IF(B3=B2,N(A4)+1,0), and copy over.
But, @Fluff 's solution reduces the number of helper rows.

 

[awoohaw]

1 more tweak. How do I revise the formula's so the columns B through I are worth $1 and just column J (9th or 18th hole) is worth $2.?

OOH.. The last hole pressure twist!

 

[awoohaw]

OOH.. The last hole pressure twist!

If the 9th carries over, is it still worth double?

 

[awoohaw]

This carries over the double value for 9th hole.

Mr Excel Questions2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Hole	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	Golfer1	5	4	3	4	6	4	7	7	5	3	7	6	4	6	3	3	4	7	9
3	Golfer2	5	4	3	5	5	4	7	6	6	7	3	7	3	5	4	3	5	6	11
4	Carryover	1	2	3	0	0	1	2	0	0	0	0	0	0	0	0	1	0	0
5	Win	0	0	0	-4	1	0	0	3	-2	-1	1	-1	1	1	-1	0	-2	2
Skins Game Golf

Cell Formulas
Range	Formula
T2	T2	=SUMIFS(B5:S5,B5:S5,">"&0)
T3	T3	=-SUMIFS(B5:S5,B5:S5,"<"&0)
B4:S4	B4	=IF(B3=B2,N(A4)+1,0)
B5:S5	B5	=IF(B3=B2,0,IF( B2>B3, N(A4)+1 + IF(MOD(B1,9)=0,1,0), -N(A4)-1 - IF(MOD(B1,9)=0,1,0) ))

 

[JTL9161]

Ok, how about

=SUM(IF(B2:J2<B3:J3,1+B4:J4,0))+(J2<J3)

That goes in K2 & K3?

 

[Fluff]

In K2, you just need to add the bit at the end to the previous K3 formula & change the < to >

 

[JTL9161]

In K2, you just need to add the bit at the end to the previous K3 formula & change the < to >

Once again...perfect! thanks.

 

[JTL9161]

Once again...perfect! thanks.

One more thing please. If I want to change the amount per hole (1 through 8) for example $1 down to .50. I change all the 1's to .50?

 

[Fluff]

That's right.

 

[JTL9161]

That's right.

That's what I thought but when I change it to .50 (last hole worth $1) It should equal a total of $5 but I get Golfer A with $5.50 and Golfer B with $0