[RESOLVED] Find the closest Number compare to a list of Numbers

[Monti124]

Hi,Iam hoping someone can help me with this.
I have a flexgrid that contians 5 numbers. And I want to be abel to compare each number of the flexgrid to an array of 15 values (0 to 14) if the value in the flexgrid is closest to the value in the array then I want to show both values (flexgrid value and array closest value) int a textbox with the Multiline in true. any help would be apreciated.

[smUX]

Easiest way would be to sort the list of values and then look through them until you find your value is lower than the next one then compare the difference between the current and the next against the value you're working with and you can work out which one is closest.

[LaVolpe]

I have a flexgrid that contians 5 numbers. And I want to be abel to compare each number of the flexgrid to an array of 15 values (0 to 14) if the value in the flexgrid is closest to the value in the array then I want to show both values (flexgrid value and array closest value) int a textbox with the Multiline in true

Showing us some real data may help. Show us the 5 numbers and the 15 values.

What if 1 or more of the 5 match 1 or more of the 15?
If 1 of the 5 matches 1 of the 15 exactly, do you stop or continue on?
Or is this something like: for each of the 5, find its closest match in the 15?

You see, I'm a bit confused too, and if I am, others may be too. Better details could be helpful for us.

[Monti124]

thank you both for the response and to answer the question . Here are part of the numbers I want ot compare.
in My flexgrid I have the following numbers.

.1980
.1990
.2000
.2010
.2020

and I want to compare each of these value to a set of predefine values. Like.

0.1992
0.1996
0.2000
0.2004
0.2008
0.2012
0.2016

for example: if 1992 is the closest number to 1980 then a Want to put 1192 in a text box.or put any number I choose.
hope these explanation works.

[LaVolpe]

Is this for each of the 5 numbers?
One more question. What if the difference is identical both below and above? Example
Test 5 against 3, 7, 9, 13, 21, etc. The difference btwn 5 & 3 and 5 & 7 are the same.

[Monti124]

in your ecenario I would prefere 7 an 3 as the answer.
since the two numbers are closest to 5.
in my program this is what Iam trying to
acomplish.

[LaVolpe]

Well, here's what I suggest, especially if your array of 15 is sorted. Psuedo code follows:

For Nr = 1 To 5
    lBestMatchIndex = 0
    For X = 1 To UBound(arrayOf15)
       If Abs(flexGridNr(Nr) - arrayOf15(X)) < Abs(flexGridNr(Nr) - arrayOf15(lBestMatchIndex)) Then
           lBestMatchIndex = X
       End If
    Next
    ' now you have the best match, you can compare it to the one above to see if they are same difference
    If lBestMatchIndex = UBound(arrayOf15) Then ' no comparison needed
        Debug.Print "Best Match for "; Nr; " is "; arrayOf15(lBestMatchIndex)
    ElseIf Abs(flexGridNr(Nr) - arrayOf15(lBestMatchIndex)) = Abs(flexGridNr(Nr) - arrayOf15(lBestMatchIndex + 1)) Then
         ' number high & low have same difference
        Debug.Print "Best Match for "; Nr; " is "; arrayOf15(lBestMatchIndex ); " and "; arrayOf15(lBestMatchIndex + 1)
    Else
        Debug.Print "Best Match for "; Nr; " is "; arrayOf15(lBestMatchIndex)
    End IF
Next

Edited: fixed some typos with lBestMatchIndex. Anyway, I think you can follow the logic; and that's the main purpose of pseudo code

[Monti124]

Thank you LaVolpe, I just plug your code into my program an it worked perfect, thats exactly What I wanted My program to do. Thanks for your time

[LaVolpe]

You are welcome. Keep in mind that if your arrayOf15 is not sorted, that ELSEIF statement will not be a valid way to determine if more than one number is closest. If that is a big requirement, you will want to ensure your array is sorted.

Edited: You may want to cache this portion of the calcs inside the loop when a new bestmatch is found. No need to recalculate it every time

Abs(flexGridNr(Nr) - arrayOf15(lBestMatchIndex))