Keep Score with Excel
Amber MacArthur, Bill Jelen, Leo Laporte, Andy Walker on the set of Call for Help on TechTV Canada. Tips shown in this episode feature some spreadsheets from The Spreadsheet at 25: The Evolution of the Invention that Changed the World".
Also mentioned on the show, you can get a free chapter a week from Learn Excel from MrExcel by signing up here.
Here is a simple worksheet for keeping score of a baseball game. When you try to enter the formula in K3, the AutoSum button won't know what you are trying to sum.
Using the mouse, highlight cells B3:J3.
Copy K3 and paste to K4. Now, as you start to enter scores throughout the ball game, the formulas in K3 and K4 will automatically recalculate to show the run total. Cards
The spreadsheet for card scoring is similar to baseball scoring, except the scores are entered down the worksheet. The formulas in B21 and D21 keep track of the total score.
Scoring for a game of 301 with Darts is a bit more complex. You start with a score of 301 and attempt to work down to zero by deducting the points from each throw. The formula in B21 is =301-SUM(B4:B20).
Many places with dart board have a poster listing the "Natural Outs". These are the dart combinations that would produce the perfect score to go out. I found one of these lists and pasted it off to the side of my Excel worksheet. As shown here, the list suggests that if you have 128 points left, you should try to throw a Triple 18, Triple 14, and then a Double 16.
Using a VLOOKUP function, I was able to take the current score in D21 and find the natural out. Row 22 has a pair of formulas to show the natural out for my scoreboard.
On Call for Help, I showed three screenshots of applications included in the book. Download scores.zip for examples of baseball, bowling, and card scoring.
For the BEST TV show on technology, check out Call for Help. This tip was originally published on June 3, 2005 and aired on TechTV on June 20, 2005. The permanent URL for this page is http://www.mrexcel.com/tip094.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Excel is a registered trademark
of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.
All contents Copyright 1998-2008 by MrExcel Consulting.