This may be easy but I'm missing it.
I have a row (A1-M1) of numbers. I want to input a formula to find which number(s) in columns B1-M1 are the nearest, + or -, to the number in A1.
As I say it's probably easy but if someone can give me an exmple formula for this that'd be great.
Thanks.
Returning multiple numbers in one cell could prove difficult for a native formula
In terms of returning the first closest match (should more than one exist)
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
EDIT: Nvm, that didn't work.
Last edited by JBeaucaire; 10-20-2010 at 01:05 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks for the reply and what you suggested does work but not in the way I wanted and it's my fault I didn't explain very well.
I've attached a simplified example of the sheet I'm working on. A question is asked and I input the answer in one column (C) and the answers I have been given by tested individuals. There answers go in the columns D-J under their respective name and what I want to do is put formulas in columns K-Q that search columns D-J to find which answer is closest to the answer in C and populate the relevant cell with the value "1" (more than one candidate can be the closest). If the answer in K-Q is exactly the same as the answer in C then populate the cell with the value "2".
Hope this makes sense.
Last edited by xyba; 10-20-2010 at 01:31 PM.
My suggestion would be the following:
thenPlease Login or Register to view this content.
Please Login or Register to view this content.
These work perfectly. Thanks very much for the help.
Further to this query, what if the values input could be either text and/or numbers, what changes would I need to make to this code?
I think you would need to first clarify the requirements...
If you mean "text input values" should be ignored (so as to prevent coercion errors etc...) then:
If on the other hand you're saying you want closest match to also work for text strings then that's a whole different ball game - and would warrant a new thread.Please Login or Register to view this content.
(goes without saying that the logic used to determine "closest" match of "text" can vary hugely from one context to the next)
Last edited by DonkeyOte; 10-21-2010 at 04:19 AM.
HiOriginally Posted by DonkeyOte
Yes I mean I want the closest match to work for text strings also.
Last edited by DonkeyOte; 10-21-2010 at 04:21 AM. Reason: reduced quote to pertinent parts
New thread please - and you would need to provide a lot of examples illustrating how you intend to determine the "closest" match.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks