Help with Linear Interpolation with multiple tables.

[ptc-5241139]

Hi all.

I have an extremely tricky problem that I have no idea how to program into Mathcad as my experiance is limited to say the least.

I have two tables as shown below I have a number of scenarios I need to run to get the correct data.

1. Gc = 0.111 - Therefor can use Table A-2 only.

N = 0.002 - Can use the N≤0.003 side of the table.

M = 0.65 - Have to interpolate between 0.6 and 0.8.

Results required for - C1, C2, C3 and Kb. I have worked these out as C1 = 0.25, C2=7.75, C3=0.6, Kb=8.75

2. Gc = 0.111 - Therefor can use Table A-2 only.

N = 0.004 - Interpolate between the N≤0.003 side and 0.006 ≤ N ≤ 0.024 side of the table.

M = 0.65 - Have to interpolate between 0.6 and 0.8.

Results required for - C1, C2, C3 and Kb. I have worked these out as C1 = 0.242, C2=7.167, C3=0.6, Kb=9.17

3. Gc = 0.111 - Therefor can use Table A-2 only.

N = 0.01 - Can use the 0.006 ≤ N ≤ 0.024 side of the table.

M = 0.65 - Have to interpolate between 0.6 and 0.8.

Results required for - C1, C2, C3 and Kb. I have worked these out as C1 = 0.225, C2=6, C3=0.6, Kb=10

4. Gc = 0.155 - Therefor have to interpolate between Table A-2 and A-3.

N = 0.004 - Interpolate between the N≤0.003 side and 0.006 ≤ N ≤ 0.024 side of table A-2 and A-3.

M = 0.65 - Have to interpolate between 0.6 and 0.8 on table A-2 and A-3

Results required for - C1, C2, C3 and Kb.

I have worked these out as C1 = 0.176, C2=7.07, C3=0.56, Kb=9.00

I have included the excel spread sheet I used to get the results.

Hope someone can help.

Kind Regards

Chris.

Table A-2 Parameters for calculating minimum weight, L10/(2 + M)2, for pipe on clay, Gc = 0.111
Gc = 0.111
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	0.1	9	0.6	10	0.1	7	0.6	10
0.4	0.1	8	0.6	10	0.1	7	0.6	10
0.5	0.1	8	0.6	10	0.1	7	0.6	10
0.6	0.2	8	0.6	10	0.2	6	0.6	10
0.8	0.4	7	0.6	5	0.3	6	0.6	10
1	0.4	7	0.6	5	0.4	6	0.6	10
1.5	0.4	5	0.6	5	0.8	4	0.6	10
2	0.7	3	0.6	5	1.5	0	0.6	10
≥ 4.0	1.4	1	0.6	5	1.5	0	0.6	10
Table A-3 Parameters for calculating minimum weight, L10/(2 + M)2, for pipe on clay, Gc = 0.222
Gc = 0.222
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	0.1	8	0.5	15	0.1	8	0.5	10
0.4	0.1	7	0.5	10	-0.3	8	0.5	10
0.5	0.1	7	0.5	10	-0.1	7	0.5	10
0.6	0.1	7	0.5	10	0	7	0.5	10
0.8	0.1	7	0.5	5	0.1	6	0.5	5
1	0.1	7	0.5	5	0.1	6	0.5	5
1.5	0.1	7	0.5	5	0.5	3	0.5	5
2	0.1	7	0.5	5	0.9	2	0.5	5
4	0.1	7	0.5	5	1.7	0	0.5	5
≥ 10	0.1	7	0.5	5	1.7	0	0.5	5

[Werner_E]

The following scenarios are not defined:

N > 0.024 ?? suggestion: error, or same as for 0.006 ≤ N ≤ 0.024

Gc < 0.111 ?? suggestion: error / linear extrapolate / same as Gc=0.111

Gc > 0.222 ?? suggestion: error / linear extrapolate / same as Gc=0.222

Additionally its not clearly stated what the range for M should be if we had to interpolate/extrapolate for Gc.

If Gc=0.111 we have a vector M of 9 values (up to max. 4). If Gc=0.222 there is one additional elelement >=10 and so we have 10 elements in M.

What, if, for example, Gc=0155 and M=6? Your Excel sheet would wrongly extrapolate even if we where at the first table where all values for M greater than 4 should be treated as 4!

[ptc-5241139]

Hi thanks for your reply and questions.

1. For N>0.024 - The results are not required. I.e a different method is taken so a message "The maximum value for the acceleration parameter N is 0.024 for clay soil. This value is exceeded" would be raised.

2. Gc < 0.111 - linear extrapolate between 0 and 0.111

3. Gc > 0.222 - linear extrapolate up to a value of 2.78 when an error code - ""The analyses are only valid for Gc less than 2.78. This value is exceeded and therefore absolute stability is the recommended design methodology" would be raised.

4. For M=6, in the first table anything over 4 is treated as 4. However in the second table you would have to interpolate between 4 and 10. Likewise if M=12 the value would be treated as 4 in the first table and 10 in the second table.

The sheet I attached to my first post is an excerpt from another sheet (which I have pasted to this reply) I just wanted a guide and then I was going to give it a go for the complete sheet.

Table A-1 Parameters for calculating minimum weight, L10/(2 + M)2, for pipe on clay, Gc = 0.0556
Gc = 0.0556
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	0	9	0.6	10	0.2	5	0.5	15
0.4	0	8	0.6	10	0.2	5	0.5	15
0.5	0.1	7	0.6	10	0.4	4	0.5	15
0.6	0.1	7	0.6	10	0.4	4	0.5	15
0.8	0.1	7	0.6	10	0.7	3	0.5	15
1	0.4	5	0.6	5	0.7	3	0.5	15
1.5	0.4	5	0.6	5	1.1	2	0.5	15
2	0.7	3	0.6	5	1.6	0	0.5	15
≥ 4.0	1.4	1	0.6	5	1.9	0	0.5	15
Table A-2 Parameters for calculating minimum weight, L10/(2 + M)2, for pipe on clay, Gc = 0.111
Gc = 0.111
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	0.1	9	0.6	10	0.1	7	0.6	10
0.4	0.1	8	0.6	10	0.1	7	0.6	10
0.5	0.1	8	0.6	10	0.1	7	0.6	10
0.6	0.2	8	0.6	10	0.2	6	0.6	10
0.8	0.4	7	0.6	5	0.3	6	0.6	10
1	0.4	7	0.6	5	0.4	6	0.6	10
1.5	0.4	5	0.6	5	0.8	4	0.6	10
2	0.7	3	0.6	5	1.5	0	0.6	10
≥ 4.0	1.4	1	0.6	5	1.5	0	0.6	10
Table A-3 Parameters for calculating minimum weight, L10/(2 + M)2, for pipe on clay, Gc = 0.222
Gc = 0.222
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	0.1	8	0.5	15	0.1	8	0.5	10
0.4	0.1	7	0.5	10	-0.3	8	0.5	10
0.5	0.1	7	0.5	10	-0.1	7	0.5	10
0.6	0.1	7	0.5	10	0	7	0.5	10
0.8	0.1	7	0.5	5	0.1	6	0.5	5
1	0.1	7	0.5	5	0.1	6	0.5	5
1.5	0.1	7	0.5	5	0.5	3	0.5	5
2	0.1	7	0.5	5	0.9	2	0.5	5
4	0.1	7	0.5	5	1.7	0	0.5	5
≥ 10	0.1	7	0.5	5	1.7	0	0.5	5
Table A-4 Parameters for calculating minimum weight, L10/(2 + M)2, for pipe on clay, Gc = 0.556
Gc = 0.556
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	1.4	3	0.5	15	0	8	0.5	10
0.4	0.5	6	0.5	5	0.3	6	0.5	5
0.5	0.5	6	0.5	5	0.3	6	0.5	5
0.6	0.5	6	0.5	5	0.3	6	0.5	5
0.8	1.1	4	0.5	5	0.4	7	0.5	5
1	1.3	4	0.5	10	0.4	7	0.5	5
1.5	1.2	7	0.5	10	0.8	6	0.5	10
2	1.2	7	0.5	10	0.8	6	0.5	10
4	1.2	7	0.5	10	0.8	6	0.5	10
≥ 10	1.4	6	0.5	10	0.8	6	0.5	10
Table A-5 Parameters for calculating minimum weight, L10/(2 + M)2, for pipe on clay, Gc = 1.11
Gc = 1.11
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	2.1	1	0.5	15	1.4	4	0.5	15
0.4	2.4	2	0.5	15	1.1	7	0.5	15
0.5	2.4	2	0.5	15	1.5	5	0.5	15
0.6	1.9	6	0.5	15	1.6	5	0.5	15
0.8	2.2	8	0.5	15	1.9	6	0.5	15
1	2.2	8	0.5	15	2.2	6	0.5	15
≥ 1.5	2.4	8	0.5	15	2	8	0.5	15
Table A-6 Parameters for calculating minimum weight,
L10/(2 + M)2, for pipe on clay, Gc = 2.78
Gc = 2.78
	N ≤ 0.003				0.006 ≤ N ≤ 0.024
M	C1	C2	C3	Kb	C1	C2	C3	Kb
≤ 0.2	3.4	1	0.5	20	2.7	3	0.5	20
0.4	3.4	1	0.5	20	2.4	4	0.5	20
0.5	3	4	0.5	20	2.2	7	0.5	20
0.6	3.2	6	0.5	15	1.9	9	0.5	15
0.8	2.4	12	0.5	15	1.9	12	0.5	15
1	2.3	12	0.5	15	1.5	14	0.5	15
1.5	2.3	12	0.5	15	1.5	14	0.5	15
2	2.3	12	0.5	15	1.5	14	0.5	15
≥ 4.0	2.3	12	0.5	15	1.5	14	0.5	15

Hope this makes sense.

Thanks for looking at this problem.

Kind regards

Chris.

[Werner_E]

2. Gc < 0.111 - linear extrapolate between 0 and 0.111

?? Extrapolation wrt the values for G=0.0556 and 0.111, right

4. For M=6, in the first table anything over 4 is treated as 4. However in the second table you would have to interpolate between 4 and 10.

Thats clear, but the question was meant what should happen if we would have to interpolate between the two tables. Guess it would be the best to make all vector M's the same length duplicating the last row as often as necessary.

It would be necessary making all the tables the same size anyway to do a 3D interpolation.

So the data structure for every data (C1, C2, C3 and Kb) would be a 6*12*4 table.

6 ... Gc, 12...M (we need an extra value to copy for <= and >=), 4..N

Unfortunately Mathcad does not allow 3D matrices and so we will have to construct our own datastructure using nested arrays.

I have an older sheet using that kind of 3D interpolation lying around somewhere which I have yet to find. It should be possible to adopt it to your problem I guess. Will take some while, though.

[Werner_E]

OK, found the sheet.

As you can see below - the 3D interpolation routine isn't very difficult and large.

The most cumbersome part was the extraction of the data from the Excel sheet and casting it in a format suitable for further calculation. The Excel table may be optimized for manual lookup but sure not for automatic computation.

Nonetheless the attached sheet seems to do what you was asking for.

[ptc-5241139]

Hi there

Thanks again you are a great help. I will have a go and see how I get on.

Cheers, give yourself a big pat on the back.

[Werner_E]

Just realized that I forgot to attach the worksheet - the screenshot won't help that much, I fear.

Here it is.

[ptc-5241139]

Hi,

Just got your reply.

Thank you so much. I am learning a lot from you.

Where are you based, and what is your background?

Take care

Chris.

[ptc-5241139]

Hi there Werner Exinger.

Have you attached the sheet or am I missing something? There is only an image on your reply.

Thanks

Chris.

[Werner_E · Accepted solution]

Have you attached the sheet or am I missing something? There is only an image on your reply.

No, you don't missed anything (apart from the worksheet;-). Unfortunately its quite common for me to forget to add the attachments. In the meantime you should have it (http://communities.ptc.com/message/219752#219752)