Finding approximate value based on interpolation of data

dezinsektor

New Member
Joined
Sep 30, 2019
Messages
25
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):

Column E
Variable_3
Column F
Variable_2
Column G
Result (Q)
Column I
Variable_1
531.50.572.4
5.532165.28
632.5266.5
6.5331.566

<tbody>
</tbody>

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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
ABCDEFGHI
1Variable_3Variable_2Result (Q)Variable_1
2Variable_159.3531.50.572.4
3Variable_231.35.532165.28
4Variable_35.2632.5266.5
5Result16.5331.566
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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)
:)
 
Upvote 0
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! :cool:


(And if I'm ever in Zagreb, I'll take you up on that! (y) )
 
Upvote 0
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! :cool:


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

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?
 
Upvote 0
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.
 
Upvote 0
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) :)
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top