Scoring spreadsheet

I have been asked how I keep track of scoring for my series so I will illuminate that issue. A bit of warning, this post is going to get long and complicated. I will attempt to K.I.S.S. (Keep it simple, stupid!) and streamlined as possible though it will address spreadsheet coding. I have decided to use quote boxes to keep segments separate.

This is a scoring book I created over time using a lot of trial and error. I'll share my findings with you in hopes your usage will find better or more efficient ways to improve it; please help make this sheet better by providing your findings or input. I believe in teaching someone how to perform a task on their own, rather than providing them a finished product. The end result is you'll have your own scoring spreadsheet and you're going to learn a little on the way.

How do I take pictures of my spreadsheet? I am on a Windows computer and I use the program SnagIt by TechSmith. It's a free-to-try program though the free version stamps a watermark in each capture's lower corner. This can be combated by capturing an area a little larger than you need and using another program to crop and exclude their watermark.

Windows and Macintosh operating systems can take screen captures (Windows = PrtSc button, Mac = Apple+Shift+3) but I like SnagIt's extra features. For Windows if you use PrtSc you can enter MS Paint and paste (CTRL+V) then crop what you need. When it comes to the Print Screen button and pasting, I prefer paint.net as my editing program. I also utilize paint.net for color sampling but that's another thread. Enough about that, on to spreadsheet stuff.

My spreadsheet has many pages, more pages than are necessary but I like keeping track of multiple aspects. To clarify the directions, I am using Excel from Microsoft Office. Columns are listed at top using the alphabet and are vertical. Rows are listed at left using numbers and are horizontal. Oracle provided OpenOffice.org Calc as a free (and freely customizable) spreadsheet program though this sample does not address it.

My first tab is named Driver List and consists of four main columns:

• In cell A1 write Identification
  
• In cell B1 write Class
  
• In cell C1 write Manufacturer and
  
• In cell D1 write Number
  This sheet does not have any references from other sheets so you may rearrange the four previously listed columns if you wish.
  
• Identification is a column of every racer's name.
  
• Class is a suggestion and is only needed if you are hosting a multi-class event. I hosted an American Iron season so I categorized the racers by AI and AIX classes.
  
• The manufacturer column is another suggestion because not every series is going to have a manufacturer championship.
  
• The driver's car number slides into the number column.

I highlight the five columns and choose Window / Freeze Panes. That isn't a typographical. In this instance I've found highlighting A → E will lock A → D, which is what we want to accomplish. Highlight starting with the column title, the actual letter; don't individually select a few cells under the A → E columns.

Congratulations, one tab is complete. Below is a visual example.

I am going to skip ahead to my ____ Points tabs. These aren't my next tabs; in my book they're tabs 6, 7, 8, and 9. I must skip tabs for now to provide more relevant background information. Tabs may be rearranged later anyway by holding down the left mouse button on one tab name and moving the mouse left or right relative to the other tabs.

In Excel the name box is a white box in the upper left, a little below File and Edit. Normally it displays the selected cell's alphanumeric grid code. I'll be referring to this location, for it becomes necessary in this step.

I created separate tabs for a handful of assorted series: ALMS, CMC, NASCAR, and Grand-Am. One could create a new tab for any series they study and list the point schedule for use in their race series. Within any one of these tabs is the following format:

• A1 is the only cell with anything listed within that column; A1 is the name of the series whose points are listed. This isn't necessary though I find it helpful to keep my information at a constant eye-level.
  
• Column B has a listing of finishing places in numerical order. Example: B1 = 1 … B6 = 6 … B20 = 20, et al. Your finished B column should have an ordinal list of numbers from 1 through [however many positions you wish to track], say 25. Select/highlight all of these numbers, click inside the name box and type a custom name for this selected group. Spaces are not permitted so if you want a two_word name use an underscore as I showed.
  
• Column C is the first column that lists points from first through last. My ALMS Points tab has different points listed from columns C through column M. The real ALMS point schedule allows for 20 competitors per class and is in one of my columns. I modified their point schedule to allow for a greater or a fewer number of competitors. These altered point schedules flank the real point schedule.

In whatever column of points you decide to use, in whichever of your ____ Points tabs, highlight every point value from greatest to least, we're going to do the same here as we did in column B. Also, repeat this step if you wish to hold a separate points battle, like a manufacturer championship.

I named my three: Place, Drivers_Points, and Manufacturers_Points. Remember your inputted names as they're important for use elsewhere. If you forgot your chosen name, re-highlight the same boxes and what you typed will appear in the name box.

This looks like a good place to talk about defined names. If you really forget what name you've assigned a group of cells, click these following toolbar links: Insert / Name / Define and a pop-up window will appear displaying your defined names. From here you may choose one of a few options, among them, deleting. If you made a mistake or don't like the name you selected, you may nuke the definition and start over.

Hey, check it out! Depending upon how many series points you've researched, you're done with that many more tabs.

My second tab is named Race List and this is where more technical processes begin. I have not only scored driver points and manufacturer points, I've also kept points across two classes. If you don't host multi-class racing, skip my instructions for a second class.

Before you begin merging cells, highlight A → D then select Window / Freeze Panes. The goal is to freeze columns A → C together.

• A1 & A2 merged = Literally, write Driver Name here, it's a heading. (Exactly as it says. All rows below A1 & A2 are the driver names).
  
• B1 & B2 merged = Literally, write Driver Number here, it's a heading. (Exactly as it says. All rows below B1 are the car numbers).
  
• C1 & C2 merged = Literally, write Class here, it's a heading. (Not necessary unless hosting a multi-class event).

The following steps are repeated for as many tracks as you'll visit in a season.

• D1, E1, F1, & G1 merged = Write your first race track's name here. Not literally, this is not a heading. This is a race venue's title.
  
• D2 = Literally, write Time here, it's a heading.
  
• E2 = Literally, write Place here, it's a heading.
  
• F2 & G2 merged = Literally, write Points here, it's a heading. Note: F2 & G2 are merged though rows below them are not. This merged cell commands two separate columns, below.
  
• For every following race venue the next four cells in row one are merged, then follow and adapt the three directions written immediately above this directive.

The cells below Time and Place are where you'll enter numerical values upon race completion. I highlight every relevant cell beneath Time, right-click, select Format Cells… and the Number tab then choose the tenth option, Text.

The first Points cell (column F in this example) is for an adjustment of points. They could be positive, as in a couple of points for: best qualifying time, fastest race lap time, led a lap, most laps led, etc. or they could be negative because a driver's racing was unclean. This cell will consist of a numerical value you enter and will be tallied with points earned for their position. Example: if you enter 6 that driver will see 6 points more than their earned positional points. If you enter -4 that driver will see a deduction of 4 from their earned positional points. I gave this column the same Text treatment as I exemplified for Time in the above paragraph.

The previous segment was becoming too large so I broke it into pieces. This is still a part of my Race List tab.
Okay, we've reached column G in our example and a line of code is to follow. This caused me a bit of anguish in my early attempts to create this sheet so I'll give you a freebee. Enter the below line of code, substituting its blue text bits for your placement name, file name, and whatever you called your grouping of points.

=IF(E3>=1,LOOKUP(E3,Place,'Amer Iron Points.xls'!Drivers_Points)+F3,"0")
Enter this code in G3. If you have 16 racers drag the lower right corner of cell G3 through cell G18. If you have 12 racers drag it through cell G14. This should copy all relevant information to the cells below, keeping separate the proper formatting.

In layman's terms the program is going through this process: If there's a number in E4 greater than or equal to 1, I am going to reference the [placement numbers] saved in [this named file]. I will count down the same number of cells in [your point scoring schedule] equal to the number in E4 and refer to the corresponding scoring value. I'll also take into account whatever value is contained in F4 then I'll spit out the completed result in G4. If there is no number greater than or equal to 1 in cell E4 I will output 0 in G4.

So in summary, when you input a driver's finishing place in number form (we've told the program it's text) in column E, the corresponding point value (from another tab) will display in the same row of column G. Also, any added or subtracted values from column F will be tallied into that score, everything happening on the fly. Column D, the time, is up to you to input; for now it's only for display purposes. I'd like to make things more complex and find a way for the spreadsheet to compare all driver times listed in D, then assign a positional value in column E and output a scoring value in column G, with column F having the only other manually inputted cells.

Your sheet should now have information in columns A → G. Of those frames A → C are frozen panes of driver information and frames D → G are track information for the first track in your season. Every four frames following G will be a group mirroring what we've done with D → G. If you host a 10-race season you will finish with information in cells AN → AQ and 15 races will get into cells BH → BK. Copy the information from the earlier cells to the later cells, adapting the new location information into the formula.

Also in my Race List tab is where I keep my running total for the manufacturer championship. If you don't have a manufacturer championship, you may skip this segment.

Hopefully you now have your tracks listed horizontally in columns (each a pairing of four columns) and your racers should be stacked vertically in the rows from A3 → A18. If you have more or less racers that second figure will vary. Group the A, B, and C cells below your final racer's name (only 1 row worth), then type in a class name. Examples: American Iron, American Iron Extreme, Touring Class, Grand Touring Class, et. al. Below this class name, group A, B, and C cells (again only one row worth) for each manufacturer you'll keep score.

I had two classes racing and keeping score for three manufacturers below each class. In this case I needed four rows (merging columns A, B, & C in each row). The first row is the class name, the next three rows are room for the manufacturer. My next class had the same information below those four cells. Extending to columns D, E, F, and G, we have D and E merged, and F and G merged. These columns should look familiar, they're where the track information is stored. Below every track (event) is where we're going to keep scoring information for manufacturer points, too. Since time is not an issue for the manufacturer championship, write Place in the D-E merged cells, and Points in the F-G merged cells. These two columns are similar to E, F, and G above in that, when you input a place in D-E, points will output in F-G. Below is another freebee line of code. As in above, change any blue text with names you've selected.

=IF(D27>=1,LOOKUP(D27,Place,'Amer Iron Points.xls'!Manufacturers_Points),"0")
Enter this line of code in column F (merged with column G) in the first row where you want to keep track of manufacturer points. Your exact placement will vary so you may not use "27" though "D" is correct; the row will vary with how many competitors you have. Here again, once you have the correct information in the correct "D" column, drag the lower right corner of cell to the next cells where you wish to keep track of manufacturer points.

Below is a video example. Watch it in YouTube on full-screen.

My third listed tab I have named Point Totals. This tab is fairly easy as no cells are merged. It does contain some long strings of addition. Your column count may vary, depending upon how many classes you're scoring. Two columns are used per class so you'll at least be using columns A & B.

• In cell A1 write Driver
  
• In cell B1 write Points

If you have more than one class, you'll also write Driver in cell C1. Differentiate A1 and C1 by stating which class that column is scoring. Points are points regardless of class, unless you choose to score each class under a different scoring schedule. In that case, I would differentiate the names within the Points columns also. Write every driver's ID tag in rows beneath column A starting with A2.

Below is another freebie line of code, to be entered in rows beneath column B starting with B2:

=SUM('Race List'!G13+'Race List'!K13+'Race List'!O13+'Race List'!S13+'Race List'!W13+'Race List'!AA13+'Race List'!AE13+'Race List'!AI13+'Race List'!AM13+'Race List'!AQ13+'Race List'!AU13+'Race List'!AY13+'Race List'!BC13+'Race List'!BG13+'Race List'!BK13)

↑ Your mileage will vary because the above line of code will be different from your requirements. What we're doing is summing all point totals for every driver from the tab I named Race List. Your tab name might be different and if it is, everywhere you see Race List above, replace it with your tab's name.

The alphanumeric codes (K13 … W13 … BG13 …) might be the same though make sure you align the driver name on this tab, with the driver name on the reference tab. Example: If you have a driver named Chief Wahoo and he's in row twelve on the Race List tab but he's in row six on the Point Totals tab, then make sure you insert the twelfth row totals from Race List into the sixth row of Point Totals.

If you have a manufacturer points battle, skip a line beneath your final racer and write

• Manufacturer in A1 and
  
• Points in B1.

Below Manufacturer, write names of any manufacturers battling for points. In column B we're going to do as we did above; sum the point totals from Race List and output the answer in column B of Point Totals. Due to this, the line of code next to the manufacturer names (column B) will be very similar to the line of code in this section, above. A screen capture is exemplified below. Noteworthy: I color-coordinated my drivers and their scores with the manufacturer for whom they were racing.

Once the tab is finished, points from every event will be totaled in column B (and D if you have them). Here is how you sort by points. Click your title (A1), drag the selection to B1, and all the way down to your last driver's points. In the picture above, to sort A.I. points, I would drag all the way to B14. Once the names and the points are highlighted, click Data, then the first option Sort. In the drop-down menu, sort by Points and select the radio button labeled Descending. Leave alone the two remaining Then by drop-down menus. Below My data range has select the radio button labeled Header row.

Depending upon how your drivers are listed relative to the two sheets, and depending upon the scoring for each event, racers will rearrange in this A column. Their points should follow them, though double check to be certain.

That's all for the scoring aspect. Only two tabs display scoring with a third tab involved for the referenced names (Place, Drivers_Points, and Manufacturers_Points). I have other tabs though they are optional and I'll leave the remaining contents up to you.

If you have any questions or problems getting your particular score sheet to properly function, post a question, comment, or your issue below and we'll work through it.