Finding the formula for a game experience

Question

I am playing a game and I am building an excel sheet that has a bunch of reference information in it. Instead of recording associated ~400 potential results, I wanted to try and come up with the formula the game was using to determine Experience Points.

The way the game is set up, there are 45 different group levels, each level has a set number of squads to it, and each squad can be common, rare, or epic. To keep things simple I decided to only look at the first 10 group levels and to only look at common squads in order to reduce variables. Also at higher Group levels or for rare and epic squads, the squad can consist of multiple unit types. However in the first 10 level of common, the squads are only made up of a single unique unit.

The information I gathered from the game is in the following table:

+───────+────────+───────+────────+──────+──────+───────+────────+────────+─────────────+
| Unit  | Group  | Unit                                 | Total           |             |
| Name  | Level  | Level | Count  | ATT  | DEF  | Lead  | ATT    | DEF    | Experience  |
+───────+────────+───────+────────+──────+──────+───────+────────+────────+─────────────+
| A     | 1      | 0     | 93     | 28   | 84   | 1     | 2604   | 7812   | 200         |
| B     | 2      | 0     | 140    | 28   | 84   | 1     | 3920   | 11760  | 340         |
| C     | 3      | 0     | 110    | 56   | 168  | 2     | 6160   | 18480  | 540         |
| D     | 4      | 0     | 330    | 28   | 84   | 1     | 9240   | 27720  | 880         |
| E     | 5      | 0     | 500    | 28   | 84   | 1     | 14000  | 42000  | 1440        |
| F     | 6      | 1     | 210    | 100  | 300  | 2     | 21000  | 63000  | 2400        |
| G     | 7      | 1     | 330    | 100  | 300  | 2     | 33000  | 99000  | 3800        |
| H     | 8      | 1     | 330    | 150  | 450  | 3     | 49500  | 148500 | 6200        |
| J     | 9      | 1     | 510    | 150  | 450  | 3     | 76500  | 229500 | 10000       |
| K     | 10     | 1     | 2300   | 50   | 150  | 1     | 115000 | 345000 | 16200       |
+───────+────────+───────+────────+──────+──────+───────+────────+────────+─────────────+

What I tried was to toss the data into an XY scatter chart and then add a trend line and grab the formula as a starting point. The trend line that seemed to fit really well was an exponential line with the formula

Y=125.94e^(0.4868*x)
 Where Y = experience
       X = Group Level

The formula had an R^2 value of 0.9999, so pretty damned close. I tried the formula and it was not giving exact results which I expected due to the decimal places in the formula. So I adjusted the formula a little by tossing it into an MROUND (rounding to a multiple) and that worked really well for the most part, but rounded the wrong way a couple of times and the larger the number the multiple would need to be adjusted. So this is one of the reasons I did not think this formula was correct, but probably on the right path.

Another reason I did not like this formula, was an assumption on my part. I would assume the formula would use some or all of the other numeric columns in the table to come up with the amount of experience (XP).

I suspect the the experience is indeed based on unit count and unit parameters somehow as when I compared higher Group level experience, different squad of the same group level had different XP. The main variation between the squads were unit name, and unit count (number of units of that specific unit in the squad).

One of the things I noted, but may be a complete red herring, is:

The ratio of ATT/DEF = 1/3

I was reading on another website how RPG character level requirement tables were created and they gave a bunch of examples for different games. Based on what I saw there, I would not be surprised if there is a rounding (round down/up, floor/ceiling) type function involved.

My goal here is to:

1. Find a formula that works for the provided table
2. Learn the process for arriving at this formula
3. Apply what I learn to expand the formula to work on my other cases where there are a couple of more variables involved

Research/related Qs

• This question was similar but much more basic and I can easily see the pattern in it.
• This site is where I read about XP level tables

Other

I am totally lost as to what tags should apply to this question.

UPDATE 22/09/04

In a further effort to reduce the variables, I started doing some attacks against a level 1 squad. Kill 1 see how much xp, Kill 2 see how much XP etc. It got a little difficult to control at one point due to some randomness with the combat system and I wound up killing way more than 3, and accidently wiped out the squad as it was slowly being reduced each attempt. So then I went after another level 1 squad to carry on filling out the data points. And then I stopped when I saw that for the same number of kills (2) in the different squads I attacked, I got 4 xp for one and 5 xp for the other. Too me that just was not right/inconsistent. So I had to take a breather before going back to that experiment. In my head the XP should be based on the number of units in the squad destroyed. And when I added up all the individual attacks until the squad was destroy, they totaled up to the 200 for Squad.

FYI: Squad = Group, Units make up the group/squad

+──────+────────+───────+─────────+──────+──────+────────+──────+──────+─────+
    | Unit | Group  | Unit  |         |      |      | Total  |      |      |     |
    | Name | Level  | Level | Killed  | ATT  | DEF  | Lead   | ATT  | DEF  | XP  |
    +──────+────────+───────+─────────+──────+──────+────────+──────+──────+─────+
    | 1st Attack                                                                 |
    +──────+────────+───────+─────────+──────+──────+────────+──────+──────+─────+
    | A    | 1      | 0     | 1       | 28   | 84   | 1      | 28   | 84   | 2   |
    | A    | 1      | 0     | 2       | 28   | 84   | 1      | 56   | 168  | 4   |
    | A    | 1      | 0     | 8       | 28   | 84   | 1      | 224  | 672  | 17  |
    | A    | 1      | 0     | 15      | 28   | 84   | 1      | 420  | 1260 | 32  |
    | A    | 1      | 0     | 19      | 28   | 84   | 1      | 532  | 1596 | 41  |
    | A    | 1      | 0     | 23      | 28   | 84   | 1      | 644  | 1932 | 50  |
    | A    | 1      | 0     | 25      | 28   | 84   | 1      | 700  | 2100 | 54  |
    +──────+────────+───────+─────────+──────+──────+────────+──────+──────+─────+
    | 2nd Attack                                                                 |
    +──────+────────+───────+─────────+──────+──────+────────+──────+──────+─────+
    | A    | 1      | 0     | 6       | 28   | 84   | 1      | 168  | 504  | 12  |
    | A    | 1      | 0     | 2       | 28   | 84   | 1      | 56   | 168  | 5   |
    +──────+────────+───────+─────────+──────+──────+────────+──────+──────+─────+

Answer 1

Let $f(1)=200, f(2)=340$

I can see almost $f(n+2)=f(n+1)+f(n)$

It works except $n=3,4,5$

If so, the solution is in the form $f(n)=c(\alpha^n+\beta^n) \quad where \quad \alpha,\beta$ are roots of $x^2-x-1=0$

But the exception rejects this simple formula. Need to use piecewise function or else to handle.

Answer 2

If you round $10000 \cdot \phi^{n-9}$, where $\phi = \frac{1+\sqrt5}{2}$ is the golden ratio, to the nearest multiple of $100$, then you get the sequence $$200, 300, 600, 900, 1500, 2400, 3800, 6200, 10000, 16200$$ for inputs $n=1, 2, 3, 4, 5, 6, 7, 8, 9, 10$. This gives the wrong answer for $n=2, 3, 4, 5$ but I still think it's much more likely that the formula above is correct and those values were manually adjusted later. (Maybe for balance reasons? It is a game, after all, not just a math problem.)

It would be interesting to look at the other levels to see if the formula continues to hold up.

Answer 3

As noted by @AbelWong, the experience column looks like a Fibonacci sequence. This sequence is well known in the developer world:

• There is a data structure called the Fibonacci heap. Other examples include Fibonacci trees, Fibonacci search, Fibonacci coding, etc.
• When presenting a programming language, one of the very first examples given is usually a recursive implementation of Fibonacci sequence. It serves also to illustrate the concept of a function (the other frequent example being the factorial).
• This recursive implementation is often used to measure a programming language speed - despite being a very bad choice for that.
• In the Scrum agile development method, which is very much used by development teams, the estimation of feature complexity is done with a so called "planning poker" between members of the team. This planning poker uses cards with increasing values. The most frequent sequence I have seen is $1, 2, 3, 5, 8, 13, 20, 40, 100$, a Fibonacci sequence where the highest numbers get rounded. This sequence can be seen in the photo illustrating Wikipedia's page about planning poker.
• The (supposed) aesthetic properties of the golden ratio, and that consecutive terms of the Fibonacci sequence approximate that ratio, are part of developers folklore.

So it is plausible that the developer in charge of the experience points just chose a Fibonacci sequence and rounded it so as to reach $10000$.