Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel nearest number

Posted on 2003-04-01
Medium Priority
1,648 Views
Hello,
I've got a question about excel. I've got a programm where I calculate a value A, this value A is an indication.
There is a list with numbers pressent the value A should indicate the nearest worth.

Example
---------------
List of numbers                Value A=13,2
---------------                -------------
5
6
12
15,5
25
---------------------------------------------

In this case i need the value 12 out of the list, cause A is closer to 12 than to 15,5.

I hope you can help me,
Thanks
Jos

0
Question by:sky_JBE

LVL 44

Expert Comment

ID: 8245865
Hello Sky_JBE,

you could try the vlookup function

=VLOOKUP(A,the column with values here,columnnumber,find closest match)

=VLOOKUP(B1,A2:A8,1,TRUE)

HAGD:O)Bruintje
0

LVL 1

Accepted Solution

iaminit earned 150 total points
ID: 8246583
If I'm understanding the question right, I don't think the VLOOKUP alone will do the trick.  When set to TRUE, VLOOKUP will pick the closest match lower or equal to the search value.  It won't pick the higher number even if it's closer.

You could write some VBA code to find the value, but if your layout allows it, I would recommend adding a column to the left of your list.  For instance, if your list of values are in B2:B10, put zero in A2, then in A3, add the formula =AVERAGE(B2:B3) and copy it through A10.  Then, assuming the value you want to check is in C2, use the formula =VLOOKUP(C2,A2:B10,2,TRUE)

In other words, determine the midpoint of your values and use the lookup based on that to determine the actual closest value.

Sorry for the long-winded answer.  Hope I was on the right track.
0

LVL 4

Expert Comment

ID: 8248800
Name the cell with the value A "value", and the range with list of number "list". (I assume you know how to name a range, otherwise feel free to ask)

Then copy this tiny (..) bit of formula to the cell where you want the answer:

=IF(value<INDEX(list;1);INDEX(list;1);IF(value>INDEX(list;COUNT(list));INDEX(list;COUNT(list));IF(value-INDEX(list;MATCH(value;list))<INDEX(list;MATCH(value;list)+1)-value;INDEX(list;MATCH(value;list));INDEX(list;MATCH(value;list)+1))))

It also manages values less/greater then lowest/largest value in list. If you know for sure value A always will be greater than (or equal to) the lowest value and less than (or equal to) the greatest the formula could be shortened.

-Matthias
0

Expert Comment

ID: 8295839
Neat formula... I think it works.

I tried the VBA route... easy

called =GetNearest(list,value)

'getnearest... add in some module in your Workbook
Public Function GetNearest(rng As Range, target As Variant)
'assume range is a 1 x N
Dim diff As Variant
diff = target   'just a guess

Dim ret As Variant
ret = rng.Cells(1)

Dim cl As Range

For Each cl In rng.Cells
If Abs(cl.Value - target) < diff Then
'we want this one
ret = cl.Value
diff = Abs(cl.Value - target)
End If
Next cl

GetNearest = ret
End Function
0

Author Comment

ID: 8326854
Hey,
Sorry for the late reaction but I had some problems with my IP, someone used it to send spam along the internet.
But I think the problem is solved.

You're solution was the best way to use in the excel sheet i've created. That's why I give you the points.

Thanks again,
Jos
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
###### Suggested Courses
Course of the Month13 days, 3 hours left to enroll

#### 578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.