Linear Interpolation of Data

[bkalma]

I am trying to write a formula which will linearly interpolate random sets of data in a table. For example, when used on the following table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[/TR]
</tbody>[/TABLE]

The output (in a separate table) should be:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[/TR]
</tbody>[/TABLE]

The actual range of the source data is D23:AC46. The data points it contains will not have a constant value or position so ideally the formula should cope with any configuration of data.

Any help would be greatly appreciated.

 

[sdruley]

Some derivation of Rolle's Theorem could be converted into a formula for each cell of the output matrix. The formula must be able to reference the two points in the artesian system in which the interpolation is made. In my mind, your matrix should be square and have the bold numbers across the top and down the left side. The integrity of the output grid could then be graphically verified by RSM

 

[Gerald Higgins]

Is it CERTAIN that any given intermediate values will be correctly located on the interpolation line between the two extremes ?

For example, is it POSSIBLE that you could start with this data set ?

0...1...2...3...4
0...-...9...10..-

i.e. 9 is not on the linear interpolation between 0 and 10.

 

[bkalma]

Yes that is possible. I should have also mentioned that the values will not necessarily be in ascending order, nor will the rate of change for each variable be the same in each row. Essentially treat it as if all values are random and that the interpolation is to occur between each pair of data points.

 

[XOR LX]

Hi,

Why are there blanks in your table of expected results?

Regards

 

[Gerald Higgins]

Yes that is possible. I should have also mentioned that the values will not necessarily be in ascending order, nor will the rate of change for each variable be the same in each row. Essentially treat it as if all values are random and that the interpolation is to occur between each pair of data points.

Whoa ! It seems like there are many new requirements creeping in.

It seems like the sample of data you posted in your OP may not have been representative of all these new situations.

Please can you post a new sample of data showing all likely scenarios, with what the results should be in each case.

 

[bkalma]

XOR LX, there are blanks because the interpolation requires 2 data points. For example, there is no number in column "0", row 1 as the first data entry for that row is in column "1". There is no lower value (before 5) with which an interpolation between 2 points can be calculated.

Gerald Higgins, yes I appear to have omitted some important information. This is an example of what the data might look like:

[TABLE="width: 1768"]
<colgroup><col span="26"></colgroup><tbody>[TR]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]0.718[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.685[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.884[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.628[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.310[/TD]
[TD]2.388[/TD]
[TD] [/TD]
[TD]1.881[/TD]
[TD]1.419[/TD]
[TD] [/TD]
[TD]1.522[/TD]
[TD]0.738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.568[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.270[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.357[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.603[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.695[/TD]
[TD] [/TD]
[TD]2.101[/TD]
[TD]1.481[/TD]
[TD] [/TD]
[TD]1.409[/TD]
[TD]0.605[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.487[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.389[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.799[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.845[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.691[/TD]
[TD] [/TD]
[TD]0.789[/TD]
[TD]1.363[/TD]
[TD] [/TD]
[TD]1.030[/TD]
[TD]0.625[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.467[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.783[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.895[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.915[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.649[/TD]
[TD] [/TD]
[TD]0.979[/TD]
[TD]1.712[/TD]
[TD] [/TD]
[TD]0.774[/TD]
[TD]0.349[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.505[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.296[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.681[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.578[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.225[/TD]
[TD] [/TD]
[TD]0.630[/TD]
[TD]1.430[/TD]
[TD] [/TD]
[TD]0.718[/TD]
[TD]0.574[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.332[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.870[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.034[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.004[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.737[/TD]
[TD] [/TD]
[TD]0.794[/TD]
[TD]0.984[/TD]
[TD] [/TD]
[TD]0.261[/TD]
[TD]1.302[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.525[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.059[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.156[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.561[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.830[/TD]
[TD]4.120[/TD]
[TD] [/TD]
[TD]5.867[/TD]
[TD]2.655[/TD]
[TD] [/TD]
[TD]2.885[/TD]
[TD]0.625[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.536[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.140[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7.651[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.898[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.038[/TD]
[TD] [/TD]
[TD]5.729[/TD]
[TD]3.992[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.420[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.537[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.012[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.155[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.887[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.625[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.610[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.510[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.283[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.335[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.962[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.623[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.258[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.796[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.466[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.830[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.572[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.527[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.804[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.147[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.861[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.656[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.337[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.290[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.327[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.912[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.565[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.475[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.690[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.767[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.050[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.230[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.528[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.353[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.271[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.824[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.035[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.500[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.742[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.542[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.962[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.440[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.728[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.494[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.035[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.942[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.024[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.097[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.358[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.527[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.168[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.722[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.025[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.501[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.655[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.920[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.597[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.820[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.560[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6.318[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7.241[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.360[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.930[/TD]
[TD] [/TD]
[TD]4.545[/TD]
[TD]4.638[/TD]
[TD] [/TD]
[TD]2.434[/TD]
[TD]1.097[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.610[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.709[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7.533[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.576[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.779[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.587[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.632[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.975[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.176[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.650[/TD]
[TD]3.664[/TD]
[TD] [/TD]
[TD]2.690[/TD]
[TD]3.372[/TD]
[TD] [/TD]
[TD]2.050[/TD]
[TD]0.943[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.616[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.212[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.551[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.680[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.578[/TD]
[TD] [/TD]
[TD]2.424[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1.086[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0.510[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.151[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5.263[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.623[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.214[/TD]
[TD] [/TD]
[TD]2.106[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.871[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[bkalma]

So I came up with a fairly complicated solution for anyone that's interested.

Beginning with the following in the range B1:G6 where the first row contains the time points and the following rows are data points (separate experiment in each row).

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65, width: 64"]2[/TD]
[TD="class: xl65, width: 64"]3[/TD]
[TD="class: xl65, width: 64"]4[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[/TR]
[TR]
[TD="class: xl66"]7.144[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]4.140[/TD]
[TD="class: xl66"]8.796[/TD]
[/TR]
[TR]
[TD="class: xl66"]9.971[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]4.181[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]1.318[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"]9.122[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]1.476[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]8.210[/TD]
[/TR]
[TR]
[TD="class: xl66"]2.658[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]9.726[/TD]
[/TR]
[TR]
[TD="class: xl66"]3.408[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]1.673[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]3.045[/TD]
[/TR]
</tbody>[/TABLE]

Create another table of equal size, but omitting the 1st row containing the time points (0 1 2 3 4 5), in range B8:G12 and use the following in cell A8 (CTRL+SHIFT+ENTER):

=IF(COLUMNS($B8:B8)>COUNT($B2:$G2),"",INDEX($B2:$G2,SMALL(IF($B2:$G2<>"",COLUMN($B2:$G2)-COLUMN($B2)+1),COLUMNS($B2:B2))))

Drag this across the entire table to give:

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64"]7.144[/TD]
[TD="class: xl65, width: 64"]4.140[/TD]
[TD="class: xl65, width: 64"]8.796[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]9.971[/TD]
[TD="class: xl65"]4.181[/TD]
[TD="class: xl65"]1.318[/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]9.122[/TD]
[TD="class: xl65"]1.476[/TD]
[TD="class: xl65"]8.210[/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]2.658[/TD]
[TD="class: xl65"]9.726[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]3.408[/TD]
[TD="class: xl65"]1.673[/TD]
[TD="class: xl65"]3.045[/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This has essentially 'compressed the data', removing any blank spaces.

Now create another table in the range B14:G18 and use the following in cell A14 (CTRL+SHIFT+ENTER):

=IFERROR(IF(COLUMNS($B14:B14)>COUNT($B2:$G2),NA(),INDEX($B$1:G$1,SMALL(IF($B2:$G2<>"",COLUMN($B2:$G2)-COLUMN($B2)+1),COLUMNS($B2:B2)))),"")

Drag this across the entire table to give:

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64"]0[/TD]
[TD="class: xl65, width: 64"]4[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

This has compressed the time points correlating to each data point (eg. the data in the first row was taken at times 0, 4 and 5).

Finally create another table, again of equal size (including the time points row in B20:G20), in the range B20:G25 and use the following in cell B21 (JUST ENTER):

=IFERROR(IF(B2="",(((INDEX($B8:$D8,1,COUNT(A2:$B2)+1))-(INDEX($B8:$D8,1,COUNT(A2:$B2))))/((INDEX($B14:$D14,1,COUNT(A2:$B2)+1))-(INDEX($B14:$D14,1,COUNT(A2:$B2)))))*(B$20-(INDEX($B14:$D14,1,COUNT(A2:$B2))))+INDEX($B8:$D8,1,COUNT(A2:$B2)),B2),"")

dragging across the entire table to give:

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64"]0[/TD]
[TD="class: xl65, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]2[/TD]
[TD="class: xl65, width: 64"]3[/TD]
[TD="class: xl65, width: 64"]4[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[/TR]
[TR]
[TD="class: xl66"]7.144[/TD]
[TD="class: xl66"]6.393[/TD]
[TD="class: xl66"]5.642[/TD]
[TD="class: xl66"]4.891[/TD]
[TD="class: xl66"]4.140[/TD]
[TD="class: xl66"]8.796[/TD]
[/TR]
[TR]
[TD="class: xl66"]9.971[/TD]
[TD="class: xl66"]7.076[/TD]
[TD="class: xl66"]4.181[/TD]
[TD="class: xl66"]2.749[/TD]
[TD="class: xl66"]1.318[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"]9.122[/TD]
[TD="class: xl66"]5.299[/TD]
[TD="class: xl66"]1.476[/TD]
[TD="class: xl66"]4.843[/TD]
[TD="class: xl66"]8.210[/TD]
[/TR]
[TR]
[TD="class: xl66"]2.658[/TD]
[TD="class: xl66"]4.072[/TD]
[TD="class: xl66"]5.485[/TD]
[TD="class: xl66"]6.899[/TD]
[TD="class: xl66"]8.312[/TD]
[TD="class: xl66"]9.726[/TD]
[/TR]
[TR]
[TD="class: xl66"]3.408[/TD]
[TD="class: xl66"]2.830[/TD]
[TD="class: xl66"]2.251[/TD]
[TD="class: xl66"]1.673[/TD]
[TD="class: xl66"]2.359[/TD]
[TD="class: xl66"]3.045[/TD]
[/TR]
</tbody>[/TABLE]

It works, though i'm sure there's probably an easier way.

 

[sdruley]

bkalma,

This is good work. Another way of confirming the randomness of the surface you have created is by using excel to plot a surface chart. Put 0, 1, 2, 3, 4, 5 down the left side. Select the entire square and insert surface plot. A picture always adds value to your work.