hi, need help in how to make interpolation formula based on 2 variable?

AJIESPEDAS · 2024-05-26T22:20:14-0400

hi experts!

i come to a problem again with excel and in need of your expertise to solve my problem.
in given 2 variable for example

12750 lbs with a temperature 31
i need a interpolation that can derive an answer based on image that i give?
is it possible? i have done a single simple interpolation and thats bout it, but this new thing i think it can be done?not so sure..

regards,

ajiespedas

StephenCrump · 2024-05-27T02:02:00-0400

You can do a 2-way linear interpolation like this:

A

B

C

D

E

F

G

H

I

J

K

L

1

Weight

Temp

-5

0

5

10

15

NoRows

4

2

15,000

A

1

2

3

4

3

B

5

6

7

8

Interpolate

Index

Fraction

4

C

9

10

11

12

Measure

B

1

5

D

13

14

15

16

Weight

13,200

13

0.6

6

14,500

A

17

18

19

20

Temp

4

2

0.8

7

B

21

22

23

24

Value

64.4

8

C

25

26

27

28

9

D

29

30

31

32

10

14,000

A

33

34

35

36

11

B

37

38

39

40

12

C

41

42

43

44

13

D

45

46

47

48

14

13,500

A

49

50

51

52

15

B

53

54

55

56

16

C

57

58

59

60

17

D

61

62

63

64

18

13,000

A

65

66

67

68

19

B

69

70

71

72

20

C

73

74

75

76

21

D

77

78

79

80

22

12,500

A

23

B

24

C

25

D

Sheet6

Cell Formulas
Range	Formula
K4	K4	=MATCH(J4,B2:B5,)-1
K5	K5	=MATCH(J5,Weight,-1)
L5	L5	=(J5-INDEX(Weight,K5))/(INDEX(Weight,K5+NoRows)-INDEX(Weight,K5))
K6	K6	=MATCH(J6,Temp,1)
L6	L6	=(J6-INDEX(Temp,K6))/(INDEX(Temp,K6+1)-INDEX(Temp,K6))
J7	J7	=(INDEX(MyTable,K4+K5,K6)(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6)L5)(1-L6)+(INDEX(MyTable,K4+K5,K6+1)(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6+1)L5)L6

Named Ranges
Name	Refers To	Cells
MyTable	=Sheet6!$C$2:$G$25	J7
NoRows	=Sheet6!$J$1	L5, J7
Temp	=Sheet6!$C$1:$G$1	K6:L6
Weight	=Sheet6!$A$2:$A$22	K5:L5

You could build all this into one formula, but it would be a doozy! (Even if you have the latest version of Excel to use the LET function).

AJIESPEDAS · 2024-05-27T06:00:43-0400

StephenCrump said:
You can do a 2-way linear interpolation like this:

A B C D E F G H I J K L
1 Weight Temp -5 0 5 10 15 NoRows 4
2 15,000 A 1 2 3 4
3 B 5 6 7 8 Interpolate Index Fraction
4 C 9 10 11 12 Measure B 1
5 D 13 14 15 16 Weight 13,200 13 0.6
6 14,500 A 17 18 19 20 Temp 4 2 0.8
7 B 21 22 23 24 Value 64.4
8 C 25 26 27 28
9 D 29 30 31 32
10 14,000 A 33 34 35 36
11 B 37 38 39 40
12 C 41 42 43 44
13 D 45 46 47 48
14 13,500 A 49 50 51 52
15 B 53 54 55 56
16 C 57 58 59 60
17 D 61 62 63 64
18 13,000 A 65 66 67 68
19 B 69 70 71 72
20 C 73 74 75 76
21 D 77 78 79 80
22 12,500 A
23 B
24 C
25 D
Sheet6
Cell Formulas
Range Formula
K4 K4 =MATCH(J4,B2:B5,)-1
K5 K5 =MATCH(J5,Weight,-1)
L5 L5 =(J5-INDEX(Weight,K5))/(INDEX(Weight,K5+NoRows)-INDEX(Weight,K5))
K6 K6 =MATCH(J6,Temp,1)
L6 L6 =(J6-INDEX(Temp,K6))/(INDEX(Temp,K6+1)-INDEX(Temp,K6))
J7 J7 =(INDEX(MyTable,K4+K5,K6)*(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6)*L5)*(1-L6)+(INDEX(MyTable,K4+K5,K6+1)*(1-L5)+INDEX(MyTable,K4+K5+NoRows,K6+1)*L5)*L6
Named Ranges
Name Refers To Cells
MyTable =Sheet6!$C$2:$G$25 J7
NoRows =Sheet6!$J$1 L5, J7
Temp =Sheet6!$C$1:$G$1 K6:L6
Weight =Sheet6!$A$2:$A$22 K5:L5

You could build all this into one formula, but it would be a doozy! (Even if you have the latest version of Excel to use the LET function).

is there any way i can copy this and have a go at my excel? ill try to manipulate it into my excel..
i have done a simple interpolation, but yours is far complicated..

btw this is my excel which i want to interpolate

toff data test.xlsx

Shared with Dropbox

www.dropbox.com

if you can open it.

StephenCrump · 2024-05-27T06:15:40-0400

AJIESPEDAS said:
is there any way i can copy this and have a go at my excel?

Click here, and you can paste the layout and formulae to cell A1 in a blank workbook:

You'll need to create the range names MyTable, NoRows, Temp and Weight

hi, need help in how to make interpolation formula based on 2 variable?

AJIESPEDAS

New Member

Attachments

Excel Facts

StephenCrump

MrExcel MVP

AJIESPEDAS

New Member

toff data test.xlsx

StephenCrump

MrExcel MVP

Similar threads

Forum statistics

Share this page

hi, need help in how to make interpolation formula based on 2 variable?

AJIESPEDAS

New Member

Attachments

Excel Facts

StephenCrump

MrExcel MVP

AJIESPEDAS

New Member

toff data test.xlsx

StephenCrump

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock