How to get the nearest possible number.

[ThrottleWorks]

Hi,

I have two datasets. File one. Range A1 value = 100.00
File 2,
A2 = 100.20
A3 = 100.40
A4 = 100.60
A5 = 100.80
A6 = 101.00

In above mentioned example, my output will be 100.20. How do I get this with formula or VBA.

Can anyone please help me in this.

 

[Chihiro]

Something like this in A2 of File one. If both books are open.

=SMALL(IF([Book3]Sheet1!$A$2:$A$6>$A$1,[Book3]Sheet1!$A$2:$A$6),1)
Confirmed with CSE.

If 2nd file is closed, adjust reference to 2nd file as needed.

Note: This works only if values looked at are larger than A1 (as in the example given).

 

[Chihiro]

Here's the version that checks whether value is greater than or smaller than, for minimum variance.

=INDEX([Lookup.xlsb]Sheet1!$A$2:$A$6,MATCH(MIN(ABS([Lookup.xlsb]Sheet1!$A$2:$A$6-A1)),ABS([Lookup.xlsb]Sheet1!$A$2:$A$6-A1),0))

Confirmed with CSE.

 

[Chihiro]

Here's vba method.

Sub MINVariance()
Dim twb As Workbook
Dim cRange As Range
Dim lRow As Long, i As Long
Dim minvar As Double, curmin As Double, resval As Double
Dim cArry As Variant

'Workbook where range of value is stored
Set twb = Workbooks.Open("C:\Test\Lookup.xlsb")

'Put range into array and store minvar (which is max possible variance at this point)
With twb
    lRow = .Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Set cRange = .Sheets(1).Range("A2:A" & lRow)
    cArry = cRange
    minvar = Application.WorksheetFunction.Max(cRange)
    .Close False
End With

'Loop through array and calculate current variance from Cell A1, if current variance is smaller than
'minvar then update minvar and result value
For i = LBound(cArry, 1) To UBound(cArry, 1)
    curmin = Abs(cArry(i, 1) - Cells(1, 1).Value)
    If curmin < minvar Then
        minvar = curmin
        resval = cArry(i, 1)
    End If
Next i

Cells(1, 2).Value = resval

End Sub

 

[ThrottleWorks]

Thanks a lot. Good night sir.

 

[Khalid NGO]

Hi Sachin,

One more formula approach, No-CSE:

=LARGE([Book2]Sheet1!A1:A6,COUNTIF([Book2]Sheet1!A1:A6,">"&A1))

Regards,

 

[ThrottleWorks]

@Khalid NGO you are awesome ! I was thinking about using 'Large' function. But I was pretty confused how would I use it.

Your use of Countif and Large is just awesome ! Thanks a lot. Have a nice day ahead.

 

[ThrottleWorks]

@Chihiro sir, thanks a lot for all these solutions. I am checking this and will revert with details. Have a nice day ahead.