EXCEL TECHNIQUES

DIGITIZING DATA using CHARTS and SCREENSHOTS – Step 7

An interpolator function can be added to fully apply the data. The Excel application has User Function capability with its VBA macro feature to make an interpolator function. The interpolator function is added to this workbook and called from the spreadsheet.

7. Check data and interpolate between points as follows:

Procedure Steps: 7.1 Use Tools > Macro > Visual Basic Editor to add the following code to the spreadsheet: (Insert a new module and copy the code into it)

Function Intp(dataInx, dataIny, x) As Double

Dim datax As Variant 'x data

Dim datay As Variant 'y data

Dim n As Integer

Dim ilow As Integer

Dim ihigh As Integer

Dim imid As Integer

datax = dataInx.Value

datay = dataIny.Value

n = UBound(datay, 1)

If n = 1 Then

n = UBound(datay, 2)

ReDim datax(n, 1)

ReDim datay(n, 1)

For c = 1 To n

datax(c, 1) = dataInx(1, c)

datay(c, 1) = dataIny(1, c)

Next c

Else

ReDim datax(n, 1)

ReDim datay(n, 1)

For c = 1 To n

datax(c, 1) = dataInx(c, 1)

datay(c, 1) = dataIny(c, 1)

Next c

End If

If x > datax(n, 1) Then 'limit to last element in table

Intp = datay(n, 1)

End If

If x < datax(1, 1) Then ' limit to first element in table

Intp = datay(1, 1)

End If

'loop to find bracket

ilow = 1

ihigh = n

imid = n / 2

Do While ihigh - ilow > 1

If x < datax(imid, 1) Then

ihigh = imid

ElseIf x > datax(imid, 1) Then

ilow = imid

ElseIf x = datax(imid, 1) Then

Intp = datay(imid, 1)

Exit Function

End If

imid = (ihigh + ilow) / 2

Loop

'linear interpolation

Intp = datay(ilow, 1) + _

((datay(ihigh, 1) - datay(ilow, 1)) / _

(datax(ihigh, 1) - datax(ilow, 1))) * (x - datax(ilow, 1))

End Function

Procedure Steps: Close the VBA interface and return to the spreadsheet to add the interpolator function Add the user input and the interpolated value cells…

Procedure Steps: … Insert the interpolator function in the interpolated value cell:

Procedure Steps: … Insert the interpolator function from the "User Defined" drop-down list:

Procedure Steps: … Assign input ranges for the x-data and y-data and user input value and Change the value in the "date" field to see the resulting interpolated value.

Reference Microsoft Excel Help for detailed instructions for each step.