Looking for IF statement formula

JTL9161

Well-known Member
Joined
Aug 29, 2012
Messages
582
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
This involves some helper rows. I'm sure it could be done with LAMBDA and LET. This is for a 2 man skins game, not an individual threesome or foursome.

Book1
ABCDEFGHIJKLMNOPQRST
1Hole123456789101112131415161718
2Golfer14437647753764633478
3Golfer254355477673735435610
4Carryover012001230000000100
5Win-10031000-4-11-111-10-21
Sheet3
Cell Formulas
RangeFormula
T2T2=SUMIFS(B5:S5,B5:S5,">"&0)
T3T3=-SUMIFS(B5:S5,B5:S5,"<"&0)
B4:S4B4=IF(B3=B2,A4+1,0)
B5:S5B5=IF(B3=B2,0,IF(B2>B3,N(A4)+1,-N(A4)-1))
 
Upvote 0
Another option
Fluff.xlsm
ABCDEFGHIJK
1$ Won
2Golfer A5543654355
3Golfer B6644553453
Data
Cell Formulas
RangeFormula
K2K2=LET(s,SCAN(0,SEQUENCE(,COLUMNS(A2:I2)),LAMBDA(a,b,IF(INDEX(A2:J2,,b)=INDEX(A3:J3,,b),a+1,0))),SUM(IF(B2:J2<B3:J3,1+s,0)))
K3K3=LET(s,SCAN(0,SEQUENCE(,COLUMNS(A2:I2)),LAMBDA(a,b,IF(INDEX(A2:J2,,b)=INDEX(A3:J3,,b),a+1,0))),SUM(IF(B2:J2>B3:J3,1+s,0)))
 
Upvote 0
@Fluff ... I was pretty sure this would have involved a SCAN. Very nice!
 
Upvote 0
Sorry but when I dupicate yours above I am getting an error.

GOLFER A
5​
5​
4​
3​
6​
5​
4​
3​
5​
#NAME?​
GOLFER B
6​
6​
4​
4​
5​
5​
3​
4​
5​
#NAME?​


=LET(S,SCAN(0,SEQUENCE(,COLUMNS(A2:I2)),LAMBDA(a,b,IF(INDEX(A2:J2,,b)=INDEX(A3:J3,,b),a+1,0))),SUM(IF(B2:J2<B3:J3,1+S,0)))
=LET(s,SCAN(0,SEQUENCE(,COLUMNS(A2:I2)),LAMBDA(a,b,IF(INDEX(A2:J2,,b)=INDEX(A3:J3,,b),a+1,0))),SUM(IF(B2:J2>B3:J3,1+S,0)))

am I missing something?
 
Upvote 0
Do you have the SCAN function?
 
Upvote 0
no..i don't you mean scan like can a document?
 
Upvote 0
No I mean the SCAN function as used in the formula. If you type =s into a cell is scan one the the auto complete options?
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top