Creating a Spades scoresheet in Numbers (sandbags)

Hi James,

A) See the first table to see the adjustment I made to the scoring, and the two paragraphs above for the reason I made that change.

B) The two trick overage in round 9 was chosen deliberately to force the sandbag total to reach 10 + 1 in that row. According to the rules (with the sandbag trigger amounts adjusted as described) as I read your description these things should happen at round 9 (see my first table to follow this):

• At the end of round 8, the player has 50 points + nine sandbags for a total of 50.09.
• During round 9 the player makes his bid (+20 points) and one extra trick (+0.01, or 1 sandbags)
• As the sandbag total reaches 10, the player's score is dropped 100 points (-100), and the sandbag total is reset to zero (-0.10)
• By the end of round nine, the player has won one more trick (+0,01)
• Net result: 50.09 +20 +0.1 -100 -0.10 +0.01 = -29.99

Note that the values displayed in the Score and Total columns of the first table are identical to those contained in the same cells of the second table. If the sandbag score is significant beyond it's use as a trigger value for the 100 point clawback, then the total score including one point for each sandbag is easily calculated from the actual value in the Total column (column E). Here's the table again, with the same actual values in columns A through F as in both tables above, displayed to the same number of decimal places (0) as in the second table above.

The added column (G) shows th running total, including one point for each sandbag, with sandbags set to zero on reaching 10.

G2, and filled down: =IF(LEN(E)<1,"",INT(E)+100*(E-INT(E)))

The original Total column may be hidden if desired, but is essential to the calculations, and cannot be removed.

Regards,

Barry

Hi adam,

The sandbag count is most significant as a trigger to a 100 point drop in the score each time it reaches a multiple of 10. With a step of 1 for each sandbag, it is difficult to separate a sandbag count of ten from a score that reached a multiple of ten without involving sandbags.

Changing the point value of each sandbag from 1 to 0.01 makes that trigger easier to detect (and reset). Here's an example, using a modified version of your formula:

Formulas:

D2, and filled down:

=IF(LEN(C)<1,"",IF(B=0,IF(C>B,-100,100),IF(C<B,B*-10,B*10+(C-B)*0.01)))

E2, and filled down:

=IF(LEN(D)<1,"",IF(SUM(E1,D2)-INT(SUM(E1,D2))>=0.1,SUM(E1,D2)-100.1,SUM(E1,D2)))

F2, and filled down:

=IF(LEN(E)<1,"",100*(E-INT(E)))

The non-bolded part of each formula is a test for the presence of a value in the column to the left of the formula. If 'nothing' has yet been entered there (by the user, or by the formula in that cell), calculation is suppressed and a null string is placed in the formula's cell (see rounds 11 through 13). If there is data in the cel, then the bold part of the formula is run.

D: This is a streamlined version of your formula, requiring one fewer IF statement.

IF the bid is zero, then the number of tricks won can be either greater than 0 or not greater than zero. In the first case, the score for the round is -100, in the second, it's 100.

If the bid is greater than zero, there are only two different calculations: If fewer tricks are won than bid, the score is the bid times -10. If the number of tricks won is equal to or greater than the bid, then the score is the bid times 10, plus the difference between the bid and the tricks won times 0.01 (or in the original, times 1) to determine the mumber of sandbags.

E: This calculates the running total, and does the 100 point subtraction (and resetting of the sandbag count) when that count reaches 10 (hundredths). Note that in the example, the count has gone to 11 (9 + 2, and is reset to 1 after Round 9). Because the sandbag count is always in the range 0 to plus 9, and the total score in that round is negative, the score plus sandbag total displayed is closer to zero than the point score sans sandbags (-30).

F: This shows the current sandbag count in integers, by extracting the fractional part of the running total and multiplying it by 100. Included for display more than for any other purpose.

Consider formatting columns E and F as numbers, both with 0 decimal places. The result would be as below:

Regards,

Barry

Hi Adam,

"As the rule is right now, the setback happens as the person goes over 10 sandbags. It should go into effect as the person goes over 9 sandbags (and since you cant have partial sandbags, it would be as soon as the person hits 10). "

Here is the table, showing only the round where the sandbag count reaches 10 and the round before it. I've set the score at the end of Round 8 to 159 to avoid a negative total aftef the score is reset by the sandbag trigger. A trick by trick description is shown below the table.

End of round 8: Player's total score (H) is 159. His sandbag count (G) is 9

Cards are dealt and player bids 2 tricks. If no changes: D: -20, E: 0, G: 9, H: 139.

-- Player wins first trick: If no further changes: D: -20, E: 0, G: 9, H: 139.

-- Player wins second trick: If no further changes: D: 20, E: 0, G: 9, H: 159.

-- Player wins third trick: If no further changes: D: 20, E: 1, G:10, H: 160.

-- SB count reaches 10, total -100, SB count reset: D: 20, E: 1, G: 0, H: 60.

-- Player wins fourth trick: Round ends: D: 20, E: 2, G: 1, H: 81.

"The reset of your sandbag does not hurt you."

I realized this morning that I had subtracted the 100 points from the 'trick' scores (column F) AND reset the sandbag counter, with the result that the total had been reduced by 110. Revised formula below. I also checked the formula with the player winning only 3 tricks in round 9, and found that rounding errors inherent in decimal arithmetic being done in binary made the resuting sandbag count fractionally less than 10. Dropping the reset trigger to the equivalent of 9 1/2 sandbags made the reset occur when the count went higher than 9.

F2, and filled down:

=ROUND(IF(LEN(D)<1,"",IF(SUM(F1,D2)-INT(SUM(F1,D2))=0.095,SUM(F1,D2)-90.1,SUM(F1 ,D2))),2)

Column F, as mentioned earlier, is key to the operation of the table, but does not need to be visible. Here's the revised table, with column F hidden:

Other formulas (except column E, which is new) will have changed due to the added column at E. I've pasted them below for reference, but they should update automatically when column E is added.

D2: =IF(LEN(C)<1,"",IF(B=0,IF(C>B,-100,100),IF(C<B,B*-10,B*10+(C-B)*0.01)))

(no change)

E2: =IF(C<=B2,"",C-B)

(new)

F2: See above image

(Revised)

G2: =IF(LEN(F)<1,"",INT(F)+100*(F-INT(F)))

(auto adjusted when column E inserted)

H2: =IF(LEN(F)<1,"",100*(F-INT(F)))

(auto adjusted when column E inserted)

Regards,

Barry

Hi Adam,

Sorry about the name mixup above. Different question.

An interpretation question regarding sandbags. Your original description said:

"...If you bid 4 and you win 5, your score will be 41 ((10x4)+1). The extra 1 counts as a sandbag. When your sandbag count reaches ten, you subtract your score by 100, and you reset your sandbag count."

Does the reset of your sandbag count reduce your total score by a further 10, or is its effect on the score included in the 100 point reduction triggered by the the count reaching 10?

Regards,

Barry

Barry,

I was also trying to create a spades scoresheet. I dont have the Excel skillset you have, but I can follow your formulas (almost).

A lot of the time spades is played with 4 people on 2 teams:

Each player bids independantly

The bids are aggregated per team

The tricks are also aggregated per team

Scoring is the same

Bags are aggregated per team also. Ten bags sends the team back 100.

Nil is the tricky part, because you do NOT aggregate the tricks per team. The person who bids nil counts his tricks separately from his partner. In the case of nil, each player gets his own score for the hand, which is then aggregated for their team.

Any suggestions?

Thanks

It would be way more fun to play spades after this is done.