formula(s) to count points that "carry-over" or build if not won... then drop back to 1 after a win??

[650nmWolf]

I had a hard time thinking of a good title for this since it is hard to explain. here is what I am hoping to achieve...

• There are four individual games that are each worth a point. If someone wins the game, then they win 1 point. If nobody wins the game, then that point "carries over" and the next game is worth 2 points; if nobdody wins that game, then the next game (3rd) is worth 3 points. If somebody wins that 3rd game, they win all 3 points (1 point for that game and they clean up the 2 points that carried over). The final and 4th game would then go back to 1 point.

• Question: I need a formula that can calculate the number of points each game is worth? It could be anywhere from 1 to 4 points each. Points carryover if nobody wins. If nobody ever wins, then no points are won. When a person wins, they get all the points that were not previously won or had been "cleaned up" by a previous winner.

• I have a column for each game and one cell in that column has a drop down to show if that game was "won" or "pushed."

Does my explanation above even make sense? It seems so simple, but my small brain is exploding trying to get this right.

 

[shg]

How about ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Win​

[/td][td="bgcolor:#F3F3F3"]

Game Val​

[/td][td="bgcolor:#F3F3F3"]

A​

[/td][td="bgcolor:#F3F3F3"]

B​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]A[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCECFF"]

1​

[/td][td="bgcolor:#CCECFF"]

0​

[/td][td][/td][td="bgcolor:#CCFFCC"]B2: =N(B1) * (A1 = "Push") + 1[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCECFF"]

1​

[/td][td="bgcolor:#CCECFF"]

0​

[/td][td][/td][td="bgcolor:#CCECFF"]C2: =N(C1) + ($A2 = C$1) * $B2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td="bgcolor:#CCECFF"]

1​

[/td][td="bgcolor:#CCECFF"]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]B[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td="bgcolor:#CCECFF"]

1​

[/td][td="bgcolor:#CCECFF"]

3​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]A[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCECFF"]

2​

[/td][td="bgcolor:#CCECFF"]

3​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]B[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCECFF"]

2​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCECFF"]

2​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]A[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

4​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Push[/td][td="bgcolor:#CCFFCC"]

5​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]B[/td][td="bgcolor:#CCFFCC"]

6​

[/td][td="bgcolor:#CCECFF"]

4​

[/td][td="bgcolor:#CCECFF"]

10​

[/td][td][/td][td][/td][/tr]
[/table]

 

[650nmWolf]

shg,
thank you very much for this! that was awesome. I was educated on the "N" function and this worked perfectly.

 

[shg]

Y'er welcome, Red.