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:

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

<colgroup><col><col><col span="16"></colgroup><tbody>
</tbody>

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?

Coolant Temperature (°C)	Engine Speed (rpm)	Friction Torque (Nm)
30	940
30	940
30.16	940
30.16	937
30.2	937.5
30.5	938
30.6	941
30.6	941
31	941
31	940.5
31	939
31	938.5
31	939.5
31.9	940
32	941.5
32.5	942.5
32.5	939
32.5	939.5
32.6	941.5
33	942
33.4	942.5
33.5	943
35	942

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

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:

Coolant Temperature (°C)	Engine Speed (rpm)	Friction Torque (Nm)
30	940	53.16184
30	940	53.16184
30.16	940	53.1292
30.16	937	53.06776
30.16	937.5	53.06985

<tbody>
</tbody>

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