Slope challenge

I have written a VB macro which does the following:
graphs a two dimensional array of numbers.
calculates the MAX, MIN, RANGE, MEAN and STDEV.

How do i calculate the largest change in the slope of a graph?
How do i determine where the largest change in the slope occurs?

Are you after the 1st or 2nd derivative? As a math minor, I interpret "change in slope" to mena 2nd derivative, but I expect you just want the slope (1st derivative).

Anyway, here's how I would get a first approximation for the 1st derivative:

If the "x" data are in column A, and the "y" data are in column B, I would add a third column called "slope". Set cell C3=(B3-B2)/(A3-A2) (ie slope=(y2-y1)/(x2-x1)) then copy that down column C. Now you have an estimate of the slope of the function over each interval, and you can use MAX, MIN, AVERAGE, etc to get whatever information you want from the slope column.

Does that seem reasonable?

I need to get the maximum change in the slope, not the maximum slope.
I was already doing what you suggested. I have another column which calculates the absolute value of the difference in the slopes. It subtracts the slope value of cell C4 from cell C3 and so on. I then get the maximum value of that column.

Is there an easier way of doing it?

How do i determine where the largest change in the slope occurs using a macro?

So I was right the first time -- you did want the 2nd derivative. Note to self -- stop assuming so much.

Basically, it's the same algorithm, different programming language. If this were me (I would use a function procedure rather than a "macro":

Function maxd2f(x,y) 'x is array of x values, y is array of y values passed from worksheet
dim df(100) as double, d2f(100) as double 'it's also possible to dimension these to be the same size as x and y.
dim dummy as double, xmax as double
n=x.count
'calculate slope
for i=2 to n
df=(y(i)-y(i-1))/(x(i)-x(i-1)) ' calculate slope for point i and associate it with the "second" data point.
next i
'calculate change in slope
for i=3 to n
d2f=(df(i)-df(i-1))/(x(i)-x(i-1)) 'calculate change in slope for point i and associate it with the "third" data point
next i
'Determine max change in slope and at what point it occurs
xmax=0
dummy=0
For i=3 to n
if d2f(i)>dummy then
dummy=d2f(i)
xmax=x(i)
end if
next i
maxd2f=array(dummy, xmax)
end function

Function returns two values in a horizontal array, so it must be entered as an array function over two cells. I haven't debugged the code, but that should get you started. if you want absolute values of slopes, then you'll need to add the abs function to appropriate statement. Note that I also have used the same basic numerical derivative. If you decide you need a different numerical derivative to try to get better accuracy, then you would just need to change the statements that calculate the derivative.

At the moment, i am using a random number generator in my VB program to create my two dimensional array.
Below is the code which carries this out.

'Initialize the random number generator.
Randomize

' Create an array with 100 rows and 2 columns.
Dim DataArray(1 To 100, 1 To 2) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = r
DataArray(r, 2) = Rnd() * 1000
Next

Seeing that i'm getting Excel to do the graphing and all of the other calculations wouldn't it be easier for it to calculate the slope part aswell by putting another Macro in my VB program?

If not how do i feed this array into the code which you provided in the above post?

Please bear in mind that at a later date i will have to replace my random number generator with readings from a microcontroller via a serial port.

Little by little I'm starting to understanding what you're trying to do and how you've got yourself organized.

In your program, do you want only the max change in slope and interval over which this maximum occurs, or do you also want to output the 1st and 2nd derivatives to a spreadsheet with the raw x-y data? The former could pretty easily be accomplished by calling the function from within your existing subroutine (it would require some modification to match your data structure). The latter would probably be easiest to accomplish by adapting the above code, either as a subroutine procedure instead of a function procedure, or incorporating the code into your existing subroutine.

All i need is the second derivative and the point at which it occurs.

Ideally, i would be able to insert a second derivative function into my spreadsheet, select the two data columns (X and Y) and hey-presto, the answer would appear.

The next best thing would be to insert a single formula instead of a function.

My VB program must be able to deal with an unknown quantity of data points. The macro which i currently have prohibits me from doing this as i must drag down the number of rows for that particular array size.

Well, I was waiting to see if anyone knew of a built-in 2nd derivative function, but, from the lack of respnses to that, I'm going to assume Excell doesn't have a built-in function to determine the 2nd derivative.

You say all you really need is the max 2nd derivative and the point at which it occurs. That's what the maxd2f function I proposed above is intended to do -- Take a set of x,y data, calculate the slope and the change in slope, then determine the maximum change in slope and the point at which it occurs. It needs a little work to adapt it to your specific situation, but that's its basic purpose.

Once you get the basic algorithm put together, it won't be hard to get it to work on a variable number of data points.