You can write a vba user defined function to use the trend line formula to evaluate a given x
Here's an example to get started
Function TrendLineValue(x As Double) As Double
Dim c As Chart
Dim t As Trendline
Dim s As String
' Get the trend line object
' this code assumes the first chart on the active sheet,
' and the first series, first trendline
Set c = ActiveSheet.ChartObjects(1).Chart
Set t = c.SeriesCollection(1).Trendlines(1)
' make sure equation is displayed
t.DisplayRSquared = False
t.DisplayEquation = True
' set number format to ensure accuracy
' adjust to suit requirements
t.DataLabel.NumberFormat = "0.0000E+00"
' get the equation
s = t.DataLabel.Text
' massage the equation string into form that will evaluate
' this code assumes 3rd order polynomial
s = Replace(s, "y =", "")
s = Replace(s, "x3", "x^3")
s = Replace(s, "x2", "x^2")
s = Replace(s, "x", " * " & x & " ")
' evaluate for given x value
TrendLineValue = Evaluate(s)
End Function
Try the following User Defined Function (UDF):
Public Function distance(s1 As String, s2 As String) As Double
Dim zum As Double, i As Long
ary1 = Split(s1, ",")
ary2 = Split(s2, ",")
zum = 0
For i = 0 To 2
zum = zum + (CDbl(ary1(i)) - CDbl(ary2(i))) * (CDbl(ary1(i)) - CDbl(ary2(i)))
Next i
distance = Sqr(zum)
End Function
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module
- paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=distance(A1,A2)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
Here is an example:
Best Answer
Small solution for dummies like me
Right click each trendline » Format trendline » Display equation on chart
Open wolframalpha's sub site for Intersection points of two curves/lines
Back in Excel copy both formulas by double clicking them and paste them over to wolframalpha. (Note to me: Replace commas through dots)
You get the x-coordinate
and calculate the y-coordinate yourself by taking one of your two trendline formulas and insert your just calculated x value
y = 1.64 x 0.52245 + 0.034 = 0.890818