ReadMe99.txt - Documentation and support for Stats99, Version 4.0 (Stats994.xls). Copyright 1999, Matt Bruce. INTRODUCTION ------------ Thank you for using Stats99. This is a spreadsheet (actually a "Book" of sheets, in Microsoft parlance) written in MS-Excel. I used the version of Excel that comes with Windows95 but the sheet should work with most earlier and later versions of Excel. This project began in fall 1997 and I have made updates and revisions every few months ever since. You may distribute the spreadsheet (and this documentation) freely but may not use it for commercial gain. GETTING STARTED --------------- To use Stats99 you need a working version of Excel. Assuming you have one, you can probably get to it from the Start menu. From Excel, click on the file menu, choose "Open" and then choose Stats99 from a list of files. You may have to change directories. For example, if your copy of Stats99 is on disk and your disk is in the A drive, then you would choose the A drive from the directory menu that comes up after you have chosen to open a file. Once you have opened Stats99, but before you do anything else with it, choose "Save As" from the Excel "File" menu. Save Stats99 under a different file name. You will do this every time you use Stats99, because every tournament (or simulation) will have its own stats. You will want to keep a clean copy of Stats99 for future use. If you plan to print from Stats99, you will want to input the tournament name, host and date. The first sheet you see when you open Stats99 is the "Indiv_Stats" sheet. On this sheet, the headers (name, host and date) are at the cell address AA1 through AA3. Use the right arrow or the mouse button to get to these cells and input the relevant info. Once you have input this info, it will also appear in various places, so that the headers are at the top of nearly any future printout. NOTE: This step is not essential; it is just a convenience. SETTING UP ---------- You will notice that the first four sheets have various columns, each with labels on row 13, but nothing from row 14 onward even though that is exactly where the data will go. This is because the template (original) data lines are all on a separate page, where they are unlikely to be deleted by mistake. Several macros allow you to copy these lines to the relevant pages with just a couple of mouse clicks. Choose "Macro" from the Tools menu. You will see a list of several Stats99 macros. The ones you want right now are all variations on a theme: "Player_Add"; "Team_Add"; "Game_Add"; and "Misc_Add." Click on the "Player_Add" macro and choose Run. The first sheet, Indiv_Stats, should now have data in row 14. If you ran the macro again then it would give you a new player in row 15, and so on. This would be tedious if you had to do it again for every player you add. Fortunately, you can add 4, 16, or 64 players by choosing the appropriate macro. The names of the macros should be self-explanatory. ADDING PLAYER NAMES ------------------- You will now have player names from row 14 down to wherever you stopped adding. Nothing new should have appeared above row 14 (after you enter the name/site/date info, you do NOTHING above row 14), nor should there be any blank lines between row 13 and the end of the players. (Player-addition macros in previous versions ran slowly because they actually inserted rows before pasting the relevant info. The new macros paste at the end, but it's surprisingly hard to figure out where the end is. My solution is not elegant, but it works as long as you don't create blank lines.) Once you have more than enough player lines, you can delete players easily: Use your mouse to highlight the entire row(s) (click on the number of the row) and choose "Delete" from the Edit menu. (Make sure what you're deleting is below row 13.) Now type in the names of the various players on the rows you've just added, one player per row. Each player name will be in column A; use column B for the abbreviation of that player's team. These team abbreviations are IMPORTANT - you may use numbers, letter codes, whatever is easiest for you, so long as you stay consistent. Every player on the same team should have the same abbreviation in column B. ADDING TEAM NAMES ----------------- The various Team_Add macros work the same way that the Player_Add macros work. (NOTE: It doesn't matter where your cursor is (or even what sheet you're on) when you run one of these macros because the macro will go to the right place automatically.) You can add 1, 4, or 16 teams at a time, then delete the rows you don't need. (Again, everything should be below 13 and there should be no blank lines before the end.) The single most important thing you will do on the Team_Stats page is put the right abbreviation (please do NOT use numbers) in column A for each team. This is the same thing you used for all of particular team's players, and the same thing you will use on the game results page. The Stats99 formulae work by pattern recognition in various columns - if you mistype an abbreviation somewhere, the stats will be incorrect. Column B on the Team_Stats page allows you to enter the long names for each team. This is unimportant to how the sheet functions but it is convenient to whoever reads your printouts. Column C allows you to place each team in a Division. Stats99 can process standings for an unlimited number of brackets, simply because the macro that sorts teams by their standing uses this column as its first real criterion. For example, suppose you have two 11-team brackets, Pool A and Pool B. Simply put an "A" or "B" in the Division column, next to each team as appropriate. It doesn't matter in what order you originally input the teams because the standings sort will group the Pool A teams together and then group the Pool B teams together. Reminder: Please DO NOT leave any blank lines between rows. (If you know you won't be adding teams, then blank lines are no big deal. Otherwise, if you try to add data later then that data may get pasted over an already-occupied row.) Of course, if you don't have separate brackets, you can leave the Division column blank with no ill effect. ADDING GAME SCORE LINES ----------------------- Use the various Game_Add macros (you can add 1, 4, 16, 64 or 256 lines at a time) to prepare the Game_Scores sheet. It doesn't matter where you are when you run the macro; you'll end up where you belong. The same warnings apply about blank lines in the middle of the data. Simply put the appropriate team abbreviations (the same ones you've already chosen for the various teams) on the appropriate lines, in the columms labeled "Team 1" and "Team 2." It does NOT matter which of those two columns has the winning team - that means you can input a schedule in advance. What you MUST do: Use the correct abbreviations, and input the correct Round number for each game. (The pattern-matching formulae use the Round number to match the right individual tossup stats to a given game, the better to compute team and Misc. stats. No game will be reflected on the standings page until you've input a score for each team, using the "Score1" and "Score2" columns as appropriate. ENTERING INDIVIDUAL DATA ------------------------ The Indiv_Stats sheet should have various rows with player and team names on them. To input round-by-round individual stats for each player, simply find the row with a particular player's name and the column that corresponds to a given round. (Stats99 is built to handle up to 22 rounds. If you need more than 22 rounds I can custom-design a sheet for you with little effort. The columns that correspond to rounds 10 through 22 are hidden, but you can "Unhide" them (from the Format menu) when you need to.) Stats99 uses one cell per player per round even though each player has three different types of tossup stat. Power tossups (if any) go in the thousands place and negs go in the hundredths place. For example, a player with one power, three regular tossups and two negs will get the number "1,003.02" for that round. Use column A and column B for the player name and team name whenever you add a new player. Use columns C through (however many rounds the tournament goes) to input particular rounds. The rest of the data - totals, PPG, et cetera - will be automatically calculated. There is one exception, for the convenience of tournaments that allow midgame substitution. Suppose Joe comes in for Bob at the half. The formulae that Stats99 uses recognize that someone has played a game whenever the relevant cell isn't blank. (So DON'T FORGET to add the 0 whenever someone plays a game but doesn't buzz.) Therefore the sheet would think that Joe and Bob both played an entire game. The problem with this is that they each missed a half. Therefore, under "Sub Out" (column Z), manually input a ".5" for each of them. (Or, if the sub came during a time out after tossup 15 of 22, you could put "+7/22" and "+15/22" to be fancy.) Time missed is cumulative, of course. If Bob played in only half of three different games - that is, he missed three halves - then you would manually input "1.5" in his "Sub Out" column. ENTERING GAME SCORES -------------------- NOTE: You can put in the team matchups for an entire schedule in advance or you can do just-in-time score recording. Stats99 will work equally well. The Game_Scores sheet has several columns that need data entered but also several columns that get automatic calculation. In general, the data entry columns have a "#" at the beginning of the relevant name. Put the teams, in either order, in columns "#Team 1" and "#Team 2." Put their scores, respectively, in the adjacent "#Score 1" and "Score 2" columns. You may (OPTIONALLY) put info about the Room, Reader and (pack) Author for each game. Do so if you want to keep stats by Room, Reader and/or Author; skip it if you're prefer not to. Either way, PLEASE put the correct Round # for each game. In the columns to the right of the Round #, Stats99 will automatically list the winner and loser in order - or take note of a tie. There are a pair of automatic columns that serve two purposes. If a game lacks a score, then those columns will list the two teams scheduled to face each other. (This preserves accurate standings, rather than recording a bunch of 0-0 "ties" for people who input the schedule in advance.) If a game has a score, then those columns will list the round that the game took place. This allows an automatic calculation of how many rounds' data have been entered. The next two columns contain raw tossup data from each team, calculated by pattern-matching from the team affiliations listed on the Indiv_Stats page. The raw tossup data helps produce accurate team stats and even pack or moderator stats, again by pattern-matching. In any case, you NEED NOT input to these columns - they're automatic. The RPI columns are also automatic. These use pattern matching to look up the winning percentages of both teams. The Team_Stats page will, in turn, use these columns to add up the winning percentages of all of a team's opponents. TEAM STATS ---------- Notice that we didn't say "Entering Team Stats." This is because, if you properly input both the Indiv_Stats (tossup stats by round) and the Game_Scores (don't forget the # of tossups for each game!), then EVERYTHING past the first three columns (which you took care of before the tournament began) is AUTOMATIC. MISC STATS ---------- This is a new feature of Stats99. Use the Misc_Add macros to get the proper formulae, the same way you did for data on the other three pages. Use the same abbreviations on this page that you used on the Game_Scores page. The only unintuitive column on this page is the "Type." If the thing you are evaluating is a "Room," put a 1. If it is a Reader, put a 2; if it is an Author, put a 3; if it is a Round, put a 4. You can actually evaluate whatever three things you'd like to cross-check, apart from the Round #. Simply change the column names on the Game_Scores page as appropriate and enter the data that you want to enter. If you're taking advantage of this feature then it should be self-explanatory to you. (Unlike Round #, Stats99 does not use the Reader, Author, or Room for anything other than its own sake.) Again, everything after the first three columns is an AUTOMATIC calculation. Put your calculator away! SORTING AND PRINTING -------------------- We've already mentioned that all the Individual, Team and Misc. stat calculations follow automatically from the individual tossup data and game score info you enter. The next step is to put your data in a suitable order and, if applicable, print it out. Particular macros for sorting and printing are almost all self-explanatory. Advanced spreadsheet users can also do their own sorts simply by choosing "Sort" from the Data menu after they've highlighted the relevant data. (NOTE: The macros will sort an entire spreadsheet. Since this includes the boilerplate info on and above row 13, we needed a way to make sure those rows stayed intact. Check out column AI (far, far right) of each of the first four pages. Those letters of the alphabet are there as placeholders. It's a kludge but it works. PLEASE DO NOT DELETE THEM.) In any case, choose "Macro" from the Tools menu to get to all the Sorting macros (which all begin "Sort_") and Printing macros (which all begin "Print_ and all begin by running an analogous Sorting macro). For individual stats, "Sort_Players_By_PPG" (or "Print_Individual_Stats") - and then "Sort_Players_By_Team" to return them to an ordering that is convenient for future data entry. On the Team_Stats page you have three options. You can "Sort_Teams_By_Standing" or "Sort_Teams_By_Stats" or "Sort_Teams_By_RPI" - the third choice ranks each team by a combination of record and strength of schedule; it displays the raw number after the team record and tossups/game stat. Each of the three options has its own print command, with an analogous title. You can also print game scores and misc. (Author, Reader, et al) stats. As usual, it does not matter what cell you have highlighted when you run a macro. Each macro automatically goes to where it belongs before it begins its substantive work. FULL LIST OF MACROS ------------------- Stats99 has 28 macros. DON'T let that intimidate you - they are all self-explanatory and most of them are copycats of each other. To run any of these macros, simply choose "Macros" from the Tools menu, click on the one you want, and choose "Run." Game_Add Game_Add_16 Game_Add_256 Game_Add_4 Game_Add_64 The above five macros all add lines to the Game_Scores sheet. These are template lines to start out - that is, they have the right formulas but you add the raw data. You need to use these macros (or copy and paste straight from the Line_Source sheet if you know what you're doing), because if you just tried to type from scratch then the formulas wouldn't work because they wouldn't even be there. Game_Add gives you one line. Game_Add_16 gives you sixteen lines and so on. Misc_Add Misc_Add_16 Misc_Add_4 Player_Add Player_Add16 Player_Add_4 Player_Add_64 Team_Add Team_Add_16 Team_Add_4 The above 10 macros add lines to the Indiv_Stats, Team_Stats or Misc_Stats menu as appropriate. These are template lines at first - you add the raw data and the formulae you've copied will go from there. Print_Games This macro prints a block from the Game_Scores page. Try it! Print_Individual Print_Misc These macros print individual stats, sorted by PPG; and misc stats, in order by category (and alphabetical or chronological order within a category). Print_Team_RPI Print_Team_Standings Print_Team_Stats All three of these macros print team standings or stats. Print_Team_Standings sorts by Winning %; Print_Team_RPI sorts by a formula like the one used at 1999 NAQT ICT; Print_Team_Stats sorts by Points Per Tossup Played & then by bonus conversion. Sort_Games_By_Round Sort_Misc These just put the Game Scores and Misc. Stats in a chronological or alphabetical order as appropriate. Sort_Players_By_PPG Sort_Players_By_Team Sort_Teams_By_RPI Sort_Teams_By_Standing Sort_Teams_By_Stats Other than Sort_Players_By_Team, each of these is analogous to a Print macro above. FULL LIST OF NAMED REFERENCES ----------------------------- The average operator need not worry about any of these. People who want to know how the sheet works (or make their own changes) should be aware of them, however. Blank_Misc Blank_Team These refer to column A of the Misc_Stats and Team_Stats pages, respectively. Their purpose is to help determine the first blank line below the row-13 labels, so that the Add_* macros won't overwrite anything important. Header_Date Header_Host Header_Tourney Each of these is a specific cell on the Indiv_Stats page. Once you put in the relevant info there, it will also pop up in several other places that become the topmost lines of a printout. Line_Game Line_Misc Line_Player Line_Team These refer to entire rows on the Line_Source page. These are the template lines with the right formulae in the right columns for each of the stat pages. New_Game New_Misc New_Player New_Team These are the names of cells that store the address of the first blank line on each page. Or rather, each cell has the address of the first blank cell in column A following row 13. This is why cells A1 through A12 are blank on each of the first four pages. Pct_Team This is the column that contains each team's winning percentage on the Team_Stats page. The formula for winning percentage is calculated the same way that one calculates it in real life. Certain formulae from the Game_Scores page use pattern matching to find the right abbreviation on the Team_Stats page and lift the winning percentage from that line. R_1 R_2 R_3 [etc] R_22 These are references to the columns that contain individual tossup data by round. A certain column on the Game_Scores page uses the Round # of each game to determine the column from which to combine individual tossup stats into team tossup stats. R_Power R_Regular R_Neg R_Player R_Team These are references to more columns on the Indiv_Stats page. They are used to convert individual tossup stats to team stats. Value_Neg Value_Power Value_Regular These are specific cells that contain the point value of a power tossup, regular tossup and neg, for those rare tournaments that have 20-point power tossups or 2-point negs or whatever you need. Z_Author Z_Reader Z_Room Z_Round These are columns on the Game_Scores page. Each of them gets used in a pattern-matching formula to produce the Misc_Stats. Z_T1 Z_T2 Z_S1 Z_S2 More columns on the Game_Scores page. The Team_Stats page uses pattern matching with these columns to get points (and points allowed) for each team. Z_Tossups This is the column on the Game_Scores page that contains tossups read in each game. Obviously any team stat based on number of tossups played will need this data. Z_Winner Z_Loser Z_Draws The information in these columns is derived from the score of each game and in turn goes to the Win, Loss, and Tie columns on Team_Stats. Z_Played Z_Raw Z_W1 Z_W2 More Game_Scores column headers. Z_W1 and Z_W2 columns contain the winning percentages, as looked up from the Team_Stats page.