interpolation within subranges

[Christopher Glaeser]

For a given running event, a table of points are associated with times. For
example:

Time Points
----------------
2:16 150
2:00 200
1:48 250
1:38 300
1:29 350
....
0:50 1200

What is the easiest approach to calculate the points for a time that falls
between two times in the table? A simple staightline interpolation within a
subrange is more than adequate. In other words, a performance of 2:08 falls
in the middle of the range 2:16 to 2:00, so the points would be in the
middle of 150 to 200, or 175 points.

Best,
Christopher

 

[Shane Devenshire]

Hi,

Suppose you put you times in E1:E100 and I'll call it T, and you scores in
F1:F100. And you enter the result in A4 then the following formula will
calculate the points.

=TREND(OFFSET(INDIRECT("F"&MATCH(A4,T,-1)),,,2),OFFSET(INDIRECT("E"&MATCH(A4,T,-1)),,,2),A4)

 

[Christopher Glaeser]

=TREND(OFFSET(INDIRECT("F"&MATCH(A4,T,-1)),,,2),OFFSET(INDIRECT("E"&MATCH(A4,T,-1)),,,2),A4)

Perfect!

Best,
Christopher

 

[joeu2004]

What is the easiest approach to calculate the
points for a time that falls between two times
in the table? A simple staightline interpolation
within a subrange is more than adequate.

"Easiest" is a subjective term. Perhaps a TREND formula would be the
easiest expression to write.

Suppose the first two rows of your data below are in A3:B4. To
interpolate "points" for 2:08, put 2:08 into a cell (C1 here, although
the more natural place is in a new row between A3 and A4) and write:

=trend(B3:B4,A3:A4,C1)

Caveat emptor: Note that TREND and related functions use the slope
and intercept of a linear regression line that best fits the data. It
always works as you wish for 2 data points. But it might not give the
desired result for 3 or more data points, depending on what you
desire.


----- original posting -----

 

[joeu2004]

Suppose the first two rows of your data below are in A3:B4. To
interpolate "points" for 2:08, put 2:08 into a cell (C1 here, although
the more natural place is in a new row between A3 and A4)

No: putting in a new row between A3 and A4 would cause problems.

and write:
=trend(B3:B4,A3:A4,C1)

Disregard this. I did not see Shane's superior response in Google Groups.
In fact, I do not even see my response there yet. Google Groups is having
problems today.