Results 1 to 7 of 7

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

  1. #1

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

    Hey guys. this is my first post.

    I hope i'm describing this right
    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.

  2. #2
    Full Member
    Join Date
    Nov 2017
    Location
    Rochester, NY
    Posts
    599
    Quote Originally Posted by DragonPrinces View Post
    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!

    Let me know what you get.

  3. #3
    Thanks for the Advice on the scatter chart. now that i have these i'm still abit confused.

    So assuming total need exp is series 1 exp between levels is series 2 and then the second difference is series 3

    the formulas i got from the scatter graph are wierd and they change abit wierd aswell. let me try and explain.

    The series 1 formula is as follows
    y= 4x2 + 10x + 6E-11
    The series 2 formula is
    y= 4E-16x2 + 8x + 6
    The series 3 formula is
    y= 1E-17x2 - 2E-15x + 8

    Now thats if i have the scatter graph for all the data from #1 - 100

    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?


  4. #4
    Junior Member
    Join Date
    Jan 2018
    Location
    Toronto
    Posts
    65
    Quote Originally Posted by DragonPrinces View Post
    Thanks for the Advice on the scatter chart. now that i have these i'm still abit confused.

    So assuming total need exp is series 1 exp between levels is series 2 and then the second difference is series 3

    the formulas i got from the scatter graph are wierd and they change abit wierd aswell. let me try and explain.

    The series 1 formula is as follows
    y= 4x2 + 10x + 6E-11
    The series 2 formula is
    y= 4E-16x2 + 8x + 6
    The series 3 formula is
    y= 1E-17x2 - 2E-15x + 8

    Now thats if i have the scatter graph for all the data from #1 - 100

    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.

    The question is, do these answers work?

    The answer for the second column works

    y = 8x + 6

    for x = 1, y = 8(1) + 6 = 14 (you have this space listed as blank, but this answer implicitly assumes there is a level 0 needing experience 0, so that the difference between levels 1 and 0 is 14).
    for x = 2, y = 8(2) + 6 = 16 + 6 = 22
    for x = 3, y = 8(3) + 6 = 24 + 6 = 30
    for x = 4, y = 8(4) + 6 = 32 + 6 = 38
    for x = 5, y = 8(5) + 6 = 40 + 6 = 46
    for x = 44, y = 8(44) + 6 = 8(40 + 4) + 6 = 8(40) + 8(4) + 6 = 320 + 32 + 6 = 352+6 = 358 (why use a calculator when you don't need to? )


    Does the answer for the first column work?

    y = 4x2 + 10x

    for x = 1, y = 4(1)2 + 10(1) = 4 + 10 = 14
    for x = 2, y = 4(2)2 + 10(2) = 4(4) + 20 = 16 + 20 = 36
    for x = 3, y = 4(3)2 + 10(3) = 4(9) + 30 = 36 + 30 = 66
    for x = 4, y = 4(4)2 + 10(4) = 4(16) + 40 = 64 + 40 = 104
    for x = 5, y = 4(5)2 + 10(5) = 4(25) + 50 = 100 + 50 = 150
    for x = 44, y = 4(44)2 + 10(44) = 4(1936) + 440 = 7744 + 440 = 8184 (I cheated and used a calculator for this one)

    So we have our answer. If y is the experience needed at level x, then the relationship between y and x is given by the equation:

    y = 4x2 + 10x
    Last edited by j-astron; 01-27-2018 at 12:01 AM.

  5. #5
    Isn't it just: Previous + Level * 8 + 6

    So lv 0 needs 0.
    lv 1 needs 0 + 1 * 8 + 6 = 14
    lv 2 needs 14 + 2 * 8 + 6 = 36
    lv 3 needs 36 + 3 * 8 + 6 = 66

    The level incremental is just Level * 8 + 6.

  6. #6
    Junior Member
    Join Date
    Jan 2018
    Location
    Toronto
    Posts
    65
    Quote Originally Posted by SMcNeill View Post
    Isn't it just: Previous + Level * 8 + 6

    So lv 0 needs 0.
    lv 1 needs 0 + 1 * 8 + 6 = 14
    lv 2 needs 14 + 2 * 8 + 6 = 36
    lv 3 needs 36 + 3 * 8 + 6 = 66

    The level incremental is just Level * 8 + 6.
    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.

  7. #7
    Quote Originally Posted by j-astron View Post
    Hi DragonPrinces, I think you're on the right track.

    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.

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •