max and min of trendline curves

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

hi,
I have made a chart of data which then has a trendline
added to get a line of best fit. The x and y co-
ordinates of the maximum of this trendline is the data
that I need. I can show the trendline equation which is
an Ax^2 + Bx + C curve which is simple to differentiate
and find the max, in my head, but as there are many of
these curves I would like to do this automatically. How
can I do this? I am running Excel 97 and have good
knowledge of VB and macros.
cheers
 
Sarah -

Check in the google groups archive
(http://www.google.com/advanced_group_search), in these *Excel* groups,
for a macro posted by Dave Braden to get trendline coefficients into the
worksheet. Once these are in the sheet, you could probably write a
couple formulas to differentiate them, find where the slopes vanish, and
determine to coordinates of those points.

- Jon
 
Since you have indicated a high comfort level with VBA, hopefully the
following is enough to get you going.

I would go with the plan below rather than Jon's pointer to David
Braden's code, even though for a few cases the trendline coefficients
may be more accurate than the LINEST values.

It is possible to use the LINEST function in VBA. That function can be
used with a polynomial trendline, popular belief and documentation not
withstanding.

Combine the two and you will land up with a VBA variant that contains a
2D array with the first and second entries in the first row containing
the A and B values. From there, finding the min/max should be a trivial
exercise as will be deciding whether you have a maxima or a minima.

Some code with minimal testing:

Option Explicit

Function VBALinest(x, y)
Dim Rslt, TempX, MyX, i As Long
If TypeOf x Is Range Then
TempX = x
ReDim MyX(LBound(TempX) To UBound(TempX), 1 To 2)
For i = LBound(TempX) To UBound(TempX)
MyX(i, 1) = TempX(i, 1)
MyX(i, 2) = TempX(i, 1) ^ 2
Next i
Else
ReDim MyX(1 To UBound(x), 1 To 2)
For i = 1 To UBound(x)
MyX(i, 1) = x
MyX(i, 2) = x ^ 2
Next i
End If
VBALinest = Application.WorksheetFunction.LinEst(y, MyX, True, True)

End Function

Sub testIt()
Dim Rslt
Rslt = VBALinest(Range("e1:e11"), Range("F1:F11"))
MsgBox "A=" & Rslt(1, 1) & ", B=" & Rslt(1, 2) & ", C=" & Rslt(1, 3)
End Sub
Note that E1:E11 contained the values -5, -4, ... 4, 5 and F1 contained
=2*E1^2+3*E1+4 (copied down to F2:F11).

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
 
Back
Top