0

I have table with 500 rows where I have a row number and cost value. Here is first 100 of them.

0,25
0,30
0,35
0,40
0,45
0,50
0,55
0,60
0,65
0,70
0,75
0,75
0,80
0,85
0,90
0,95
0,95
1,00
1,05
1,10
1,15
1,20
1,25
1,35
1,40
1,50
1,55
1,60
1,70
1,75
1,85
1,90
2,00
2,05
2,15
2,25
2,30
2,40
2,50
2,60
2,70
2,80
2,90
3,00
3,10
3,25
3,35
3,45
3,55
3,65
3,80
3,90
4,05
4,20
4,35
4,50
4,60
4,75
4,90
5,05
5,20
5,35
5,50
5,65
5,80
6,00
6,15
6,30
6,50
6,65
6,85
7,05
7,25
7,45
7,65
7,85
8,05
8,25
8,45
8,65
8,85
9,10
9,35
9,60
9,85
10,10
10,35
10,60
10,85
11,10
11,35
11,60
11,90
12,20
12,50
12,80
13,10
13,40
13,70
14,00

When I graph them, it looks exponential.

enter image description here

But when I look at difference between each row value, graph looks jittery.

enter image description here

Log of value

enter image description here

Factor (division) of value

enter image description here

What steps should be made if I wan't to create formula so that I can predict next 1000 (or more) values?

EDIT: Manually, I have got so far to use 0,178*SQRT(x)-0,730 to predict values, but it has an error in range of 0..25% with strange pattern.

enter image description here

It seems that there is something more to it than this simple formula.

How and what best fit curve software to use to get as close as possible?

PS: These are cost of stat points in RPG game. It could be that there is custom function with some sort of balancing. How to determine if it is custom?

Deele
  • 101
  • The graph is expected to look jittery because your values are not continuous and because of noise. A more usual visual tool to see if your data is actually exponential is to make logarithmic plot: Basically, take the logarithm to your data and plot that - if it is exponential, it will be a straight line. Also, perhaps you wanted to plot the factor (and not difference)? That should be constant if it is exponential. If you want a function for your data, you should either fit an exponential function to your data or, perhaps more reliable, fit a straight line to the logarithm of the data. – Bobson Dugnutt Mar 17 '17 at 10:21
  • @Lovsovs do you mean that I need to calculate (in Excel) =LOG(B2; 10) where B column are values and 10 is base, correct? And by factor you mean =B3*B2 where B2 is previous value and B3 is next value, correct? – Deele Mar 17 '17 at 10:49
  • Correct and not correct: For the latter I mean the ratio of two consecutive datapoints (if all points have an equal distance on the $x$-axis), i.e. $k_n=\frac{B_n}{B_{n+1}}$ (or the other way around - it doesn't matter as long as you are consistent). If $k_n$ is the same number for all $n$, the data is exponential. – Bobson Dugnutt Mar 17 '17 at 11:08
  • @Lovsovs Look at my edits. I hope I did it correctly. – Deele Mar 17 '17 at 11:33
  • 1
    Hmm, it clearly isn't exponential. As the resulting function (in the logplot) looks a bit like like a square root (like this, the function you're looking for might be on the form $ae^{b\sqrt{x}}$ but perhaps it just isn't a simple function. – Bobson Dugnutt Mar 17 '17 at 11:52
  • @Lovsovs Yeah, it really looks like square root! How did you came up with those coefficients in 0,16 * sqrt(A1) - 0,5? Can you please explain in full length answer, what steps should be made to come up with your assumptions, and what should be done next, to determine if it is or it is not exponent and how to get to the result? – Deele Mar 17 '17 at 12:03
  • Those coefficients were just a (probably bad) guess. I will make an answer to collect all the information. – Bobson Dugnutt Mar 17 '17 at 12:16
  • @Lovsovs By playing with those two numbers, I came up to closer line using 0,177 * sqrt(A1) - 0,73 see plot https://image.ibb.co/izgjTv/Snap_2017_03_17_at_14_22_04.jpg But still, middle does not match and it seems, that with higher values it is incrementing too fast. – Deele Mar 17 '17 at 12:28
  • You need to use some software built for fitting (look up how to do it with excel) to get the best results. I tried to construct an answer, but I wasn't able to construct a model I think I would fit better than the square root, so I'm holding off on that for now. I might answer later if I figure it out, but don't count on it. Good luck! – Bobson Dugnutt Mar 17 '17 at 12:32
  • @Lovsovs Please make an answer and collect all the information like you promised :) What to do now with that log plot? How to get to the formula to calculate value predictions? – Deele Mar 17 '17 at 12:39

1 Answers1

2

As seen from the logplot, your function $f(x)$ is not exponential (the line is not straight). However, observe that it looks a bit like a square root, so

$$f(x)=ae^{b\sqrt{x}}$$ would be a potential model to fit after. But note how your function actually is zero (for $x=1$ it seems). An exponential function can't do this ($e^0=1$). This hints that the function may instead be of the form $$f(x)=axe^{b\sqrt{x}},$$ which would be my best guess (it has this form - ignore the orange lines). To find the coefficients $a,b$ that fits your data the best, you need to usage some dedicated software (Excel might be able to fit after custom functions, I don't know) to find the best guess.

Good luck!

Bobson Dugnutt
  • 10,672
  • How to put in excel that last formula? I tried with =$I$2 * $A3 * EXP($I$3 * SQRT($A3)) where $A3 is x, $I$2 is a, $I$3 is b. But whatever I do with coefficients it does not come close to values. – Deele Mar 17 '17 at 13:46
  • @Deele The values need to be very small, around $a=1.1$ and $b=-0.1$. However, you shouldn't do this "by hand", you should get some software to do it for you, as I've been trying to tell you. – Bobson Dugnutt Mar 17 '17 at 14:02
  • @Deele Install the built-in, but optional, Solver Add-on for Excel. It can minimize the error by tweaking the parameters. – Ark-kun Dec 26 '17 at 11:47