I was wondering if anyone could tell me if there is a formula that I can input into excel that will be able to pinpoint the inflection points in a running thread of data?
I was wondering if anyone could tell me if there is a formula that I can input into excel that will be able to pinpoint the inflection points in a running thread of data?
Just to be clear, when I think of "inflection points," I think back to my calculus class where we would talk about inflection points as the point where the concavity changes --> meaning that the second derivative changes sign (http://en.wikipedia.org/wiki/Inflection_point). If this is what you are thinking of when you think of inflection point, then I doubt you are going to find a simple, built in formula that will find the inflection point.
If I were building a spreadsheet to find inflection points, the general algorithm would look something like this:
1) Smooth the data if needed
2) Calculate first derivative
3) Calculate 2nd derivative.
4) Using a suitable root finding algorithm, find the roots (zeros) of the 2nd derivative.
5) Test that the sign of the 2nd derivative changes on both sides of the roots
6) List out the inflection points.
Of course, at this point the algorithm is very generic -- we haven't really started talking about how to do this in Excel. If you can provide some specifics of how you want to proceed specifically what parts you don't know how to accomplish, then we can provide more specific suggestions.
Originally Posted by shg
I am a biologist doing a postgraduate degree so basically I have no idea how to do this. I have data files of dive data for Whales. The data looks wave like in shape when viewed on screen, where the whale has swum up and down. My supervisor has asked me to come up with a 'rule' which pin points the points of inflection in the whales trajectory (or the concavity changes), I thought there might be a way of applying a formula to the data to pin point these inflections? But as I said, I am a biologist not a mathematician or a excel wizz kid so I am well aware it may not be this straight forward!
With a BS in Zoology myself, I can appreciate that most biology degrees do not require higher math. I've tried to think of how to approach this problem without calculus, and I just can't seem to see past the calculus application. I can appreciate your difficulty, because I find things are a lot easier to do in Excel if I already understand the math I want to use for the problem.
The concept of the derivative covers about 1/2 of a calculus course, so there is not way I can teach this concept in a single internet forum post. The best I can do is explain that the derivative of a function is basically an extension of the concept of the slope of a line that you should have learned in algebra. If you look at the animation in the wikipedia article half to two-thirds down the page, you can see them showing the slope of the curve at different points along their sample curve. In many ways, what you are being asked to do is a lot like what this animation is doing -- determining the slope at different points along the curve and locating the points where the slope is changing concavity (the "red" lines in the animation).
A few suggestions of how to approach this with only algebra to fall back on:
1) The "easiest," though it will be hard to automate, might be a graphical approach like the animations noted above. Plot a graph of the raw dive data. Visually scan the graph and mark an X where you judge the inflection points are. Read the coordinates of those points off of the graph and enter that data into the spreadsheet for further analysis.
2) If you feel you need to automate this in the spreadsheet, you are going to need to figure out slopes (again, a concept you should have learned in algebra) of the curve you have. Then you will need to analyze that column, looking for "changes" in the slope. You might calculate a slope based on the slope (that would be the 2nd derivative) and see if those values give you some good indicators. The thing you will need to watch for is that the raw data might be "noisy," so there might be false or incorrect inflection points returned. Automating this kind of approach means that you will need to program the spreadsheet to figure out the "false positives" and so on.
I'm not sure what your instructor is expecting to get out of this (something publishable? a quick analysis to suggest where more in depth analysis is needed? an exercise for your educational enhancement?). In many ways, this isn't a trivial problem to program in. I know it would be tedious, but option 1 would not take much programming effort -- It might take less effort to do it graphically/manually than it will take to figure out a robust algorithm to have the spreadsheet do it automatically.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks