Interpolating Data From a Table

[davyj93]

Hi Guys,

I've recorded engine data and two of the variables recorded were engine speed and coolant temperature. To determine friction torque, the table below needs to be used:

[TABLE="class: outer_border, width: 1377"]
<colgroup><col><col><col span="16"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]Engine Speed[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1100[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]1400[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]1600[/TD]
[TD="align: center"]1800[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]2200[/TD]
[TD="align: center"]2400[/TD]
[TD="align: center"]2600[/TD]
[TD="align: center"]2800[/TD]
[TD="align: center"]3000[/TD]
[/TR]
[TR]
[TD="align: center"]Coolant Temperature[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]-30.04[/TD]
[TD="align: center"][/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]104.4[/TD]
[TD="align: center"]108.9[/TD]
[TD="align: center"]113[/TD]
[TD="align: center"]117.8[/TD]
[TD="align: center"]122.7[/TD]
[TD="align: center"]127.6[/TD]
[TD="align: center"]132.5[/TD]
[TD="align: center"]141[/TD]
[TD="align: center"]148[/TD]
[TD="align: center"]156[/TD]
[TD="align: center"]161.7[/TD]
[TD="align: center"]168[/TD]
[TD="align: center"]176[/TD]
[TD="align: center"]183[/TD]
[/TR]
[TR]
[TD="align: center"]-20.04[/TD]
[TD="align: center"][/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]90.2[/TD]
[TD="align: center"]93.9[/TD]
[TD="align: center"]98.1[/TD]
[TD="align: center"]102.6[/TD]
[TD="align: center"]107.5[/TD]
[TD="align: center"]112[/TD]
[TD="align: center"]117.3[/TD]
[TD="align: center"]125[/TD]
[TD="align: center"]132.3[/TD]
[TD="align: center"]139.2[/TD]
[TD="align: center"]144.9[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]156.2[/TD]
[TD="align: center"]162[/TD]
[/TR]
[TR]
[TD="align: center"]-10.04[/TD]
[TD="align: center"][/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]80.3[/TD]
[TD="align: center"]83.6[/TD]
[TD="align: center"]87.8[/TD]
[TD="align: center"]92[/TD]
[TD="align: center"]96.5[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]107.7[/TD]
[TD="align: center"]115[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"]127.5[/TD]
[TD="align: center"]133.7[/TD]
[TD="align: center"]139.3[/TD]
[TD="align: center"]145[/TD]
[/TR]
[TR]
[TD="align: center"]-0.04[/TD]
[TD="align: center"][/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]66.8[/TD]
[TD="align: center"]69.5[/TD]
[TD="align: center"]72.9[/TD]
[TD="align: center"]76.8[/TD]
[TD="align: center"]80.6[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]92.5[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]106[/TD]
[TD="align: center"]113[/TD]
[TD="align: center"]119.1[/TD]
[TD="align: center"]125.7[/TD]
[TD="align: center"]133[/TD]
[/TR]
[TR]
[TD="align: center"]9.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]45.5[/TD]
[TD="align: center"]54.3[/TD]
[TD="align: center"]58.7[/TD]
[TD="align: center"]60.8[/TD]
[TD="align: center"]62.7[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]72.9[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]92[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]106[/TD]
[TD="align: center"]112.5[/TD]
[TD="align: center"]119[/TD]
[TD="align: center"]125.5[/TD]
[/TR]
[TR]
[TD="align: center"]19.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]43.8[/TD]
[TD="align: center"]52.3[/TD]
[TD="align: center"]56.5[/TD]
[TD="align: center"]58.5[/TD]
[TD="align: center"]60.4[/TD]
[TD="align: center"]63.6[/TD]
[TD="align: center"]65.9[/TD]
[TD="align: center"]69.1[/TD]
[TD="align: center"]72.2[/TD]
[TD="align: center"]80.1[/TD]
[TD="align: center"]87.1[/TD]
[TD="align: center"]95[/TD]
[TD="align: center"]102.1[/TD]
[TD="align: center"]108[/TD]
[TD="align: center"]115[/TD]
[TD="align: center"]120.9[/TD]
[/TR]
[TR]
[TD="align: center"]29.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]42.2[/TD]
[TD="align: center"]50.3[/TD]
[TD="align: center"]54.4[/TD]
[TD="align: center"]56.3[/TD]
[TD="align: center"]58.1[/TD]
[TD="align: center"]61.2[/TD]
[TD="align: center"]63.5[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]69.5[/TD]
[TD="align: center"]77.1[/TD]
[TD="align: center"]83.9[/TD]
[TD="align: center"]91.5[/TD]
[TD="align: center"]98.3[/TD]
[TD="align: center"]104[/TD]
[TD="align: center"]110.5[/TD]
[TD="align: center"]116.4[/TD]
[/TR]
[TR]
[TD="align: center"]39.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]40.5[/TD]
[TD="align: center"]48.4[/TD]
[TD="align: center"]52.3[/TD]
[TD="align: center"]54.1[/TD]
[TD="align: center"]55.9[/TD]
[TD="align: center"]58.8[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]63.9[/TD]
[TD="align: center"]66.7[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]80.6[/TD]
[TD="align: center"]87.9[/TD]
[TD="align: center"]94.4[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]106[/TD]
[TD="align: center"]111.8[/TD]
[/TR]
[TR]
[TD="align: center"]49.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]39.3[/TD]
[TD="align: center"]43.8[/TD]
[TD="align: center"]46.6[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]49.3[/TD]
[TD="align: center"]51.5[/TD]
[TD="align: center"]53.2[/TD]
[TD="align: center"]55.5[/TD]
[TD="align: center"]57.6[/TD]
[TD="align: center"]63.1[/TD]
[TD="align: center"]68.2[/TD]
[TD="align: center"]73.9[/TD]
[TD="align: center"]80.5[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]92[/TD]
[TD="align: center"]100.3[/TD]
[/TR]
[TR]
[TD="align: center"]59.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40.8[/TD]
[TD="align: center"]41.8[/TD]
[TD="align: center"]42.7[/TD]
[TD="align: center"]44.1[/TD]
[TD="align: center"]45.4[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48.5[/TD]
[TD="align: center"]52.1[/TD]
[TD="align: center"]55.8[/TD]
[TD="align: center"]59.8[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]73.2[/TD]
[TD="align: center"]80.9[/TD]
[TD="align: center"]88.7[/TD]
[/TR]
[TR]
[TD="align: center"]69.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40.8[/TD]
[TD="align: center"]41.8[/TD]
[TD="align: center"]42.7[/TD]
[TD="align: center"]44.1[/TD]
[TD="align: center"]45.4[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48.5[/TD]
[TD="align: center"]52.1[/TD]
[TD="align: center"]55.8[/TD]
[TD="align: center"]59.8[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]73.2[/TD]
[TD="align: center"]80.9[/TD]
[TD="align: center"]88.7[/TD]
[/TR]
[TR]
[TD="align: center"]79.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40.8[/TD]
[TD="align: center"]41.8[/TD]
[TD="align: center"]42.7[/TD]
[TD="align: center"]44.1[/TD]
[TD="align: center"]45.4[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48.5[/TD]
[TD="align: center"]52.1[/TD]
[TD="align: center"]55.8[/TD]
[TD="align: center"]59.8[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]73.2[/TD]
[TD="align: center"]80.9[/TD]
[TD="align: center"]88.7[/TD]
[/TR]
[TR]
[TD="align: center"]89.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40.8[/TD]
[TD="align: center"]41.8[/TD]
[TD="align: center"]42.7[/TD]
[TD="align: center"]44.1[/TD]
[TD="align: center"]45.4[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48.5[/TD]
[TD="align: center"]52.1[/TD]
[TD="align: center"]55.8[/TD]
[TD="align: center"]59.8[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]73.2[/TD]
[TD="align: center"]80.9[/TD]
[TD="align: center"]88.7[/TD]
[/TR]
[TR]
[TD="align: center"]99.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40.8[/TD]
[TD="align: center"]41.8[/TD]
[TD="align: center"]42.7[/TD]
[TD="align: center"]44.1[/TD]
[TD="align: center"]45.4[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48.5[/TD]
[TD="align: center"]52.1[/TD]
[TD="align: center"]55.8[/TD]
[TD="align: center"]59.8[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]73.2[/TD]
[TD="align: center"]80.9[/TD]
[TD="align: center"]88.7[/TD]
[/TR]
[TR]
[TD="align: center"]109.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40.8[/TD]
[TD="align: center"]41.8[/TD]
[TD="align: center"]42.7[/TD]
[TD="align: center"]44.1[/TD]
[TD="align: center"]45.4[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48.5[/TD]
[TD="align: center"]52.1[/TD]
[TD="align: center"]55.8[/TD]
[TD="align: center"]59.8[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]73.2[/TD]
[TD="align: center"]80.9[/TD]
[TD="align: center"]88.7[/TD]
[/TR]
[TR]
[TD="align: center"]119.96[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]39.2[/TD]
[TD="align: center"]40.8[/TD]
[TD="align: center"]41.8[/TD]
[TD="align: center"]42.7[/TD]
[TD="align: center"]44.1[/TD]
[TD="align: center"]45.4[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]48.5[/TD]
[TD="align: center"]52.1[/TD]
[TD="align: center"]55.8[/TD]
[TD="align: center"]59.8[/TD]
[TD="align: center"]66.5[/TD]
[TD="align: center"]73.2[/TD]
[TD="align: center"]80.9[/TD]
[TD="align: center"]88.7[/TD]
[/TR]
</tbody>[/TABLE]

The values in the main body of the table are friction torque. I would like to be able to calculate friction torque for a range of engine speeds and coolant temperatures. I think I have to interpolate the table to do this. How do I interpolate the table on Excel?

Below is a small sample of my spreadsheet. I would like a formula to calculate the friction torque by using the values in the engine speed and coolant temperature columns, and then using the table above. Any suggestions?

[TABLE="class: outer_border, width: 426"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Coolant Temperature (°C)[/TD]
[TD="align: center"]Engine Speed (rpm)[/TD]
[TD="align: center"]Friction Torque (Nm)[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30.16[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30.16[/TD]
[TD="align: center"]937[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30.2[/TD]
[TD="align: center"]937.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30.5[/TD]
[TD="align: center"]938[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30.6[/TD]
[TD="align: center"]941[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30.6[/TD]
[TD="align: center"]941[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]941[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]940.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]939[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]938.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]939.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31.9[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"]941.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32.5[/TD]
[TD="align: center"]942.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32.5[/TD]
[TD="align: center"]939[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32.5[/TD]
[TD="align: center"]939.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32.6[/TD]
[TD="align: center"]941.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"]942[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33.4[/TD]
[TD="align: center"]942.5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33.5[/TD]
[TD="align: center"]943[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]35[/TD]
[TD="align: center"]942[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
David

 

[AliGW]

Welcome to the forum!

Firstly, add your expected outcomes to the first five rows of the output table, please.

 

[davyj93]

Here is the table with expected results:

[TABLE="class: outer_border, width: 429"]
<tbody>[TR]
[TD="align: center"]Coolant Temperature (°C)[/TD]
[TD="align: center"]Engine Speed (rpm)[/TD]
[TD="align: center"]Friction Torque (Nm)[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"]53.16184[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"]53.16184[/TD]
[/TR]
[TR]
[TD="align: center"]30.16[/TD]
[TD="align: center"]940[/TD]
[TD="align: center"]53.1292[/TD]
[/TR]
[TR]
[TD="align: center"]30.16[/TD]
[TD="align: center"]937[/TD]
[TD="align: center"]53.06776[/TD]
[/TR]
[TR]
[TD="align: center"]30.16[/TD]
[TD="align: center"]937.5[/TD]
[TD="align: center"]53.06985[/TD]
[/TR]
</tbody>[/TABLE]

I think these values are correct. I used an online calculator from:
Bilinear Interpolation Equation Formula Calculator - Double Interpolator

That link has an equation for the interpolation but I'm not sure how to use it on my spreadsheet.

 

[Martvg]

Bit of a puzzle applying the formula concerning bilinear interpolation with so many INDEX MATCH combinations, but this should be it:

=(INDEX($A$3:$A$18,MATCH(B24,$A$3:$A$18,1)+1)-B24)/(INDEX($A$3:$A$18,MATCH(B24,$A$3:$A$18,1)+1)-INDEX($A$3:$A$18,MATCH(B24,$A$3:$A$18,1)))*((INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)+1)-C24)/(INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)+1)-INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)))*INDEX($C$3:$R$18,MATCH(B24,$A$3:$A$18,1),MATCH(C24,$C$1:$R$1,1))+(C24-INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)))/(INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)+1)-INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)))*INDEX($C$3:$R$18,MATCH(B24,$A$3:$A$18,1),MATCH(C24,$C$1:$R$1,1)+1))+(B24-INDEX($A$3:$A$18,MATCH(B24,$A$3:$A$18,1)))/(INDEX($A$3:$A$18,MATCH(B24,$A$3:$A$18,1)+1)-INDEX($A$3:$A$18,MATCH(B24,$A$3:$A$18,1)))*((INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)+1)-C24)/(INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)+1)-INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)))*INDEX($C$3:$R$18,MATCH(B24,$A$3:$A$18,1)+1,MATCH(C24,$C$1:$R$1,1))+(C24-INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)))/(INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)+1)-INDEX($C$1:$R$1,MATCH(C24,$C$1:$R$1,1)))*INDEX($C$3:$R$18,MATCH(B24,$A$3:$A$18,1)+1,MATCH(C24,$C$1:$R$1,1)+1))

With B24 having 30 (degrees, of the first point you try to calculate), with c24 having 940 (speed of the engine, of the first point you try to calculate), with A3:A18 populated by the Temperatures, with c1:R1 populated by the speed of the engine and with C3:R18 populated by the table.

One small note, using bilinear interpolation your data should be linear, which your data is not. Therefore the results you posted and the results you will get from the formula above will be slightly different from the true answer.

Best regards,
Mart van Gestel

 

[davyj93]

Would a different formula need to be used then if my data isn't linear?

What formula would have to be used?

 

[Martvg]

Well, it is not a must, it depends on how accurate you want to be.

Torque for a temperature of 29.96 at a speed of 1000 should be 54.4 (assuming you measured without any errors).

If I calculate it with the data you have given:

Temperature 29.96 and speed 800 result in 50.3 Torque
Temperature 39.96 and speed 800 result in 48.4 Torque
Temperature 29.96 and speed 1100 result 56.3 in Torque
Temperature 39.96 and speed 1100 result 54.1 in Torque

Using the website or the formula, a temperature of 29.96 and a speed of 1000 will result in a 54.3 Torque (instead of the 54.4 you measured).

Unfortunately, getting an even more accurate answer using a different approach is a bit out of my scope.
But someone else might be able to help you, if getting more accurate answers is a must.

Best regards,
Mart