Re: Interpolating missing data



These will only return linear interpolations,

no, that isn't correct.

If you want to graph your data, you can access the equation produced.

Tushar Mehta:
Trendline coefficients
http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm



You can see how Bernard Liengme solves for the coefficients of a polynomial
curve using LINEST.
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

==========================================

Chip Pearson posted some code to get the values from the chart itself:

http://groups.google.com/groups?threadm=%23hup3qhhEHA.1652%40TK2MSFTNGP09.phx.gbl

From: "Chip Pearson" <chip@xxxxxxxxxxxx>
References: <A62DB3F5-4262-4B41-9328-6D65D3B77725@xxxxxxxxxxxxx>
Subject: Re: Trendline Data
Date: Thu, 19 Aug 2004 13:28:02 -0500
Lines: 73
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
Message-ID: <OR5ptphhEHA.712@xxxxxxxxxxxxxxxxxxxx>
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: cpe-65-26-82-203.kc.rr.com 65.26.82.203


If you mean to get the polynomial coefficients, try something
like the following:

Dim TL As Trendline
Dim A As Double, B As Double, C As Double, D As Double, _
E As Double, F As Double, G As Double
Dim S As String
Dim Arr As Variant
Dim Pos As Integer
Set Ser = Chart1.SeriesCollection(1)
Set TL = Ser.Trendlines.Add(xlPolynomial, 6) ' change 6 to
appropriate order
TL.DisplayEquation = True



TL.DataLabel.NumberFormat = "0.000000000000000"
S = TL.DataLabel.Text
Pos = InStr(1, S, "=")
S = Mid(S, Pos + 1)
S = Replace(S, " + ", "|+")
S = Replace(S, " - ", "|-")
Arr = Split(S, "|")

A = 0: B = 0: C = 0: D = 0: E = 0: F = 0: G = 0
' g
Pos = InStr(1, Arr(LBound(Arr) + 0), "x")
G = CDbl(Left(Arr(LBound(Arr) + 0), Pos - 1))

' f
Pos = InStr(1, Arr(LBound(Arr) + 1), "x")
F = CDbl(Left(Arr(LBound(Arr) + 1), Pos - 1))

' e
Pos = InStr(1, Arr(LBound(Arr) + 2), "x")
E = CDbl(Left(Arr(LBound(Arr) + 2), Pos - 1))

' D
Pos = InStr(1, Arr(LBound(Arr) + 3), "x")
D = CDbl(Left(Arr(LBound(Arr) + 3), Pos - 1))

' c
Pos = InStr(1, Arr(LBound(Arr) + 4), "x")
C = CDbl(Left(Arr(LBound(Arr) + 4), Pos - 1))

' b
Pos = InStr(1, Arr(LBound(Arr) + 5), "x")
B = CDbl(Left(Arr(LBound(Arr) + 5), Pos - 1))

' a
A = CDbl(Arr(UBound(Arr)))


Cordially,
Chip Pearson


--
Regards,
Tom Ogilvy






"JAG-W" <john@xxxxxxxxxxxxxxxx> wrote in message
news:1159709059.159857.38840@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Tom Ogilvy wrote:
If you want to do a linear interpolation, then use the Forecast
function.
FORECAST(x,known_y's,known_x's)

=FORECAST(2,{10,20},{1,3})
returns 15 for example.

Also look at LINEST

These will only return linear interpolations, I was hoping to be able
to some how to access the trendline formulae that the charting function
has and use that, but this seems not to be possible :(

Best

JAG-W



.



Relevant Pages

  • Re: Extract formula from Text box
    ... Dim sStr As String, sStr1 As String ... Dim cht As Chart ... >>>Scatter chart and adds a Third-order Polynomial Trendline. ... >> If ParamErrThen Exit Function ...
    (microsoft.public.excel.programming)
  • Re: Using VBS Arrays As Data Ranges In Excel
    ... SaveAsGif and also the Trendline I added. ... Const ForReading = 1 ... Dim objExcelApp ' Excel application object ... 'Order - Optional Variant. ...
    (microsoft.public.scripting.vbscript)
  • Re: Using VBS Arrays As Data Ranges In Excel
    ... SaveAsGif and also the Trendline I added. ... Dim objFSO, objShell ... Const ForReading = 1 ... 'Order - Optional Variant. ...
    (microsoft.public.scripting.vbscript)
  • Re: How to display 4 order Polynomial equation from the Excel in V
    ... I use a ChartSpace control in foreground to show the same ... Dim chConstants ... ' Add a trendline to the first series and return ... How to display 4 order Polynomial equation from the Excel in VB6? ...
    (microsoft.public.vb.general.discussion)
  • Re: Excel option to store trendlines coefficients in cells for use
    ... evaluates the current trendline at a given x ... vCht is the name/number of the chart. ... The coefficients are returned to precision *displayed* _ ... On Error GoTo 0 ...
    (microsoft.public.excel.charting)