Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interpolating missing data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ti...efficients.htm You can see how Bernard Liengme solves for the coefficients of a polynomial curve using LINEST. http://www.stfx.ca/people/bliengme/E...Polynomial.htm ========================================== Chip Pearson posted some code to get the values from the chart itself: http://groups.google.com/groups?thre...GP09.p hx.gbl From: "Chip Pearson" References: Subject: 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: 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" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interpolating value | Excel Discussion (Misc queries) | |||
Interpolating data timepoints | Excel Discussion (Misc queries) | |||
Interpolating an x, y point from known x's and y's | Excel Discussion (Misc queries) | |||
Help with Interpolating for a value. | Excel Worksheet Functions | |||
Line Interpolating from X-Y data set. | Excel Programming |