Building a formula to for incremental data (Not sure where this goes) gaming formula

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I was wondering if someone could explain to me how to build a formula to find a out any point in set of data that has a fixed growth pattern.

I did the math in excel so i kinda know the theory behind it i'm just not sure how to turn that into an actual functioning formula.

A small extract from the data.
this is to work out how how much experience is needed at a particular level to reach the next level

Level

needed exp for current level

exp difference between the levels

growth rate between the difference between levels

1

14

-

-

2

36

22

8

3

66

30

8

4

104

38

8

5

150

46

8

44

8184

358

8

So i know that somehow i need to use the starting point of 14 for the first level and then the growth rate of 8 at the level i'm trying to calculate. that's about as far as i have gotten.

We've had a lot of people lately asking to reverse engineer a formula for gaming!

In general, you can only guess what kind of formula might have been used to make a given table. But in this case, you've found that the "second difference" is constant, which implies that the data are generated by a quadratic function.

I could show you how to find that function; but since you're already in Excel, you can just make a scatter chart of x and y and tell it to make a trendline (polynomial, order 2), and display the equation. The answer is a very simple formula!

I know that the series 2 formula is ment to be y = 8x + 6 (tested and its perfect)
Series 3 is ment to just be Y = 8 (because we know that the 8 is constant)

Im wondering if the wierd formulas is because Number 1 on my table has 2 empty cells?

Any further advice?

Hi DragonPrinces, I think you're on the right track.

Just a note, those words are spelled "weird", and "meant".

Anyway, you are right that the first column (ignoring the level numbers) is the experience needed at each level, the second column is the first difference of the experience between levels, and the third column is the second difference, i.e. the difference of the differences.

You only need to fit a quadratic trendline to the first column (series 1) because that's the trend you're trying to figure out: how does the experience needed vary with level? You don't need to compute trendlines for the second and third columns, because those aren't the trends you're trying to figure out.

You should think critically about the numbers you're getting:

4x2 + 10x + 6e-11

Well, the constant term is 6e-11, which is a computer's way of writing 6 x 10-11. That is 0.00000000006, which is so small, it's effectively zero. It's just a numerical artifact due to the slight imprecision of the numbers as they are represented in computer memory. The program tried to fit a full second-order polynomial with quadratic, linear, and constant terms, but the data have no constant term, so the fitter found a really small value for it (small enough to be ignored completely). The answer you got is effectively just 4x2 + 10x

The same thing happened to the second column. You tried to fit a quadratic to data that is just linear (a straight line). As a result, the fitter found an extremely small coefficient for the second-order (x2) term. 4e-16 = 0.0000000000000004, which is effectively 0. It's saying there is no second-order term, and the answer is effectively just 8x+6

The same thing happened with the third column. You tried to fit a quadratic curve to data that are constant (straight, flat line). So the coefficients on the second-order (x2) and first-order (linear, x) term came out to be incredibly tiny. It's saying that these terms basically don't exist, and the answer is just y = 8.

Yup, you have a difference equation, so that works if you know how much experience was needed in the previous level. The quadratic equation works if all you know is the current level, and you want to know how much experience is needed.

So if we're trying to figure out how much experience is needed for level 10, we you can apply your equation 10 times, or we can apply my equation once.

Just a note, those words are spelled "weird", and "meant".
Sorry about the spelling.

Well, the constant term is 6e-11, which is a computer's way of writing 6 x 10-11. That is 0.00000000006, which is so small, it's effectively zero. It's just a numerical artifact due to the slight imprecision of the numbers as they are represented in computer memory. The program tried to fit a full second-order polynomial with quadratic, linear, and constant terms, but the data have no constant term, so the fitter found a really small value for it (small enough to be ignored completely). The answer you got is effectively just 4x2 + 10x

y = 4x2 + 10x

That makes perfect sense and i should have noticed it since i often see scientific notation. The computer has the little bits extra and doesn't know its to small to matter so just puts it in.

Thanks everyone that contributed. I will definitely be able to use this in the future.