CODEWITHSUNDEEP
excelvbamath
AM847
AM847

Reputation: 31

Finding the Inflection Point

I currently have two sets of data, a x and y axis, and I need to find the point where it changes from a positive slope to negative slope. Is there anyway of finding that data in VBA or a function within excel?

Upvotes: 0

Views: 27269

Answers (4)

jalex
jalex

Reputation: 1524

The best you can do in general is use finite differences to approximate the 2nd derivative and find where that changes sign.

Given two columns X and Y estimate the 2nd derivative Ypp away from the ends with the following formula. Assumes the x values increment with a fixed value h

figx

figy

X Y ≈Ypp
x_1 y_1
x_2 y_2 =(y_3-2*y_2+y_1)/(h*h)
x_3 y_3

The inflection point is where the 2nd derivative switches signs. You can simply find where two consecutive values multiply to a negative value ypp_2*ypp_1 <= 0.

If you want more precision then you need to fit a model to the data, or go with cubic splines.

Note that if the X data isn't with equal intervals you have to use the more complex formula below

figy2

Upvotes: 1

CharlesPL
CharlesPL

Reputation: 331

May I suggest doing this would by using regression. Not a linear, but a typical multiple order regression, AKA polynomial regression (y = a_0 + a_1*x + a_2*x^2 + ... + a_n*x^n). See this thread for more details on how to do it. This can be done directly in Excel, no need to code anything in VBA. However, you'll probably need to deal with array formulas (AKA CTRL+ Enter formulas).

Then, once, you've find a regression that fits your distribution (r² > 0.9 or what suits you), you could simply do a derivative of this equation. Since this is a polynomial equation, the equation is quite easy : y' = a_1 + 2*a_2*x + ... + n*a_n+1.

The fun part now starts! We need to find what values of x makes y = 0. If your regression is below the 4th order, there is an analytical solution possible (i.e. there's an equation that can gives you the x value, because your derivative will be of order 3). If you are over the 4th order, then, you need to use a numerical method. Yes, you can use VBA to get a bisection algorithm going, but do you know that Excel has a numerical solver integrated? Use it to get the values you are looking for (assuming at least one value is real).

As you didn't supplied exemple of dataset, this is though to figure, but if we use MikeD exemple, we would get this !

With the values, instead of the formulas:

Upvotes: 0

Robert Dodier
Robert Dodier

Reputation: 17576

If there is any noise in the data, computing differences will amplify that noise, so there is a greater chance of finding spurious inflection points. A way to reduce the noise is to fit a curve to the data, and then compute the inflection points for that curve. E.g. fit a cubic polynomial to the data, and find the inflection point of that.

Upvotes: 0

MikeD
MikeD

Reputation: 8941

You can - as an approximation - calculate DeltaY / DeltaX for each subsequent pair of lines and check for change of the sign of this.

Example (starting in [A1] - copy all formulas down from their starting cell)

[B2] =A2^3-A2
[C3] =(B3-B2)/(A3-A2)
[D3] =SIGN(C3)
[E4] =IF(D4<>D3;"beep";"")

X       X^3-x   DY/DX  SIGN(F'(x))  change
-1      0           
-0,9    0,171    1,71   1   
-0,8    0,288    1,17   1   
-0,7    0,357    0,69   1   
-0,6    0,384    0,27   1   
-0,5    0,375   -0,09   -1      beep
-0,4    0,336   -0,39   -1  
-0,3    0,273   -0,63   -1  
-0,2    0,192   -0,81   -1  
-0,1    0,099   -0,93   -1  
0       0       -0,99   -1  
0,1     -0,099  -0,99   -1  
0,2     -0,192  -0,93   -1  
0,3     -0,273  -0,81   -1  
0,4     -0,336  -0,63   -1  
0,5     -0,375  -0,39   -1  
0,6     -0,384  -0,09   -1  
0,7     -0,357  0,27     1      beep
0,8     -0,288  0,69     1  
0,9     -0,171  1,17     1  
1       0   1,71     1  
1,1     0,231   2,31     1

change of slope occurs at relative maxima or minima (1st differential quotient equal 0)

Upvotes: 0

Related Questions