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

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)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Christopher Glaeser" wrote:

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

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

Perfect!

Best,
Christopher

On Feb 12, 12:34 pm, "Christopher Glaeser" wrote:
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 -----

On Feb 12, 12:34*pm, "Christopher Glaeser" wrote:
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

"joeu2004" wrote:
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.