Menu ▾ ▴

Home

Alan Chalker

Table of Contents

This main page has the following sections:

  • Overview
  • Common Problems
  • Instructions
  • Example Customizations

Overview

This is an Excel based scoring system for use at Science Olympiad tournaments. It provides a number features as listed below. Earlier versions have been in use since 2002 at tournaments all over the country. For 2012 significant improvements and features have been added, along with more robust error checking and handling. It is free for non-commercial use and modification. Please submit any bug reports or feature requests using the ticket system linked to above or by emailing the author at the address listed on the Startup page in the program.

Features include:

  • Self contained in 1 Excel workbook
  • Individual scoring sheets for each event
  • A master scoring report
  • Automatic error checking, including tie detection and highlighting
  • Ability to import data from spreadsheet provided by event supervisors
  • Ability to generate reports ordered either by team number or rank for individual events and overall results
  • Ability to output medal results for the awards ceremony as both a script for the presenter and a PPT presentation to display
  • Customizable for tournament specific information such as team names, trial / pilot events
  • Ability to dynamically 'remove' individual teams and events from scoring calculations
  • Printable version of blank score sheets available for supervisors who want to use a paper copy

Common Problems

  • Difficulty saving: When downloading the files, often you will get a choice to either 'Open With Excel' or 'Save to Disk'. If you open the files directly in Excel from your web browser, it will usually open them in Read Only mode. This will be indicated in the title bar at the top of the Excel window, where the file name will be followed by (Read Only). To correct this, use the "Save As" option in Excel to first save a copy to your local hard drive somewhere. To prevent it from happening in the first place, always choose 'Save to Disk' when downloading a file and then double click on the saved file to open it in Excel.
  • Enabling macros: To fully utilize the features of the program, macros must be enabled in Excel. Here are directions for doing that: http://www.mdmproofing.com/iym/macros/index.html NOTE: MS Excel 2008 for Mac does NOT support macros of any type, thus much of the functionality of this system will not be available. Virtually any other version of MS Office for Windows or Mac will work though. In Excel 2002 and newer, you need to explicitly allow a macro to add code to a workbook. Go to Tools, Macro, Security. Click on the 2nd tab: Trusted Publishers. In the lower left, choose Trust Access to Visual Basic Project. In Excel 2007, Go to Office Icon, Excel Options, Trust Center, Trust Center Settings, Macro Settings, and choose Trust access to the VBA project object model.
  • Compile error: When running the create stand alone score sheet or create power point macro, you might get an error window that pops up that says "Compile Error; Can't find project or library". This is due to a corrupted reference in your MS Office installation. To resolve it:
    • Start from any tab in the spreadsheet and press ‘Alt’ and ‘F11’ keys at the same time. The Visual Basic for Applications window should open up
    • On the left side, scroll down and double click on the ‘Modules’ folder to expand it, then double click on the Module1 to open it up on the right side.
    • Go up to the Tools menu and click on References.
    • Look at the items that have checks next to them and one (or more) should say something like “Missing:”. Uncheck that one (or more).
    • Click on Ok, then Save and close the VBA window.

Instructions

The sections below contain copies of the current instructions from each page of the program

General Instructions

  1. On any sheet in this workbook, items you can click on or change the value of are colored YELLOW. All other items are 'locked out'.
  2. All sheets have brief instructions at the top of them explaining what to do on the sheet.
  3. This sheet is the master table of contents. You can click on any of the links to the left to switch to the corresponding sheet. All sheets have a yellow box in the upper left corner labeled "Click to return to the start page" that brings you back here.
  4. There are 5 other 'special' pages:
    • Master Score Sheet: The grand overview of all events and scores
    • Printable Medals List: Prints out the x top places in each event for use at the awards ceremony
    • Setup: Sets up the titles, team names, and event names
    • Printable Blank Score Sheet: Print out to give hard copies to Event Supervisors if needed
    • Blank Score Sheet: Copy into a new workbook and give to Event Supervisors if needed
  5. The remaining sheets are for each individual event and should be filled out with the scores from that event.
  6. All place and rank calculations are automatic. You only need to enter scores (and tie breakers / tiers if appropriate).
  7. There is built in error checking for things like ties and blank scores. Big red boxes appears when there is an error telling you what it is and where it's located.
  8. You must have Macros enabled in Excel, or Excel will give you an error.

Setup Sheet

  1. Fill in only the information in the YELLOW cells. You may leave cells blank.
  2. Change # of Teams button: only to change the number of team. You can change the state / name / status (e.g. Competed, No Show, DQ'd) directly below anytime.
  3. Change Event Names button: only if necessary, as will delete all event data. You can change the names / type (e.g. Normal, Trial/Pilot, Not Run) at the right anytime.
  4. Create Standalone Blank Score Sheet button: allows you to save a single generic score sheet for use by event supervisors.

Printable Medals List

  1. Change the number of medals button: Updates both event and team medals that are visible
  2. Print this sheet button: sends results to the default printer.
  3. Create a PPT presentation button: copies all results into a PowerPoint presentation.

Event Score Sheets

  1. Only fill in the YELLOW cells. Everything else is automatic.
  2. Special codes: ""p"" for participation, ""ns"" for no show, ""dq"" for dq'd (without the quotes) allowed in any column.
  3. Raw Score column: A red warning box indicates if you have blank scores and haven't put in a special code from above.
  4. (Optional) Tier column: Tier 1 ranks higher than Tier 2, etc.
  5. Tie Break column: A red warning box indicates if you have ties and the tied rows are highlighted red. To resolve, fill in those rows with the value from the tie break question.
  6. Sort Order selectors: use drop down list to set high vs. low wins for raw scores and tie breaks.
  7. (Optional) Import Data button: use to copy raw scores / tiers / tie breaks from another Excel file
  8. (Optional) Create Rank sorted button: creates a standalone version of this sheet sorted by rank instead of team number

Example Customizations

Some tournaments run the scoring process slightly different. Here are some example customizations that can be made to the system to adapt it.

  • Throw out the worst X scores: When team totals are tallied, some tournaments throw out each team's 1 (or 2, 3, etc) worst event scores before ranking all the teams. A simple modification can be made to the Master score sheet to handle this, as follows:
    1. Setup the system with all your events and number of teams.
    2. Switch to the Master sheet and Unprotect it (option is under the Review menu)
    3. Click on the first teams Total Points cell (should be AK7)
    4. The default formula should be: =IF((SUM(D7:AI7))=0,"",SUMIF(D$83:AI$83,1,D7:AI7)+AJ7) add the following between the AJ7 an the ): -LARGE(D7:AI7,1) so that the resulting formula is: =IF((SUM(D7:AI7))=0,"",SUMIF(D$83:AI$83,1,D7:AI7)+AJ7-LARGE(D7:AI7,1)) This will drop the worst score, add additional terms for each additional score you want to drop (i.e. -LARGE(D7:AI7,2) for the 2nd worst, -LARGE(D7:AI7,3) for the 3rd worst, etc.
    5. Copy the new cell, and then drag and select the rest of the cells below it (all the rest of the team totals).
    6. Use the Paste Special function and select Formulas to copy the updated formula to the rest of the cells.
  • Use non-sequential team numbers: Version 2 of the system now allows arbitrary event numbers
  • Run a total of Y events, but teams compete in only X events: To do this, setup all Y events as normal type events. Then for each event a team does NOT compete in / want counted towards their team total, input a score of 'ns' (without the quotes) on the event page. All of the 'ns' results from all the teams will cancel each other out with regards to the final rankings, although the total team points will be inflated for everyone.