Excel nearest number

Posted on 2003-04-01
Medium Priority
Last Modified: 2012-05-04
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.

List of numbers                Value A=13,2
---------------                -------------  

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,

Question by:sky_JBE
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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)



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.

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:


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.


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

Author Comment

ID: 8326854
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,

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

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

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

Join & Ask a Question