Interpolating missing data

  • Thread starter Thread starter john
  • Start date Start date
J

john

Hi all,

I am a novice at this so please make allowances, I am looking to use a
vlookup function to access a 2D matrix of data, the problem is that the
2D data is only partially complete (currently ~20%). What I would like
to do is fill in what I do know and have excel interpolate the interim
values. I am only interested in the interim values and not
extrapolated, so I think the trend function is not a good fit for what
I need? What I would like to be able to do is access the trendline
available in charting and use this to return the missing data! Is this
possible?
Any pointers would be welcome

Best JAG-W
 
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

--
Regards,
Tom Ogilvy




(e-mail address removed)...
 
Tom said:
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
Click to expand...

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
 
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=#[email protected]

From: "Chip Pearson" <[email protected]>
References: <[email protected]>
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: <[email protected]>
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
 
Back
Top