Finding approximate value based on interpolation of data

[dezinsektor]

Hello,

I need help with this problem of finding the closest result based on 3 criteria.
Using index and match I get the result but only if the result value and the variables are exact match.

These are the measurements that I get from device:
Variable_1 - B2=59.3
Variable_2 - B3=31.3
Variable_3 - B4=5.2
________
Result (Q) B5=?

So I need to match these variables with data table (of course that the table is much bigger, it has over 32.000 cells):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column E
Variable_3[/TD]
[TD]Column F
Variable_2[/TD]
[TD]Column G
Result (Q)[/TD]
[TD]Column I
Variable_1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]31.5[/TD]
[TD]0.5[/TD]
[TD]72.4[/TD]
[/TR]
[TR]
[TD]5.5[/TD]
[TD]32[/TD]
[TD]1[/TD]
[TD]65.28[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]32.5[/TD]
[TD]2[/TD]
[TD]66.5[/TD]
[/TR]
[TR]
[TD]6.5[/TD]
[TD]33[/TD]
[TD]1.5[/TD]
[TD]66[/TD]
[/TR]
</tbody>[/TABLE]

To explain, if variable_3=5, variable_2=31.5 and variable_1=72.4, the result is 0.5.
But if only one of the variables don't match I get no result (#N/A) with index match.
And the problem is that sometimes none of the variables don't match, then I need to find closest matches of 3 variables and get the result.

This is the formula I use:
={INDEX($G$13:$G$113;MATCH(1;(B2=$I$13:$I$113)*(B3=$F$13:$F$113)*(B4=$E$13:$E$113);0))}

If VBA is needed to solve this I would gladly accept it as a solution to this problem.


Thank you very much!

 

[Eric W]

Welcome to MrExcel.

I don't think you mean interpolation, and I'm not even sure how you'd want to do that, given the table you showed. But here's an option that returns the Result (Q) value from the row that has the smallest absolute difference of the 3 values from the table and your B2:B4 cells:

Book1
	A	B	C	D	E	F	G	H	I
1					Variable_3	Variable_2	Result (Q)		Variable_1
2	Variable_1	59.3			5	31.5	0.5		72.4
3	Variable_2	31.3			5.5	32	1		65.28
4	Variable_3	5.2			6	32.5	2		66.5
5	Result	1			6.5	33	1.5		66
Sheet2

Cell Formulas
Range	Formula
B5	{=INDEX(G2:G5,MATCH(MIN(ABS(E2:E5-B4)+ABS(F2:F5-B3)+ABS(I2:I5-B2)),ABS(E2:E5-B4)+ABS(F2:F5-B3)+ABS(I2:I5-B2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dezinsektor]

Eric, thank you very much for your prompt answer!
Could you be so kind and explain this formula in detail.

Thanks again.
(beer on me if you ever come to Zagreb)

 

[Eric W]

Consider:

=INDEX(G2:G5,MATCH(MIN(ABS(E2:E5-B4)+ABS(F2:F5-B3)+ABS(I2:I5-B2)),ABS(E2:E5-B4)+ABS(F2:F5-B3)+ABS(I2:I5-B2),0))

When you create an array formula, which you did by pressing Control+Shift+Enter, the formula acts on a range (or ranges) of data instead of just individual values. Then at some point in the formula, there's a way to compress the multiple results back to a single result.

This part: ABS(E2:E5-B4) finds the difference between every value in E2:E5 and B4, then computes the absolute value. This works out to: 5 - 5.2, 5.5 - 5.2, 6 - 5.2, and 6.5 - 5.2 or {-.2,.3,.8,1.8} and the absolute value makes it {.2,.3,.8,1.8}. This is repeated for the other two parameters, giving {.2,.7,1.2,1.7} and {13.1,5.98,7.2,6.7}. Then the 3 arrays are added together piecewise, giving {13.5,6.98,9.2,10.2}. Note that I used ABS to make sure that a positive and negative value won't cancel out, which would make it seem "closer" than it is. I considered using SQR instead of ABS, but that depends on your data and would require some analysis. I also noted that the values in the third array are larger than the other two arrays, meaning that a large difference there would dominate the other arrays, meaning that the other 2 would have very little effect on the outcome. You could consider scaling the parameters to account for that. But again, more analysis needed.

Anyway, we're almost done. Given the {13.5,6.98,9.2,10.2} array the MIN function finds the smallest value, 6.98, which represents the "closest" set of parameters, then uses that in a MATCH function with the full array again, finds it in the second position, then return the Result from the second position, which is G3 or 1.

Hope this makes sense!


(And if I'm ever in Zagreb, I'll take you up on that! )

 

[dezinsektor]

Consider:

=INDEX(G2:G5,MATCH(MIN(ABS(E2:E5-B4)+ABS(F2:F5-B3)+ABS(I2:I5-B2)),ABS(E2:E5-B4)+ABS(F2:F5-B3)+ABS(I2:I5-B2),0))

When you create an array formula, which you did by pressing Control+Shift+Enter, the formula acts on a range (or ranges) of data instead of just individual values. Then at some point in the formula, there's a way to compress the multiple results back to a single result.

This part: ABS(E2:E5-B4) finds the difference between every value in E2:E5 and B4, then computes the absolute value. This works out to: 5 - 5.2, 5.5 - 5.2, 6 - 5.2, and 6.5 - 5.2 or {-.2,.3,.8,1.8} and the absolute value makes it {.2,.3,.8,1.8}. This is repeated for the other two parameters, giving {.2,.7,1.2,1.7} and {13.1,5.98,7.2,6.7}. Then the 3 arrays are added together piecewise, giving {13.5,6.98,9.2,10.2}. Note that I used ABS to make sure that a positive and negative value won't cancel out, which would make it seem "closer" than it is. I considered using SQR instead of ABS, but that depends on your data and would require some analysis. I also noted that the values in the third array are larger than the other two arrays, meaning that a large difference there would dominate the other arrays, meaning that the other 2 would have very little effect on the outcome. You could consider scaling the parameters to account for that. But again, more analysis needed.

Anyway, we're almost done. Given the {13.5,6.98,9.2,10.2} array the MIN function finds the smallest value, 6.98, which represents the "closest" set of parameters, then uses that in a MATCH function with the full array again, finds it in the second position, then return the Result from the second position, which is G3 or 1.

Hope this makes sense!


(And if I'm ever in Zagreb, I'll take you up on that! )

It makes perfect sense.
Just 2 things bother me a bit:
1st - why is there twice this expression for every variable ABS(E2:E5-B4), ABS(F2:F5-B3) and ABS(I2:I5-B2?
2nd - I did the formula breakdown and calculate every step how you wrote it but with the bigger set of data. I noticed that this is not always the case: "MIN function finds the smallest value, 6.98, which represents the "closest" set of parameters, then uses that in a MATCH function with the full array again, finds it in the second position, then return the Result from the second position"

Sometimes the smallest value is, let say in 7th position and result in 6th. Or this is just a coincidence that both are in 2nd position?

 

[Eric W]

It makes perfect sense.
Just 2 things bother me a bit:
1st - why is there twice this expression for every variable ABS(E2:E5-B4), ABS(F2:F5-B3) and ABS(I2:I5-B2?

The array containing the "closeness" formula is generated twice. Once is used to find the minimum value, the other is used as a search array. If that array is {8,7,6,5} you'd get something like:

...MATCH(MIN({8,7,6,5}),{8,7,6,5},0)...

...MATCH(5,{8,7,6,5},0)...

...4...

The 4 means that the minimum value, 5, is found in the 4th position.

2nd - I did the formula breakdown and calculate every step how you wrote it but with the bigger set of data. I noticed that this is not always the case: "MIN function finds the smallest value, 6.98, which represents the "closest" set of parameters, then uses that in a MATCH function with the full array again, finds it in the second position, then return the Result from the second position"

Sometimes the smallest value is, let say in 7th position and result in 6th. Or this is just a coincidence that both are in 2nd position?

As in the example above, the matching value will not always be in the second position. It will be in the position of the smallest value, representing the closest match from your table.

 

[dezinsektor]

Thank you Eric for the explanation.
I understand and it is perfectly clear (as the water spring) that the matching value will not always be in the second position (I'm not an idiot )
I thought that the matching value and the result are always at the same position, like in first example.

Again, thnx, so I guess this is 2 beer on me if you ever come here. I don't see why not, it is a beautiful country with great biodiversity (mountains, rivers, waterfalls, sea)

 

[dezinsektor]

Hi, it's me again.
At the end of the program we made in VBA, this formula is used to find the solution based on mentioned variables. It works fine, but what I want is to somehow highlight the row where the match is been found, it's difficult to find it manually because there is over 25 000 rows.

Thank you!

 

[dezinsektor]

Even better, to return all the values from that row in desired cell

 

[Eric W]

That all seems doable, I just need a better idea of how you want the output to look. Do you want all 4 values in the same cell? Or 1 each in B5:E5? And possibly the row number? Do you still want the actual row highlighted? Do you care if it's formulas or VBA? How often do you run this?