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):

Column E Variable_3	Column F Variable_2	Column G Result (Q)	Column I Variable_1
5	31.5	0.5	72.4
5.5	32	1	65.28
6	32.5	2	66.5
6.5	33	1.5	66

<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!

 

[dezinsektor]

I run this every day now because of the ongoing project.
Everything is in VBA, even the array formula which you gave me.
So, yes, the best solution will be to return the row number and all values from that row in one desired row (lets say cells (13, 13)).

Thanks again.

 

[Eric W]

I'm still a bit unclear on what you want, but let's try this:

First, press Alt-F11 to open the VBA editor. Go to a regular module and paste this code:

Public Function GetClosestQ(ByVal v1 As Double, ByVal v2 As Double, ByVal v3 As Double, _
                            ByVal r1 As Range, ByVal r2 As Range, ByVal r3 As Range, ByVal r4 As Range)
                            
 Dim s As Double, op(1 To 5, 1 To 1) As Variant, a1 As Variant, a2 As Variant, a3 As Variant, a4 As Variant
 Dim i As Long, w As Double
 
    If r1.Columns.Count > 1 Or r2.Columns.Count > 1 Or r3.Columns.Count > 1 Or r4.Columns.Count > 1 Then
        GetClosestQ = "The ranges must be 1 column wide."
        Exit Function
    End If
    
    If r1.Cells.Count <> r2.Cells.Count Or r1.Cells.Count <> r3.Cells.Count Or r1.Cells.Count <> r4.Cells.Count Then
        GetClosestQ = "Ranges are not the same size."
        Exit Function
    End If
    
    s = -1
    a1 = r1.Value
    a2 = r2.Value
    a3 = r3.Value
    a4 = r4.Value
    
    For i = 1 To UBound(a1)
        w = Abs(v1 - a1(i, 1)) + Abs(v2 - a2(i, 1)) + Abs(v3 - a3(i, 1)) + Abs(v4 - a4(i, 1))
        If w < s Or s < 0 Then
            s = w
            op(1, 1) = a1(i, 1)
            op(2, 1) = a2(i, 1)
            op(3, 1) = a3(i, 1)
            op(4, 1) = a4(i, 1)
            op(5, 1) = i + r1.Row - 1
        End If
    Next i
    
    GetClosestQ = op
                            
End Function

Close the VBA editor and go back to your worksheet.

Book1
	A	B	C	D	E	F	G	H	I
1			Results		Variable_1	Variable_2	Variable_3	Result (Q)	Row
2	Variable_1	59.3	65.28		65.28	32	5.5	1	11
3	Variable_2	31.3	32
4	Variable_3	5.2	5.5
5	Result (Q)		1
6	Row		11
7
8
9					Variable_3	Variable_2	Result (Q)		Variable_1
10					5	31.5	0.5		72.4
11					5.5	32	1		65.28
12					6	32.5	2		66.5
13					6.5	33	1.5		66
Sheet4

Cell Formulas
Range	Formula
E2:I2	E2	{=TRANSPOSE(getclosestq(B2,B3,B4,I10:I13,F10:F13,E10:E13,G10:G13))}
C2:C6	C2	{=getclosestq(B2,B3,B4,I10:I13,F10:F13,E10:E13,G10:G13)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

I tried deciding between a formula, a UDF, or a macro. Although a formula could work, I leaned against it due to complexity and performance with thousands of lines. If you only do this once a day, then a macro might be best. But I wasn't quite sure where you wanted the results put. So I ended up with a UDF since it should be as fast as VBA, and it allows you to pick the ranges you want.

In this example, your data is in E10:I13, and the matching values are in B2:B4. Select the C2:C6 range, enter the formula, and press Control+Shift+Enter, and you'll get the values you want in a vertical column. If you want a horizontal output, select E2:I2 and put the formula in a TRANSPOSE, and press Control+Shift+Enter. (Or I could change the UDF if you want.)

Let me know if this works for you.

 

[dezinsektor]

Thank you Eric, I will try this. I just need to figure it out where to put that code, because I use VBA to connect Excel with calculation software via open server.
I'll try to put it at the end of the code, after the disconnect subroutine.

 

[Eric W]

You can either put it at the end of your code, or you can add another module and put it there.

 

[dezinsektor]

Hi Eric, I tried it and it almost worked
Thank you very much for the effort.

This is the catch, with 35 721 combinations that I have, INDEX_MATCH_MIN_ABS... formula gives me result (Q) = 2.5
When I copied your VBA and entered array formulas the result (Q) = 2.4 (C2:C6)

Interesting thing is that these 2 results in the array (excel table) are next to each other, it looks like this:
variable 1 (set point) - 66.3
variable 2 (reading) - 31
variable 3 (reading) - 7.9
INDEX_MATCH_MIN_ABS.._result (Q) = 2.5

row number	variable 3	variable 2	(Q)	variable 1	calculation data 1	calculation data 2
6431	8	31	2.4	66.43	83.84	417
6432	8	31	2.5	66.24	85.75	417

As you can see, variable 3 and 2 are in both cases the same (8 and 31), variable 1 (66.24) in row 6432 is closer to 66.3 (set point) than in row 6431.

Any idea why?

ty.

 

[dezinsektor]

And I checked the set point value format, it is 66.30 when I add 1 decimal place.

 

[Eric W]

Well, it's probably because I'm a nitwit!

Change this line:

        w = Abs(v1 - a1(i, 1)) + Abs(v2 - a2(i, 1)) + Abs(v3 - a3(i, 1)) + Abs(v4 - a4(i, 1))

to:

        w = Abs(v1 - a1(i, 1)) + Abs(v2 - a2(i, 1)) + Abs(v3 - a3(i, 1))

I was having so much fun putting the 3 nearly identical terms in that I added a fourth term that isn't needed. Sorry!

 

[dezinsektor]

Eric, this is awesome! It works fine.
Just one more thing, I tried to modify your VBA code but didn't got what I wanted.
In the table, from my previous post you can see two additional columns (calculation data 1 and calculation data 2) I'd like to get that results also.

Thank you again and again...

 

[Eric W]

Use this UDF:

Public Function GetClosestQ(ByVal v1 As Double, ByVal v2 As Double, ByVal v3 As Double, _
                            ByVal r1 As Range, ByVal r2 As Range, ByVal r3 As Range, _
                            ByVal r4 As Range, ByVal r5 As Range, ByVal r6 As Range)
                            
 Dim s As Double, op(1 To 7, 1 To 1) As Variant, a1 As Variant, a2 As Variant, a3 As Variant, a4 As Variant
 Dim i As Long, w As Double, a5 As Variant, a6 As Variant
 
    If r1.Columns.Count > 1 Or r2.Columns.Count > 1 Or r3.Columns.Count > 1 Or r4.Columns.Count > 1 Or _
       r5.Columns.Count > 1 Or r6.Columns.Count > 1 Then
        GetClosestQ = "The ranges must be 1 column wide."
        Exit Function
    End If
    
    If r1.Cells.Count <> r2.Cells.Count Or r1.Cells.Count <> r3.Cells.Count Or _
       r1.Cells.Count <> r4.Cells.Count Or r1.Cells.Count <> r5.Cells.Count Or r1.Cells.Count <> r6.Cells.Count Then
        GetClosestQ = "Ranges are not the same size."
        Exit Function
    End If
    
    s = -1
    a1 = r1.Value
    a2 = r2.Value
    a3 = r3.Value
    a4 = r4.Value
    a5 = r5.Value
    a6 = r6.Value
    
    For i = 1 To UBound(a1)
        w = Abs(v1 - a1(i, 1)) + Abs(v2 - a2(i, 1)) + Abs(v3 - a3(i, 1))
        If w < s Or s < 0 Then
            s = w
            op(1, 1) = a1(i, 1)
            op(2, 1) = a2(i, 1)
            op(3, 1) = a3(i, 1)
            op(4, 1) = a4(i, 1)
            op(5, 1) = a5(i, 1)
            op(6, 1) = a6(i, 1)
            op(7, 1) = i + r1.Row - 1
        End If
    Next i
    
    GetClosestQ = op
                            
End Function

To call it, just select 7 cells instead of 5, and add the 2 additional ranges:

=getclosestq(B2,B3,B4,I10:I13,F10:F13,E10:E13,G10:G13,J10:J13,K10:K13)

I still put the row as the last returned value.

 

[dezinsektor]

Thank you Eric, you are genius!