A special way to enter data

  • Posted on: 21 December 2007
  • By: sean

Dear Sean,
I am tryin got create a database using the same scoring used during tournaments. I can't seem to create the database and was wondering if there was a special way to enter the data into microsoft excel 2003. - Maurice, 12/17/07

Hi Maurice. We actually use a Filemaker database to keep track of our membership, payments, dates, and scores all together. I don't know what kind of programming head you have but this is a little overview of how we generate scores and averages in database language.

Firstly, we need to track 4 numbers for 9 games for each person. These numbers are the number of entrances (ENT) and serves (SRV) and dividing the ENT by the SRV comes the average (AVG) and we also create an adjusted average (ADJ). Then at the end of the season we also get a grand average and grand adjusted average.

Basically a season statistics set looks like this for nine rows per player:

ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG

Then we take the grand average and divide by the number of games which is:

Total(AVG) / 9

This is also coincidentally the same as Total(ENT) / Total(SRV), but the part that is important to our system is the adjusted and grand adjusted averages. These numbers are the same as the normal averages except we are dropping the lowest two scores off the players roster, effectively make it a 7 game grand average except it will be a different set of numbers dropped out per player.

I don't know how you would do this in Excel, but you need a system of dropping the lowest score per person. In Filemaker we just made a field that stands as an "omit" option in which the score is not included in the count. When I am done I get a formula that looks like this bugger to get the grand adjusted average:

ENT / SRV = AVG
ENT / SRV = AVG
omit (ENT / SRV = AVG)
ENT / SRV = AVG
omit (ENT / SRV = AVG)
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG
ENT / SRV = AVG

Total(AVG) / 7 = ADJ

Then finally we have a total number that reflects the players best seven game average, and by this point we are talking members who have played something like 300 games of four square each.

Hope this can shed a little light on your project, let me know how it all shakes down.

Comments

Jason McG's picture

Hey Sean,

I'd been meaning to ask you about the final scoring system for some time now. Now that I've read it, I'm a bit worried since it could be exploited. It might be better to drop the lowest two scoring games, and then take the average of the sum of all entrances and the sum of all serves for the other games.

This does have the side effect of removing the "truancy" penalty (which I would likely benefit me).

Fortunately we haven't had a problem with anyone gaming results like that. But I think it would provide a more useful indicator to players if they knew how many times they entered and how many times they served each game.

Best,
Jason M.

sean's picture

This, from a PhD candidate in mathematics. Let me runs some numbers and get back to you.