Interpolation in Excel

[Ruben40870]

Hi All,

I am hoping someone can help me with an issue I have been having. I am trying to add an Interpolation sum in one of my spreadsheets but I just can't figure out how. Below is the sum:

I really hope someone can help.

Thanks in advance!

 

[JamesCanale]

I'm not sure I understand your formula. It looks like it should be (0.880909-0) on the bottom right.

MrExcelPlayground2.xlsm
	B	C
4	0	0.74
5	0.880909	0.810473
6	1	0.82
Sheet30

Cell Formulas
Range	Formula
C5	C5	=(C6-C4)/(B6-B4)*(B5-B4)+C4

 

[Ruben40870]

Thanks for the reply.

Yes, the formula is a bit weird and that's why I'm struggling but I know it's right, even confirmed it with Microsoft Maths.

I'm starting to think it's not exactly interpolation but very similar, if that makes sense...

 

[Joe4]

It looks like it is just solving the equation for CU, right?
If so, then you will need to replace your hard-code numbers (variables) with Excel range references.
If you do that, we can come up with an Excel formula based on those ranges that will work whenever you plug numbers into those ranges.
But you just need to let us know exactly what you want those range references to be (i.e. what cell is 0.82 going in, what cell is 0.74 going in, etc).

 

[Ruben40870]

I'm working off a table for a light, I'll post the table for this equation below.

But the I need to do the equation for different lights so the table will change as the light changes, so I would prefer to manually input the values I get from the table.

 

[JamesCanale]

I think you had your formula a bit backwards - this makes more sense:

MrExcelPlayground2.xlsm
	B	C
4	0	0.82
5	0.880909	0.749527
6	1	0.74
Sheet30

Cell Formulas
Range	Formula
C5	C5	=(C6-C4)/(B6-B4)*(B5-B4)+C4

 

[JamesCanale]

You can do something like this for the whole table....

MrExcelPlayground2.xlsm
	B	C	D	E	F	G	H	I	J	K
24			Column:	2
25			5	0.45
26			5.15	0.4425
27			6	0.4
28
29		1	2	3	4	5	6	7	8	9
30	0	.82	.82	.82	.80	.80	.80	.76	.76	.76
31	1	.74	.72	.70	.73	.71	.69	.70	.68	.67
32	2	.67	.64	.61	.66	.63	.60	.64	.61	.59
33	3	.61	.56	.53	.60	.56	.52	.58	.54	.51
34	4	.55	.50	.46	.54	.50	.46	.53	.49	.45
35	5	.50	.45	.41	.49	.44	.41	.48	.44	.40
36	6	.46	.40	.37	.45	.40	.37	.44	.40	.36
37	7	.42	.37	.33	.41	.36	.33	.40	.36	.33
38	8	.38	.33	.30	.38	.33	.30	.37	.33	.30
39	9	.36	.31	.27	.35	.30	.27	.34	.30	.27
40	10	.33	.28	.25	.33	.28	.25	.32	.28	.25
Sheet30

Cell Formulas
Range	Formula
D25	D25	=XLOOKUP(D26,B30:B40,B30:B40,"",-1,1)
E25	E25	=VLOOKUP(D25,B30:K40,E24+1,FALSE)
E26	E26	=(E27-E25)/(D27-D25)*(D26-D25)+E25
D27	D27	=D25+1
E27	E27	=VLOOKUP(D27,B30:K40,E24+1,FALSE)

 

[Ruben40870]

It works out now, but only sometimes.

For instance the following equation doesn't get calculated correctly:

The difference between the two equations being the first on there was a minus after the CU in the equation, versus now there is a plus after the CU. This is important to get the correct value.

Whether its a +/- is based on the given RCR value, if the value is closer to the lower value then a plus sign is used. If a higher RCR value is given then a - sign is used in the equation.

 

[Ruben40870]

You can do something like this for the whole table....

MrExcelPlayground2.xlsm
	B	C	D	E	F	G	H	I	J	K
24			Column:	2
25			5	0.45
26			5.15	0.4425
27			6	0.4
28
29		1	2	3	4	5	6	7	8	9
30	0	.82	.82	.82	.80	.80	.80	.76	.76	.76
31	1	.74	.72	.70	.73	.71	.69	.70	.68	.67
32	2	.67	.64	.61	.66	.63	.60	.64	.61	.59
33	3	.61	.56	.53	.60	.56	.52	.58	.54	.51
34	4	.55	.50	.46	.54	.50	.46	.53	.49	.45
35	5	.50	.45	.41	.49	.44	.41	.48	.44	.40
36	6	.46	.40	.37	.45	.40	.37	.44	.40	.36
37	7	.42	.37	.33	.41	.36	.33	.40	.36	.33
38	8	.38	.33	.30	.38	.33	.30	.37	.33	.30
39	9	.36	.31	.27	.35	.30	.27	.34	.30	.27
40	10	.33	.28	.25	.33	.28	.25	.32	.28	.25
Sheet30

Cell Formulas
Range	Formula
D25	D25	=XLOOKUP(D26,B30:B40,B30:B40,"",-1,1)
E25	E25	=VLOOKUP(D25,B30:K40,E24+1,FALSE)
E26	E26	=(E27-E25)/(D27-D25)*(D26-D25)+E25
D27	D27	=D25+1
E27	E27	=VLOOKUP(D27,B30:K40,E24+1,FALSE)

It would too much work as the equation may only be done like three times and not again thereafter. Different tables will always be used