×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Finding the nearest values?

Finding the nearest values?

Finding the nearest values?

(OP)
I'm having a problem here with finding a simple function (that does not involve me messing with nested IF functions) to find the nearest values from some data.

A1 contains my value (number, two decimal places).
 
A2 to A11 contains a list of values in ascending order.

In A12 I want to put the nearest value less than A1's value and in A13 the nearest value that is greater than A1's value.

In the event I get an exact match (unlikely but possible) I need to know what will happen in A12 and A13 so that  can then responding with an IF function and modify the subsequent calculations.

I've been trying to figure this out using Hlookup and Index functions but the help directions seem to have been written by Martians and the examples are crap. dazed

Some gentle help for idiots required please.

PS this should be automatic and not require special keystrokes. It should also, if possible, avoid add-ins.
This spreadsheet will be distributed to others to use.
I am writing it in  Excel 2002.

JMW
www.ViscoAnalyser.com

 

RE: Finding the nearest values?

In B1 put =MATCH(A1,A2:A11)
In A12 put =INDEX($A$2:$A$11,$B$1)
In A13 put =INDEX($A$2:$A$11,$B$1+1)

If you get an exact match it will appear in A12, and A13 will be the next number in the list.

You could also use a lookup function, but for a list in a column you want VLOOKUP, not HLOOKUP.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Finding the nearest values?

(OP)
Thanks Doug, I'm going to try that solution next.
In the meanwhile I have searched all the learned sites.
Most seem to use some sort of INDEX(ABS(MIN(****) function that require cntl+shift+enter
However, the solution that works to find the higher and lower numbers best, so far, I found here:
http://www.exceltip.com/st/Retrieving_the_Closest_Larger_/_Closest_Smaller_Values_from_a_List_when_there_is_No_Exact_Match/993.html
This works a treat.
I now have to add in some IF functions for where the value entered is either below the lowest value in my range or above the highest number in my range and the exact match situation.

JMW
www.ViscoAnalyser.com

 

RE: Finding the nearest values?

(OP)
Thanks Doug, that is an elegant solution also. Now I have to work out which one will best suit the subsequent operations. I suspect yours because of that initial =MATCH(A1,A2:A11)
which gives me an index to work with.
Thanks again.

JMW
www.ViscoAnalyser.com

 

RE: Finding the nearest values?

jmw - the formulas given in your link will work on an unsorted list.  If you know the list will always be sorted using =MATCH is simpler. In fact that is exactly what it is for.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Finding the nearest values?

(OP)
Thanks again IDS, that is not something I had noticed because my data is ordered.

Many of the "expert" help sites so often come up with no real answers to problems.

In this case I was lucky to be able to learn of both solutions (neither of which requires "cntl+shift+enter") on the same day I posed the question.

Always satisfying to push ones own envelope and learn new things, and comforting to know there is good help at hand.



 

JMW
www.ViscoAnalyser.com

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources