Author Topic: A (Very) Simple Pace Calculator For Excel  (Read 5972 times)

0 Members and 1 Guest are viewing this topic.

Offline ChrisP

  • Spring Jumper
  • *
  • Posts: 1690
  • I'm going to jump next to your leg.
    • Donkey Blog
A (Very) Simple Pace Calculator For Excel
« on: February 06, 2013, 03:53:39 AM »
This is for all the people who, like me, are terrible at doing math in their heads.

I've been looking for a more simplified pace calculator, as the ones available now are either:

1. Too "high maintenance", requiring constant and very distracting live data entry throughout a game,

2. Too demanding of specific data for a game where you just want a quick but reasonably accurate estimate of pace,

3. Only usable with MAME (Pauline.exe), and of no use to arcade-machine players,

OR

4. Lacking in the ability to quickly measure what a final score might be if the player were to increase or decrease his pace.

I wanted one where I could just plug in a score, the level and screen, and immediately know the pace, without needing a start score or any other numbers.

Since I couldn't find such a thing, I made one myself. :)

This is an Excel (2003) worksheet. I went with Excel because it was the quickest way for me to get to a finished product. I haven't learned how to code such a thing into a webpage.

I hammered this out relatively quickly, so it's "version 1.0". It aint pretty, but it should be self-explanatory: enter your data (your score at the last completed screen, and what that screen was) into the dark gray boxes and a table of projected kill screen scores (listed by score-per-level for the remainder of the game) will appear in the light gray boxes. (It'll also tell you the specific screen # that you're on.)

You can also enter your start score and the sheet will calculate your average-per-level so far and give you your projected kill screen score if you continue at that average. (The "Start" field is optional - enter "0" to blank if out.)

I saved the file with data already plugged into the fields as an example. The data comes from an actual game of DK. If you can identify the game, congratulations, you are a DK supernerd!

Strengths:

1. It's fast and very simple, which was the point. There is very little data to enter, so you can quickly input your numbers during a game. You can also "catch up" at any time - if you miss a screen, or four, or twenty, it doesn't matter. All you need is your score at the last screen completion.

2. It projects your maximum scoring potential if you were to raise, lower, or maintain your current pace. No other calculator that I know of does this, and this is actually pretty important, since, in practice, players will raise or lower their pace during the game for many reasons and need to get an idea of what score they'll end up with if they do so. Other pace calculators assume that you'll keep going with the same averages. This will tell you where you'll end up if you want to shift gears from 53K double-hammer levels to 45K "running board" levels (or the other way around: you've been running boards the whole time and now you want to know how much ground you could make up if you started totally shredding ass).

Weaknesses:

1. Big flaw #1 is that calculations for an as-yet incomplete level are a little rough, because the formula calculates based on the average of ALL post-L4 screens and doesn't differentiate by screen type. Obviously this will throw things off, more or less depending on where you are in the level, as scoring is not uniform by screen type (especially at higher pace where it's weighted heavily toward barrels). But after the sixth screen, when the level ends, the number will be clean and accurate.

2. Big flaw #2 is deaths. The pace calculation doesn't add theoretical points for deaths still to come, and deaths that HAVE occurred will create the opposite problem by artifically inflating the score, and thus, the per-level average. However, it won't throw things off by TOO much, especially if your deaths are low value, and/or happen later in the game.

I thought about working something into the formulas to fix both of these problems, or including cells for deaths, but I'll let it be.

In practice, we all know that pace is in constant flux and is ALWAYS a ballpark figure.

We also know that after we get killed we're going to be too busy screaming and kicking the machine to write down how much we got for the death that just happened.

The point here isn't to be needle accurate, the point is to have something we can punch some simple numbers into every now and then during a game and get a rough estimate of where we are.
« Last Edit: February 06, 2013, 04:42:29 AM by ChrisP »
http://donkeykongblog.blogspot.com

4 Quarters :-* - 800K Avg. Per Qtr. :o - No Restarts 8) - No Proof :'(

7/26/2013   Coin 35,946   710,800   18-1
7/28/2013   Coin 35,947   903,700   22-1
8/16/2013   Coin 35,948   694,100   17-6
8/17/2013   Coin 35,949   893,100   22-1

3,201,700: the $1 World Record?
Twitch TV  Streamer DK Killscreener Crazy Kong Killscreener DKF Blogger DK Jr. Killscreener

Offline stella_blue

  • High Score Moderator
  • Spring Jumper
  • *
  • Posts: 1502
Re: A (Very) Simple Pace Calculator For Excel
« Reply #1 on: February 06, 2013, 08:29:07 AM »
Nice job, Chris.  Low maintenance, with the added benefit of multiple "future pace" scenarios.

When I looked over your formulas, I immediately recognized the 2 flaws you've already identified.

A brief thought on each:

1)  Sure, if you enter your score after Level 5-3, you'll have an inflated pace value with 2 barrel screens included and 2 typically lower scoring stages (elevators and rivets) completely absent.  However, it's far more likely that a player seeking a low maintenance approach will wait until the rivet stage has been completed before entering their data, when they have a few extra seconds to do so.  I don't see this "flaw" as a big deal at all.

2)  As you correctly point out, unless you suffer several high scoring deaths early on, the overall effect shouldn't be too major.  The way I see it, if I'm down to my last man on L5 (whether "good deaths" or not), I'll probably re-start.  If I decide to play on, I would simply disregard the pace calculation and focus my attention on the future "what if" scoring scenarios to the right.  Again, no big deal.

If forced to suggest one change, it would be a calculation to "back into" a start value if one is not entered.  As it currently stands, the overall pace is zero if the player neglects to enter their start.  The formula I envision, without being overly complex, would be a combination of lookup functions that use the current score (and screen #) in conjunction with a 2-dimensional table (static and pre-loaded) that assumes a consistent scoring progression.  It's far from ideal, but better than nothing.

I'll have to give this some more thought.

« Last Edit: February 06, 2013, 08:42:25 AM by stella_blue »
DK High Score 
952,100
  (L22-1)
DK Level 1-1 
13,400
DK No Hammer 
535,300
  (L15-2)
Twitch TV  Streamer Most Time Online DKF Blogger DK Killscreener

Offline Jeffw

  • Full Member
  • *
  • Posts: 72
Re: A (Very) Simple Pace Calculator For Excel
« Reply #2 on: February 06, 2013, 11:05:27 AM »
Nice job Chris. Coincidentally I've been working on adding similar functionality to Pauline for pace calculation. I find there are several important flaws with the basic pace equation:

pace = start + 17*level_average

that the current pace algorithm is based off of.

1. It doesn't give a pace before L6

2. Pace is subject to huge fluctuations and major inaccuracies in levels immediately following L5. Pace from this equation almost has no meaning until at least L8 or so. If you get an extremely lucky massive scoring L5 score then the pace will be huge on 6-1 (I've had it read like 1.29m before) which is clearly very inflated and similarly terrible luck on L5 will result in a very deflated pace. You can't eliminate major fluctuations or early inaccuracies like this but ideally I'd want them to happen on the very first screens of the game and have the pace be a little more stable once you reach L6 (but not too stable since it's still quite early).

3. It can give different paces for two different games that have the same score on the same level if the games had different starts. Pace is like a current status and should not be based on past events. If two games have the same score on the same level then it shouldn't really matter how they got there, they should be considered to have the same pace.

I've been working on an alternate pace algorithm that solves all these problems. The algorithm would only use the current score, level and spare men and would output a pace. It would have to be a lot more sophisticated than your spread sheet equations because of the flaws you mentioned. I also realize that once I finish writing this algorithm it could potentially be ported to a web app. Unfortunately the best solution I have at the moment requires a fair amount of work.

Basically, what I was planning on doing is writing an algorithm that's based around the concept of pace charts (a pace chart would be considered a score on each screen of the game, including pre-L5 and screens between levels, that leads to a final score equal to the given pace). Note that a score for each screen before L5, an L5+ barrel score, an L5+ pie factory score, an L5+ spring score, and an L5+ rivet score fully specify an entire pace char.

The first step would be to write the capability of generating a pace chart for any arbitrary pace. This requires writing a set of about 5 or so pre-programmed pace charts ranging in pace from 850k to 1.2m (this along is quite a lot of work and requires coming up with barrel, pie, spring, and rivet averages plus scores for each pre-L5 screen for each pace). Then I can compute an arbitrary pace chart by interpolating between them.

The next step is to write the actual pace algorithm which will just generate a bunch of charts for various paces until it finds a chart for which the the current score/screen of the game matches the score/screen of the pace chart and then output that pace.

I think this will solve the issues I described and the issues with your excel worksheet. Writing this kind of thing is probably not necessary for the intended purposes of the spreadsheet, but I still thought it was worth describing this since it's currently my best solution to all pace calculating issues.

I'm currently still trying to write the pre-programmed pace charts and making them as representative of their pace as possible.
« Last Edit: February 06, 2013, 11:13:38 AM by Jeffw »
Twitch TV  Streamer DK Killscreener Kong-Off 2 Champion DK 1 Million Point Scorer DK 1.1 Million Point Scorer Kong-Off 3 Champion

Offline stella_blue

  • High Score Moderator
  • Spring Jumper
  • *
  • Posts: 1502
Re: A (Very) Simple Pace Calculator For Excel
« Reply #3 on: February 06, 2013, 11:36:48 AM »

I'm not so sure I agree with flaw #3, Jeff.  Looking at a simple example:

1)  Player 1 decides to race to Level 5 and ends up with a start of 100k.  From there he switches to a 2-hammer game on the barrel boards, and averages 54k on Levels 5 through 9.

2)  Player 2 decides to point press the first 4 Levels and secures a 130k start.  From there he changes to a top hammer only approach on the barrel stages, running boards and averaging 48k on Levels 5 through 9.

Both players begin L10 with a score at or near 370k, albeit by different means.

Would you consider the pace of those 2 games identical?  Personally, I wouldn't.

DK High Score 
952,100
  (L22-1)
DK Level 1-1 
13,400
DK No Hammer 
535,300
  (L15-2)
Twitch TV  Streamer Most Time Online DKF Blogger DK Killscreener

Offline marinomitch13

  • Moderator
  • Spring Jumper
  • *
  • Posts: 1792
    • How to Play DK
Re: A (Very) Simple Pace Calculator For Excel
« Reply #4 on: February 06, 2013, 11:43:38 AM »
Wow, Jeff, this is exactly the thing that my friend Alex and I were gonna do with our pace program. We were planning on adding optional features that the player could check off such as 'only grabbing top hammer', or 'only using the weave' etc to help the algorithm become more accurate faster (we've got so many little ideas it's hard to even remember them all, but we have a list typed up). Ultimately, we were gonna both 1) create a database that would track a particular players games that would find the coefficient off of the average score per screen of each of their specific stage averages, as well as 2) just put in some coefficients up-front that we've already researched and found to be typical of certain playing styles. We were gonna incorporate these coefficients to much the same end as you've described.

Lastly, we were gonna try to make a sort of game-database-sharing community that could be tracked/used/watched/organized by the members. This is a while down the road yet (maybe have it done after this summer if all goes as planned), but it seems like several other people are also already working on the same sorts of things.

I have a feeling having this forum to really connect all of the DK brainpower and effort is gonna help create some exciting things soon.
“Thou hast made us for Thyself, and our heart is restless until it finds its rest in Thee.” -Augustine, Confessions.
Twitch TV  Streamer DKF Blogger DK Killscreener

Offline Jeffw

  • Full Member
  • *
  • Posts: 72
Re: A (Very) Simple Pace Calculator For Excel
« Reply #5 on: February 06, 2013, 12:51:03 PM »

I'm not so sure I agree with flaw #3, Jeff.  Looking at a simple example:

1)  Player 1 decides to race to Level 5 and ends up with a start of 100k.  From there he switches to a 2-hammer game on the barrel boards, and averages 54k on Levels 5 through 9.

2)  Player 2 decides to point press the first 4 Levels and secures a 130k start.  From there he changes to a top hammer only approach on the barrel stages, running boards and averaging 48k on Levels 5 through 9.

Both players begin L10 with a score at or near 370k, albeit by different means.

Would you consider the pace of those 2 games identical?  Personally, I wouldn't.

Yes, due to this kind of reasoning I'm going to leave support for the old algorithm (you will be able to choose which pace algorithm to use with a configuration option). However, I would still consider giving the two players the same overall pace to be most correct for the situation that you described.

Consider another situation in which two players have the same start. Player A plays 5 levels at 60k/level and then decides to slow down and plays the next 5 and 50k/level. Player B plays 10 levels at 55k/level. They have the same score on the same level but should their paces be considered different? The simple pace equation would give them the same pace so is that a flaw? I would say that they should have the same pace for the reason that pace should not consider recent changes in playing style that may lead to a different final score, it should be a projection of what the final score will be given overall playing of the entire game up until that point.

I've thought about the possibility of including something like a "recent pace" which just considers the past 5 levels or something and uses those to predict a final score, but for overall pace I would say that it doesn't make sense to draw a line anywhere in the game and only consider scores beyond that line in the pace projection. There shouldn't be a line drawn at the end of L4 just there shouldn't be one after L5 or after L6 or after the past 5 levels, you can have something like a "recent pace" if you want to capture recent changes in playing style.
« Last Edit: October 05, 2013, 05:37:21 PM by Jeffw »
Twitch TV  Streamer DK Killscreener Kong-Off 2 Champion DK 1 Million Point Scorer DK 1.1 Million Point Scorer Kong-Off 3 Champion

Offline hchien

  • Spring Jumper
  • *
  • Posts: 540
Re: A (Very) Simple Pace Calculator For Excel
« Reply #6 on: February 06, 2013, 01:43:05 PM »
Why not use the "least squares regression line" to project pace?  In layman's terms this is the "best fit" line.  This would address many of the above issues:

- aggressive start/conservative L5+ vs conservative start/aggressive L5+: the "best fit" line would just have a higher starting point and lower slope in the first case and vice versa

- fatigue/adjustment in gameplay: the "best fit" line would (to some extent) compensate for slower pace at the end.  To better adjust for this you could do regression analysis on the level scores, use the regression line to project future level scores and sum them up for the pace.

The formula for this can easily be found on the web.  If anyone is interested in implementing this, I can help with statistics/math.
World Record for most insults to Vincent and lowest 1 hour Q*bert score: 395985
Twitch TV  Streamer DK Killscreener DK 1 Million Point Scorer DK 1.1 Million Point Scorer DK Jr. Killscreener Kong-Off 1 Champion KO4 Champion

Offline Fast Eddie

  • Elite Member
  • *
  • Posts: 358
Re: A (Very) Simple Pace Calculator For Excel
« Reply #7 on: February 06, 2013, 03:03:24 PM »
during a real time game you just want something to do some quick math for you from time to time. i think its more useful to know "levels remaining*how you plan to play" rather than what your overall pace is/was...

a calculator that simply took your current score/level and worked out k/s scores for a range of future level averages like 46k 52.5k 55k 57.5k 60k 62.5 would give you all the information you need. there is so much randomness in dk it is pointless trying to be too accurate...

i think this is the simplest way to get the information you need and i think it should also be a more accurate way to predict your k/s as its not warped by how lucky/aggressive you have been up to that point in the game the way a pace projection is...

8)
Twitch TV  Streamer DK Killscreener March Madness No-Hammer Champ DK 1 Million Point Scorer No-Hammer Killscreener 2014 No-Hammer Tourney Champion

Offline Fast Eddie

  • Elite Member
  • *
  • Posts: 358
Re: A (Very) Simple Pace Calculator For Excel
« Reply #8 on: February 06, 2013, 03:16:39 PM »
so ill get excel and check out your spreadsheet Chris, sounds perfect for me...
Twitch TV  Streamer DK Killscreener March Madness No-Hammer Champ DK 1 Million Point Scorer No-Hammer Killscreener 2014 No-Hammer Tourney Champion

Offline ChrisP

  • Spring Jumper
  • *
  • Posts: 1690
  • I'm going to jump next to your leg.
    • Donkey Blog
Re: A (Very) Simple Pace Calculator For Excel
« Reply #9 on: February 06, 2013, 03:54:25 PM »
Well now I'm intimidated because the actual math guys are here looking at my crude gorilla math!
http://donkeykongblog.blogspot.com

4 Quarters :-* - 800K Avg. Per Qtr. :o - No Restarts 8) - No Proof :'(

7/26/2013   Coin 35,946   710,800   18-1
7/28/2013   Coin 35,947   903,700   22-1
8/16/2013   Coin 35,948   694,100   17-6
8/17/2013   Coin 35,949   893,100   22-1

3,201,700: the $1 World Record?
Twitch TV  Streamer DK Killscreener Crazy Kong Killscreener DKF Blogger DK Jr. Killscreener