I solved the problem using various string manipulations. The resultant function obtains the equation that describes a trendline. Then it allows the user to input a value into that equation in order to get out an interpolated value based on the trendline equation.
Thanks for the help,
best regards,
Andrew
Function TrendLineValue(ByVal XValue As Double, ChartName As String, SeriesName As String) As Variant
'EXPLANATION
'The purpose of this function is to obtain values interpolated from existing data, using
'a trend line of the existing data. The parameters SeriesName and ChartName point
'this function to the series that has the trend line. It then obtains the equation that
'describes the trend line.
'The Function then uses XValue as the input value to the trend line equation. The
'resultant value of the trendline equation is the required interpolated value.
'Note: This function current only works for linear and power trendlines. The case statement
'is easy enough to modify if trendlines of other types are required.
'This function will fail if the specified series does not have a trend line
'PARAMETERS=============================================================================
'SeriesName: The name of the series that has the trendline, the equation of which is
'required.
'ChartName: The name of the chart that displays the series and associated trendline.
'XValue: The input value that will be put into the trendline equation in order to
'return the required value.
'VARIABLES=============================================================================
'TLEqn: The equation of the trendline
'TLEqnMod: The modified version of the trendline
'Variable Declarations=================================================================
Dim TLEqn As String
Dim TLEqnMod As String
Dim TLEqualPos, TLXPos, TLAddPos
Dim TLCoeff, TLExp, TLConst
Dim Area As Double
Dim TLType As Long
'BODY ==================================================================================
'Obtain the type of trendline that has been used
TLType = Charts(ChartName).SeriesCollection(SeriesName).Trendlines(1).Type
'Obtain the equation that describes the trendline
TLEqn = Charts(ChartName).SeriesCollection(SeriesName).Trendlines(1).DataLabel.Text
'remove the 'y' from the start of the trend line equation
TLEqnMod = Right(TLEqn, Len(TLEqn) - 1)
'remove spaces from the string
TLEqnMod = Trim(TLEqnMod)
'The case statement replaces the 'x' in the equation with a reference to a cell.
'Also, it turns the string into a proper equation based on the type of trend line that has
'been used.
Select Case TLType 'this is identifies the type of trendline
Case xlLinear
'find the position of =, x and + in the equation
TLXPos = InStr(1, TLEqnMod, "x", vbTextCompare)
TLEqualPos = InStr(1, TLEqnMod, "=", vbTextCompare)
TLAddPos = InStr(1, TLEqnMod, "+", vbTextCompare)
'Obtain the constants in the linear equation
TLCoeff = CDbl(Trim(Mid(TLEqnMod, TLEqualPos + 1, TLXPos - TLEqualPos - 1)))
TLConst = CDbl(Trim(Mid(TLEqnMod, TLAddPos + 1, Len(TLEqnMod) - TLAddPos)))
'Evaluate the Trend line equation using the user input and return the value
TrendLineValue = TLCoeff * XValue + TLConst
Exit Function
Case xlPower
'find the position of =, x and + in the equation
TLXPos = InStr(1, TLEqnMod, "x", vbTextCompare)
TLEqualPos = InStr(1, TLEqnMod, "=", vbTextCompare)
'Obtain the constants in the linear equation
TLCoeff = CDbl(Trim(Mid(TLEqnMod, TLEqualPos + 1, TLXPos - TLEqualPos - 1)))
TLExp = CDbl(Trim(Mid(TLEqnMod, TLXPos + 1, Len(TLEqnMod) - TLXPos)))
'Evaluate the Trend line equation using the user input and return the value
TrendLineValue = TLCoeff * XValue ^ TLExp
Exit Function
Case xlExponential, xlLogarithmic, xlMovingAvg, xlPolynomial
'This part needs to be modified if this function is to cope with the above
'types of trendlines.
TrendLineValue = "This type of trendline is not supported. Currently, only linear and power trendlines are supported"
Exit Function
Case Else ' Other values.
TrendLineValue = "This type of trendline is not supported. Currently, only linear and power trendlines are supported"
Exit Function
End Select
End Function
This message was edited by andrew.ward on 2002-05-09 21:11
This message was edited by andrew.ward on 2002-05-12 14:44