Spreadsheet: 80830d1281013741-formula-that-automatically-uses-the-last-cell-in-a-row-copy-of-golfpressespaymentexample
Sheet: Sheet1

Downs
Score | Hole 1 | Hole 2 | Hole 3 | Hole 4 | Hole 5 | Hole 6 | Hole 7 | Hole 8 | Hole 9
Player1 | 4 | 4 | 5 | 3 | 4 | 4 | 5 | 4 | 3
Player2 | 3 | 4 | 3 | 4 | 3 | 4 | 3 | 4 | 3 |  | Hole 5 is the same as hole 3.  Each bet is a $5 bet  You start a new bet when a player is down 2.    In one bet you may be down by six - but it's only one bet and it's only a $5 bet
$5 2-Downs | -5 | -5 | -5 | 0 | -5 | -5 | -10 | -10 | -10 |  | I think on hole 5 plaryer 1 lost $10 not $5.
Player 1 | -1 | -1 | -2 | -1 | -2 | -2 | -3 | -3 | -3 | <- Running Tot
 |  |  | 0 | 1 | 0 | 0 | -1 | -1 | -1 |  | 2-Down Rules:?
 |  |  |  |  |  |  |  |  |  |  | If a player goes down by 2 then the next holes doubles in value
[NAME] |  |  |  |  |  |  |  |  |  |  | It doubles again if a player goes down by 4 holes are 4 times the value
Running Totals |  |  |  |  |  |  |  |  |  |  | Nothing ever doubles.  You just start a NEW bet when a player goes down 2
Abs of Run Tot
Hole $ [NAME]
Running Tot $
$5 1-Downs | -5 | -5 | -10 | 0 | -15 | -15 | -25 | -25 | -25
Player 1 | -1 | -1 | -2 | -1 | -2 | -2 | -3 | -3 | -3 |  | 1-Down Rules:?
 | 0 | 0 | -1 | 0 | -1 | -1 | -2 | -2 | -2 |  | If a player goes down a hole then the next holes double in value
 |  |  | 0 | 1 | 0 | 0 | -1 | -1 | -2 |  | If a player goes down by 2 holes then it doubles again
 |  |  |  | 0 | -1 | -1 | -2 | -2 | -2 |  | This one is tough to call, but again nothing ever doubles.  You just start a NEW bet when a player goes down 1
 |  |  |  |  | 0 | 0 | -1 | -1 | -1 |  | Player 1 wins hole 1, that's one bet, he wins hole 2, he is now up 2 bets.  On hole three player 2 finally wins - so he goes from down 2 to down 1 on the first bet (the first bet is still only worth $5 or one bet no matter how much he is down), he suares bet 2, wins bet 3 and a new bet 4 is started.
 |  |  |  |  |  |  | 0 | 0 | 0 |  | I screwed up hole 5, player 2 was ahead in bet 3, so when he lost hole 5 that bet became all square.  Therefore on holes 5 & 6, player 1 is only down $15 not $20
 |  |  |  |  |  |  |  |  |  |  | 1-downs are VERY hard to call - that is why I wanted to get a formula.
[NAME] |  |  |  |  |  |  |  |  |  |  | Main thing to remember is no bets EVER double.  A bet is only worth what is agreed on up front, this example is $5 bets.  Even if a player goes down by 9 on one bet, it's still only one bet and he's only lost $5.
[NAME] |  |  |  |  |  |  |  |  |  |  | Now what happens is if you are playing 1 downs a NEW bet automatically starts when a player goes down 1.  If you're playing 2-downs, a new bet automatically starts when a player goes down 2 on the previous bet.
Hole $ Value |  |  |  |  |  |  |  |  |  |  | Some people get confused thinking that if a player goes down 4 on the first bet, then anohter bet is started.  That is not the case becase bet 1 can only start bet 2.  Once a bet is started it stays for the entire round.
[NAME] |  |  |  |  |  |  |  |  |  |  | So there is only 1 bet at the start and when a player has lost 2 holes a second bet is started
Hints: - Only calculate player 1 and player 2 will be the opposite value.
1. Treat each hole as halved (0), Won (1) or Lost by Player 1 using =If(B3=[NAME]<B4,1,-1)
2. You need a running total of holes ahead or down by player 1 to determine the value of next hole.
3. You need an absolute value of this total in 2 for the choose function next
4. The value of each hole is determined by a choose function using 3 above - Choose(Max (of Abs of Run Tots),5,10,10,20,20,40) for 2-down and …,5,10,30,40,50,60) for 1-down.
I'm not sure how to roll this up into a single equation.  I think my answer is correct using separate rows for each breakdown of the problem.