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] dazed](https://www.tipmaster.com/images/dazed.gif)
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.
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] dazed](https://www.tipmaster.com/images/dazed.gif)
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.





RE: Finding the nearest values?
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?
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:
h
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?
which gives me an index to work with.
Thanks again.
JMW
www.ViscoAnalyser.com
RE: Finding the nearest values?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Finding the nearest values?
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