Pitch is an American trick-taking card game derived from the English game of All Fours (Seven Up). … The modern game involving a bidding phase and setting back a party’s score if the bid is not reached.
The only “real” pitch is four-point, keep low, bid or set to win. For my tournament, we don’t shoot the moon and you can sneak out (win without bidding or setting). That keeps things moving so games don’t last forever. Each two-man team plays three sets of five games per night. As you might imagine, I keep score in Excel.
The spreadsheet consists of a Summary page, a printable Score Sheet, a Data page (for recording games), a Tables page for recording player names, and two pre-made schedules for 16 and 18 teams. The key to good scorekeeping is no errors, so I have a few error checking features in the spreadsheet. You can see above that I have a wins row that always equals 135. If I don’t have 135 wins for the week (18 teams * 15 games * 1/2 wins), then I made an error.
On the Data page, I have a Dupes column.
The formula is {=SUM((Date=A2)*(Team=B2))}, which ensures that each team is only represented three times per week (once each for three sets). If it’s more or less than three, I made an error and I need to go decipher some handwriting on the scoresheet. The Dupes column has condition formatting applied that alerts me when an error exists.
Another error-catching feature is conditional formatting on the Wins column.
For every even row, it sums itself and the row just below it. These should always equal five, as each set is five games. This only works because I enter the data a certain way. If I wanted to be order-agnostic, I would need to have a Set ID and adjust the formula accordingly. Any yellow that shows up indicates either an error, or somebody didn’t show and needs to make up the game.
If you go “set” in Pitch, it means that you won the bid but did not make the number. It’s a badge of honor to have a lot of sets. If you’re not going set, you’re probably not bidding your hand aggressively enough. Of course there is such a thing as going set too much, or so I’ve heard. On the Summary page, I show the set leaders.
I start by computing the number, 92 for the first example. That starts on the Data tab
I list each team, one through 18, and use =SUMIF(Team,G2,Sets)+ROW()/10000 to add up the sets. Then I add a really small number to the end based on the row to split ties. Back on Summary, =LARGE(TotalSets,ROW()-29) returns 92.0015 (the set table starts on row 30, so this gets the largest value). When I copy this formula down, it gets the 2nd largest value, 3rd largest value, etc. To get the team number, I match the score with =MATCH(J30,TotalSets,FALSE) and then use a VLOOKUP to pull the short team name.
The skunk roll (a skunk is when you go zero-for-five in a set) is done in a similar manner. On the Data page, I compute the skunks with {=SUM((Team=R2)*(Wins=0))+ROW()/100000}. That adds up all the zero win sets for each team. I report the LARGEst ones and use the same MATCH and VLOOKUP formulas to get the team numbers and names.
When I only have 17 teams, as I did this year, I use the 18 team schedule and make team 18 the BYE team. When the schedule says that you play team 18, you get five points. I still record the games, giving team 18 zero points and their opponents five so all the error checking formulas work. I also use team 18 to balance the sets because invariably they don’t match the actual money collected.
Finally, I have one macro (you knew I would). It finds the next Thursday on the schedule and puts a big thick border around it. The code isn’t very interesting, but here’s the part that finds the next Thursday
dtNextThursday = Date
Else
dtNextThursday = Now + Abs((Weekday(Now) >= 5)) * 7 – Weekday(Now) + 5
End If
If I’m being a derelict and not updating the scores until the next Thursday (Weekday(Now) = 5), then today is the next Thursday. Otherwise it uses some formula I undoubtedly stole of the internet, but have forgotten where. Probably Chip Pearson’s site.
In case you’re wondering, the standings and the schedule are on the same page because I print them duplex.
You can download PitchTemplate.zip
Posting code? Use <pre> tags for VBA and <code> tags for inline.