Deriving continuous Data from Connected XY scatter line

**Blofeld** · 11-26-2008, 07:05 AM

Hi,

I am trying to find a way to derive/interpolate the continuous line data that excel uses to connect the points on an XY scatter chart.

Essentially my scatter chart represents a weighted histogram with the X axis representing 0-100%, with each point (and by extension the line connecting them) representing the portion [of 100%] at a particular revenue level, on the Y axis (attached below).

I need to find a way to obtain this 'connected line' data in continuous format for, say, 1% intervals…this data is then to be further interrogated in excel. Does anyone know a way to obtain/interpolate this data?

Thanks in advance,

Blofeld

**Andy Pope** · 11-26-2008, 07:41 AM

You can calculate the new Y with formula. See attached.

Although I have a feeling that there is a neater way of calculating this.

**Blofeld** · 11-26-2008, 10:28 AM

Hi Andy, thanks for your help...

Someone on another forum suggested using the Trend function to derive the missing data (See the sheet attached).

This was the suggested function to fill in the blanks:

=TREND(OFFSET(A$2,MATCH(D2,B$2:B$26,-1)-1,,2),OFFSET(B$2,MATCH(D2,B$2:B$26,-1)-1,,2),D2)

However, at first glance this seems to work, but when I plot the new line chart, you can see I've somehow managed to arrive at an mirrored version of the original XY plot.

My gut feeling is that this is a school boy error, Do you think I should be after the X values instead?

Rgds

Blofeld

**shg** · 11-26-2008, 10:42 AM

If you don't mind using a UDF:

Code:

Function Linterp(ByRef Tbl As Range, ByRef dX As Double) As Variant
    ' shg 06 Jun 1997

    ' Linear interpolator / extrapolator
    ' Tbl is a two-column range containing known x, known y, sorted ascending

    Dim i       As Long     ' index to Tbl
    Dim nRow    As Long     ' rows in Tbl
    Dim dXAbv   As Double   ' Tbl value above dX
    Dim dXBlo   As Double   ' Tbl values below dX
    Dim dRF     As Double   ' row fraction

    nRow = Tbl.Rows.Count
    If nRow < 2 Or Tbl.Columns.Count <> 2 Then
        Linterp = "Table must have >= 2 rows, exactly two columns"
        Exit Function    '-------------------------------------------------------->
    End If

    If dX < Tbl(1, 1).Value Then    ' dX < xmin, extrapolate first two entries
        i = 1
    Else
        i = WorksheetFunction.Match(dX, WorksheetFunction.Index(Tbl, 0, 1), 1)
        If dX = Tbl(i, 1).Value Then    ' dX is exact from table
            Linterp = Tbl(i, 2)
            Exit Function    '---------------------------------------------------->
        ElseIf i = nRow Then   ' dX > xmax, extrapolate last two entries
            i = nRow - 1
            'Else
            ' dX lies between two rows, so interpolate entries i, i+1
            ' which is what happens by default
        End If
    End If

    dXAbv = Tbl(i, 1).Value
    dXBlo = Tbl(i + 1, 1).Value
    dRF = (dX - dXAbv) / (dXBlo - dXAbv)    ' row fraction

    Linterp = Tbl(i, 2).Value * (1 - dRF) + Tbl(i + 1, 2).Value * dRF
End Function

In E1 and copy down,

=LInterp($A$2:$B$26, C1)

Make both charts scatterplots instead of line graphs ...