How to access a chart series collection formula?

[Jean-Marc]

Hello all,

I use a simple graph to find the minimum of a curve, and use a parabolic fit to get good accuracy on the position and value of that minimum. I want to know the location of the X and Y points used in a graph. If I right-click on the graph, press on "Select Data", and then choose to edit Series 1, I can see the formula used for the X points (=Results!$AZ$243:$AZ$248) and the Y points (=Results!$AZ$243:$AZ$248). I would like to access these formula from vba to allow me to automatically write the minimum value and location at the bottom of the sheet.
I found how to get the values (see below) but had no success getting the actual formula. I could of course do a search of the whole sheet to find the values, but it seems wasteful!

Rem My chart is named "ParabolicFit"...

Dim ValueX As Variant, ValueY As Variant
ActiveSheet.ChartObjects("ParabolicFit").Activate
ValueX = ActiveChart.SeriesCollection(1).XValues
ValueY = ActiveChart.SeriesCollection(1).Values

Thank you for any help!

Jean-Marc

 

[gardnertoo]

The formula can be derived using the LINEST spreadsheet formula. If you are using a y=ax^2 + bx + c formula and getting a good fit, here are the formulas. These are array formulas, so confirm with CTRL+SHIFT+ENTER, not just Enter:

To get a: =INDEX(LINEST($Y$6:$Y$47,$X$6:$X$47^{1,2}),1)
To get b: =INDEX(LINEST($Y$6:$Y$47,$X$6:$X$47^{1,2}),1,2)
To get c: =INDEX(LINEST($Y$6:$Y$47,$X$6:$X$47^{1,2}),1,3)

Replace $Y$6:$Y$47 with the range of cells containing the known Y values of the actual curve, and replace $X$6:$X$47 with the cell containing the know X values.

Credit: here's where I got those formulas