How can i display the formula from a graph

[Guest]

 

[JE McGimpsey]

Assuming the formula is a trendline on a graph, you can use this User
Defined Function:

Public Function DisplayEquation(Optional nSeries As Long = 1, _
Optional nChart As Long = 1)
Application.Volatile
DisplayEquation = Application.Caller.Parent. _
ChartObjects(nChart).Chart.SeriesCollection(nSeries). _
Trendlines(1).DataLabel.Text
End Function

If you're not familiar with UDF's see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and call it from the worksheet as

=DisplayEquation()

for one chart, one series, or, for series 2 of chart 3:

=DisplayEquation(2, 3)


Note that you may need to force a calculation to cause the display to
update.

 

[Tushar Mehta]

Hi JE,

Note that you may need to force a calculation to cause the display to
update.

From what I can tell XL updates charts only after it completes all
worksheet/book computations. So, the DisplayEquation() will *always*
trail the chart equation by one calculation cycle. To see the full
effect of this lag, use an equation that includes RAND() to calculate
the plotted values.

I was playing with something to extract the individual coefficients and
since I often use (embedded) RAND() calls to generate test data, I ran
into the lag effect from the very beginning.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[JE McGimpsey]

Thanks, Tushar - it makes sense, of course.

One can obviously use a worksheet_Calc event macro to recalc the cell:

Private Sub Worksheet_Calc()
Application.EnableEvents = False
Range("A1").Calculate
Application.EnableEvents = True
End Sub

but that's not much more better than using the event to put the equation
into the cell directly.