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

 

[Fluff]

In that case do it like

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

 

[JTL9161]

I'm not getting something. I have this in K2 =SUM(IF(B2:J2<B3:J3,0.5+B4:J4,0))+(J2<J3)*0.5 and this in K3 =SUM(IF(B2:J2>B3:J3,0.5+B4:J4,0))+(J2>J3)*0.5

Hole​	1​	2​	3​	4​	5​	6​	7​	8​	9​
GOLFER A​	5​	5​	3​	3​	4​	4​	2​	3​	4​	5
GOLFER B​	6​	6​	4​	4​	5​	5​	3​	4​	5​	0
Carryover​	0​	0​	0​	0​	0​	0​	0​	0​	0​

 

[Fluff]

What do you expect to get?

 

[JTL9161]

What do you expect to get?

Nevermind...sorry. I was looking at it and thought I had .25 instead of .50 Thanks again. Stay warm...wherever you are. 20 degrees and with wind 5 below. Staying in

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[JTL9161]

Hello again. Doing all possible testing I found if in a round of golf all the holes are tied (carryover on every hole) It give golfer B all the money when no money was won. Solution?

 

[Fluff]

Ok, for K2 use

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

 

[JTL9161]

Ok thanks. I doubt any round would end up that way but I had to test it.

 

[JTL9161]

Last thing. I promise. I have a formula that tells me how much money is going to the pot, meaning for example the last hole is tied. Is there a (IF?) formula that can tell how many .50 carryovers are left (if any) and if the last hole was carried over? Example: hole 8 and 9 end up tied so that means $1.50 remains but that is only 2 carryovers, 1 50 cent carry over and 1 $1 carry over. Can I get the number 1 under a new cell named .50 carryover and the number 1 under a new cell named $1 carry over? Obviously hole 9 could be the only carryover so $1.00 would remain so .50 carryovers would be 0 and $1 carryover would be 1.

Remaining Balance .50 Carryover $1 carryover
$1.50 1 1

 

[Fluff]

As this is a totally different question, it needs a new thread.