WebbBLOG
Michael Webb's Weblog

Fun with OpenOffice Interpolation

Essentially, interpolation is uhm, "finding" a value between two known values. Consider acquiring some samples at a customer site of users and bytes transferred, and then having to "guesstimate" some in-between values. You will try to get some numbers from the minimum to the maximum, but you'll never get them all.

So go through steps 1 through 4 of "What's in the middle," read the paragraph explaining the slope of a function -- and I'll take over from there.

1. Enter 6.5 in A1

2 Enter “ =INDEX($F$1:$G$20;(MATCH($A$1;$F$1:$F$20;1))+1;2) ”
where “ INDEX($F$1:$G$20” is the index search range
where “(MATCH($A$1;$F$1:$F$20;1))+1” matches the 'next largest' value of 6.5, which is row 7
-- (this function replaces the “ROW” value in the INDEX Formula)
and where “2” is the second column, which contains the value to be retrieved (61) in A2.

3. Enter “ =LOOKUP($A$1;$F$1:$F$20;$G$1:$G$20) “
where the 'next lowest' value of (row) 6.5 is retrieved (47) in A3.

4. Enter “ =INDEX($F$1:$G$20;(MATCH($A$1;$F$1:$F$20;1))+1;1) ”
where “ INDEX($F$1:$G$20” is the index search range
where “(MATCH($A$1;$F$1:$F$20;1))+1” matches the 'next largest' value of 6.5, which is row 7
and where “1” is the first column, which contains the value to be retrieved (7) in A4.

5. Enter “ =INDEX($F$1:$G$20;MATCH($A$1;$F$1:$F$20);1) ”
where “ INDEX($F$1:$G$20” is the index search range
where “ MATCH($A$1;$F$1:$F$20)” matches the 'next lowest' value of 6.5, which is row 6
and where “1” is the first column, which contains the value to be retrieved (6) in A5.

6. Enter “ =$A$1-INDEX($F$1:$G$20;MATCH($A$1;$F$1:$F$20);1) ”
where “$A$1” represents the value 6.5 (minus) INDEX($F$1:$G$20;MATCH($A$1;$F$1:$F$20);1)
where “ INDEX($F$1:$G$20” is the index search range
where “ MATCH($A$1;$F$1:$F$20)” matches the 'next lowest' value of 6.5, which is row 6
and where “1” is the first column, which contains the value to be retrieved (6)
(and 6.5 – 6 = 0.5, which will be the result) in A6.

7. Enter “ =LOOKUP($A$1;$F$1:$F$20;$G$1:$G$20) “
which is needed for the equation in A7.

7. Rather than paste in all the values into a single cell, why not refer to the cells containing the lookups.
Enter “=(((A2-A3)/(A4-A5))*(A6))+A7”
in A8 to represent the longer formula --and the answer is 54.

You can also verify the lookups changes in cells A2 – A6 as you change the value in A1.

You too, now can use interpolation in your consulting skills.

( Jun 29 2005, 03:22:55 PM CDT ) Permalink Comments [1]