This may not be what you want -- it uses VBA.
Assume that one curve is in columns B and C, the other curve is in E and F.
Also assume that the curves start in row 2. The x-values are B and E, the
y-vlaues in C and F. Also assume that B and E are in increasing order.
Try the following macro (but save your sheet first, it will insert rows)
Option Explicit
Public LastRow As Integer
Sub MatchupCurves()
Dim rIndex As Integer
Dim MoreData As Boolean
MoreData = True
rIndex = 2
While MoreData
If Cells(rIndex, 2) Cells(rIndex, 5) And Cells(rIndex, 5) < "" Then
Range(Cells(rIndex, 2), Cells(rIndex, 3)).Insert shift:=xlDown
Cells(rIndex, 2) = Cells(rIndex, 5)
End If
If Cells(rIndex, 5) Cells(rIndex, 2) And Cells(rIndex, 2) < "" Then
Range(Cells(rIndex, 5), Cells(rIndex, 6)).Insert shift:=xlDown
Cells(rIndex, 5) = Cells(rIndex, 2)
End If
rIndex = rIndex + 1
If Cells(rIndex, 2) = "" And Cells(rIndex, 5) = "" Then MoreData = False
LastRow = rIndex - 1
Wend
End Sub
Sub Interpolate()
Dim mColumn As Integer
Dim mRow As Integer
Dim i As Integer
Dim temp As Double
For mColumn = 2 To 5 Step 3
For mRow = 2 To LastRow
If Cells(mRow, mColumn) = "" Then Exit For
If Cells(mRow, mColumn + 1) = "" Then
i = mRow + 1
While Cells(i, mColumn + 1) = ""
i = i + 1
Wend
temp = Cells(mRow, mColumn) - Cells(mRow - 1, mColumn)
temp = temp / (Cells(i, mColumn) - Cells(mRow - 1, mColumn))
Cells(mRow, mColumn + 1) = temp * (Cells(i, mColumn + 1) -
Cells(mRow - 1, mColumn + 1)) + Cells(mRow - 1, mColumn + 1)
End If
Next mRow
Next mColumn
End Sub
Run MatchupCurves and then run Interpolate. This won't do the adding, I'm
guessing you can handle that. This also will not do any extrapolating. That
is, if the endpoints do not coincide there's nothing to interpolate with.
I tested this on a few examples, but not exhuastively. Good Luck.
Art
|