Tom Ogilvy
12/18/2006 6:29:00 PM
You were very clear:
Sub ABC()
Dim rng As Range, rng1 As Range
Dim cell1 As Range, cell2 As Range
Dim cell As Range, dSlope As Double
Dim dIntercept As Double
Dim y(1 To 2) As Double
Dim x(1 To 2) As Double
Set rng = Range("B2:B8")
Set rng1 = Range("C2:C8")
Set cell1 = rng1(1)
Set cell2 = rng1(rng1.Count)
If IsEmpty(cell1) Then _
Set cell1 = cell1.End(xlDown)
If IsEmpty(cell2) Then _
Set cell2 = cell2.End(xlUp)
If cell1.Row > 8 Or cell2.Row < 2 Then Exit Sub
If cell1.Row = cell2.Row Then
rng1.Value = cell1.Value
Exit Sub
End If
y(1) = cell1
y(2) = cell2
x(1) = cell1.Offset(0, -1) - rng(1)
x(2) = cell2.Offset(0, -1) - rng(1)
dSlope = Application.Slope(y, x)
dIntercept = Application.Intercept(y, x)
For Each cell In rng1
If IsEmpty(cell) Then
cell.Value = (cell.Offset(0, -1) _
- rng(1)) * dSlope + dIntercept
End If
Next
End Sub
--
Regards,
Tom Ogilvy
"amirstal" wrote:
> I guess I was not clear enough, so I'll try again.
> This is what I have now:
> B C
> 2 12/19/2006 0.999706
> 3 12/20/2006
> 4 12/21/2006
> 5 12/22/2006
> 6 12/23/2006
> 7 12/24/2006
> 8 12/25/2006 0.998675
>
> I can solve for C3, C4 etc.
>
> But when I open my excel tomorrow, for example, the number in C2 might
> be now in C3 and the one in C8 might go to C7. In this case I will
> still want to solve for anything that is between a number above and
> below...
>
> Thanks.
>
>
> Tom Ogilvy wrote:
> > You need 4 values. Two "x" values and two "y" values.
> >
> > then you just solve for the equation of the line and substitute in the new
> > value of x for which you want a estimated value of Y.
> >
> > In your example, 1 and 8 I assume represent the x values.
> >
> > If the values you have are 2 and 7, then use those as your x values and
> > solve for the equation. Once you have the equation, it doesn't make any
> > difference where the new x value is in relation to the known values (from a
> > mechanical standpoint - obviously building an extimate of a value far away
> > from the known values is usually ill advised.
> >
> > Use the SLOPE:
> > SLOPE(known_y's,known_x's)
> >
> > Known_y's is an array or cell range of numeric dependent data points.
> >
> > Known_x's is the set of independent data points.
> >
> >
> > and INTERCEPT formulas
> >
> > INTERCEPT(known_y's,known_x's)
> >
> > Known_y's is the dependent set of observations or data.
> >
> > Known_x's is the independent set of observations or data.
> >
> > to build your equation
> >
> > See excel help for details.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "amirstal" wrote:
> >
> > > Let's say I have 2 values: one at F1 and the other at F8.
> > > By using linear interpolation I can solve for F2, F3, F4, F5, F6 and F7
> > > (the two other values that are needed for the interpolation are not
> > > relevant for my problem).
> > >
> > > Problem:
> > > The two values that I have are not always in F1 and F8.
> > > They now can be at F2 and F7.
> > >
> > > Question:
> > > How can I apply the interpolation without knowing where exactly my two
> > > values appear? I do know for sure that I have two values and that there
> > > are cells in between those two values that are needed to take
> > > interpolated values...
> > >
> > > I hope I am clear.
> > >
> > > Thanks.
> > >
> > >
>
>