0
$\begingroup$

Apologies if the question is phrased incorrectly, i've struggled to understand the right terminology which would have allowed me to research this myself.

I have a series of probabilities in a table, where the probability of an event happening three times is less than the probability of it happening twice.

I have historical data of predictions showing the predicted probability of an event happening two and three times. I then have more predictions which only show the probability of it happening twice, but are missing the predictions for the event happening three times.

The complete data that had predictions for the event happening both two and three times appears to increase in a fairly logical pattern and I think I should be able to estimate what the probability of the event happening three times is where that data is missing.

two_events three_events diff percent_increase
20.78 5.42 15.36 73.917228
21.14 5.58 15.56 73.604541
23.78 6.76 17.02 71.572750
23.96 6.89 17.07 71.243740
24.98 7.32 17.66 70.696557
24.99 7.32 17.67 70.708283
25.22 7.43 17.79 70.539255
26.65 8.14 18.51 69.455910
28.02 8.85 19.17 68.415418
29.57 9.67 19.90 67.297937
30.94 10.42 20.52 66.321913
31.05 10.49 20.56 66.215781
31.29 10.62 20.67 66.059444
32.00 11.03 20.97 65.531250
32.71 11.44 21.27 65.025986
34.21 12.33 21.88 63.957907
34.31 12.39 21.92 63.888079
35.56 13.17 22.39 62.964004
36.13 13.52 22.61 62.579574
36.46 13.74 22.72 62.314866
36.58 13.81 22.77 62.247130
38.86 15.31 23.55 60.602162
39.97 16.06 23.91 59.819865
41.62 17.22 24.40 58.625661
41.83 17.37 24.46 58.474779
42.95 18.22 24.73 57.578580
45.15 19.35 25.80 57.142857
46.33 20.73 25.60 55.255774
48.45 22.42 26.03 53.725490
48.52 22.49 26.03 53.647980
49.80 23.54 26.26 52.730924
50.78 24.37 26.41 52.008665
52.55 25.90 26.65 50.713606
53.11 26.40 26.71 50.291847
61.85 34.84 27.01 43.670170
62.34 35.36 26.98 43.278794
64.98 38.22 26.76 41.181902
69.28 43.17 26.11 37.687644
72.29 46.91 25.38 35.108590
75.13 50.63 24.50 32.610142

I think I should be able to use this to predict that if the probability of two events happening is 21%, then the probability of three events happening is going to be roughly 5.50% for example.

two_events three_events diff percent_increase
20.78 5.42 15.36 73.917228
21 (example) ??? (presumably 5.50 or 5.51) ??? ???
21.14 5.58 15.56 73.604541

To be clear the first column is not sequential in anyway, I am only looking at predicting what the second column would be based on the first.

It also does not need to be a perfect formula, only roughly correct

But I do not know how to come up with a formula to calculate this or the right terminology to use to search for it.


Thanks to a great link in the answers i've been given the following formula:

$$y=a(x+b)^d+c$$

With these variables:

a = 0.00000154534 b = 75.0975 c = 3.47686 d = 6.59594

But I am not sure how to write that out in something like Excel or code such as Python, Javascript or SQL.

If I use

((0.00000154534(21+75.0975))^3.47686)+6.59594

to work out the value of 21, which should be 5.50, they all result in an error and seem to require something between a(x+b).

I assume there is a function for whatever a(x+b) is doing that you would have to use in Excel or similar, but i'm afraid I have no idea what it is called and have found it impossible to Google.

Confusingly typing ((0.00000154534(21+75.0975))^3.47686)+6.59594 into Google comes out with 6.59594 so i'm clearly making a basic error somewhere.

Could you please provide an example either using an Excel formula or code?

$\endgroup$

1 Answer 1

1
$\begingroup$

Welcome to MSE!

What you're describing sounds like you're looking to interpolate or extrapolate from this data to other possible points.

You have two possibilities. In both cases, though, the difference $P_2-P_3$ isn't all that useful. Nor is the increase from one data point to the next. What you'd really like is to find a function such that $P_3=f(P_2)$.

Since this data set looks relatively smooth, you could just do two-point interpolation. In other words, if you have a $P_2$ within the bounds of the dataset, take the points above and below it, and pretend the line they define is the function (for this part of the data). You don't even really have to "graph" it, just find the formula of the line and plug in your $P_2$ value.

Sadly, that only works for interpolation. If you need something outside the bounds of the dataset, you'll have to do more.

Better than two-point interpolation is to use a curve-fitting program. There are several places online to do a curve-fit; personally I like Desmos because it plays well, though sometimes it doesn't like copy/paste. You also need some guess as to the form of the equation for Desmos--other systems likely do, Excel being an example.

This looks approximately quadratic, at least in the middle of its range. Possibly a different exponent would be useful. Letting the $x$-axis be $P_2$ and the $y$-axis be $P_3$, I'd try fitting to the forms: $$y=ax^2+c$$ or more complex: $$y=a(x-b)^d+c$$ to account for a possible offset on the $x$-axis.

Good luck!

(I say approximately quadratic because, for instance, $16=40\% \cdot 40$, and $24 \approx 50\%\cdot 50$.)

$\endgroup$
5
  • $\begingroup$ Thanks for the response. Unfortunately I didn't really understand it. I think you are saying that if I used the data set to create a graph with a curve, the curve would show at any point what the probability of three events would be relative to the probability of two events. I have created a basic graph with Desmos with the data desmos.com/calculator/yr2neqziro however I cannot see how this would then lead to a formula for calculating y when you only have x. $\endgroup$ Commented Aug 3, 2022 at 13:16
  • $\begingroup$ You're halfway there! I guess creating a curve fit in Desmos isn't very simple, so I've put in two examples; one with a quadratic expression, the other with an unknown exponent. Once you have your data table, you have to write $$y_2 \sim ax_2^2 + b$$ or something like it to get a curve fit. Here's the Desmos link. Essentially you can treat the equations it creates as pretty good fits, and use them as your $f(x)$. (If you search "curve fitting software" you may come up with easier options.) $\endgroup$ Commented Aug 3, 2022 at 22:22
  • $\begingroup$ Thanks you so much that has been a great help, however i'm still a little stuck as i'm unsure how to calculate a(x+b). In very simple maths I would expect some symbol such as plus, minus, times or divide between a and the brackets. I assume there is a term for how to calculate this but i've found it impossible to Google. I've updated the question with a more detailed explanation of where I am confused and specifically examples using Excel or Code which I think would help me understand. $\endgroup$ Commented Aug 4, 2022 at 12:26
  • $\begingroup$ @JohnMellor I've posted a slightly simplified Desmos graph. If you use the quadratic fit (the first one), and drop the subscripts from $x$ and $y$, you get $$y = a(x+b)^2 + c$$ Then, notice that under the fitting equation there is a list of "parameters." And that contains the numerical values of $a,b,c$. So the proper equation (ignoring all but $3$ significant digits) is $$y = 0.00802(x+3.32)^2 + 0.940$$ Does that make sense? $\endgroup$ Commented Aug 4, 2022 at 22:59
  • $\begingroup$ Thankyou, I think i've figured it out now. The use of the brackets and the squared was confusing and I was struggling to convert it to something excel or code could understand. For my use it was easier to write it out as: (0.00802 * ((x+3.32) * (x+3.32))) + 0.940 $\endgroup$ Commented Aug 8, 2022 at 10:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .