Fill the data for unknown points

[AmyM-NirR]

Hi,

Suppose I have a train that's driving away from a train station, and I've measured the speed at a certain distances from the station (in this case 1,5 and 7, but it could be any number of distances).

My goal is to calculate the speed in every running kilometer away from the station. My calculation method is as follows (don't mind the physics error):

1. If I have a speed measure of the point, use the value of the measure
2. else, the speed is equal to: previous_measured_speed+0.1*(current_distance - previous_measured_distance)

I was able to generate a running KM using the formula in the picture below.

As for the speed calculation, I'm trying to create a dynamic formula to generate the calculated speed for all known and unknown points.

The obvious method without using dynamic formula, will be to use XLOOKUP and then for every unknown point calculate manually using absolute references.

However, this method is tedious, as I have to drag the formula manually between every two known points, and there could be a lot more than 3 known points.

I'm looking for a way to generate a dynamic array with the results of the calculation, regardless of the number of the points.

If possible, I would like to have the generation of the distances and the speeds in one formula, but maybe that's too complicated.. 

THANKS!

[#2 · Accepted solution]

@AmyM-NirR 

=BYROW(H3#,LAMBDA(row,IFNA(IF(XLOOKUP(row,D3:D5,E3:E5),XLOOKUP(row,D3:D5,E3:E5)),(row-XLOOKUP(row,D3:D5,D3:D5,,-1))*0.1*(OFFSET(row,1,0)-row)+XLOOKUP(row,D3:D5,E3:E5,,-1))))

You can try this formula for the calculation of speed.