find closest match to a reference number in a row of numbers

How can I find the closest match larger( or smaller) than a reference value 
in a row of unsorted data
0
Krill (1)
12/20/2005 5:07:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
526 Views

Similar Articles

[PageSpeed] 25

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

A5 = lookup_value

=INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))

Biff

"Nick Krill" <Nick Krill@discussions.microsoft.com> wrote in message 
news:48E40D61-4180-43FC-B9D9-293C462F1AD5@microsoft.com...
> How can I find the closest match larger( or smaller) than a reference 
> value
> in a row of unsorted data 


0
biffinpitt (3172)
12/20/2005 6:03:03 AM
Biff:

This is very nice.  Can you explain or interpret how the formula is treating or 
bringing back the both the match(row) and the match(column) arguments of the 
index function?

Confused here...
TIA,,

"Biff" <biffinpitt@comcast.net> wrote in message 
news:uv1qMsSBGHA.272@TK2MSFTNGP11.phx.gbl...
> Hi!
>
> Try this:
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> A5 = lookup_value
>
> =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))
>
> Biff
>
> "Nick Krill" <Nick Krill@discussions.microsoft.com> wrote in message 
> news:48E40D61-4180-43FC-B9D9-293C462F1AD5@microsoft.com...
>> How can I find the closest match larger( or smaller) than a reference value
>> in a row of unsorted data
>
> 


0
jmay (696)
12/20/2005 12:00:46 PM
Hi!

It's only referencing a single (row) array so there is no column argument.

Simply determine the minimum deviation from the lookup_value. Since the OP 
stated that it could be either above or below the lookup_value we have to 
use the ABS function so that negative deviations are made equal to positive 
deviations.

Biff

"JMay" <jmay@cox.net> wrote in message 
news:hnSpf.61035$WH.17922@dukeread01...
> Biff:
>
> This is very nice.  Can you explain or interpret how the formula is 
> treating or bringing back the both the match(row) and the match(column) 
> arguments of the index function?
>
> Confused here...
> TIA,,
>
> "Biff" <biffinpitt@comcast.net> wrote in message 
> news:uv1qMsSBGHA.272@TK2MSFTNGP11.phx.gbl...
>> Hi!
>>
>> Try this:
>>
>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>
>> A5 = lookup_value
>>
>> =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))
>>
>> Biff
>>
>> "Nick Krill" <Nick Krill@discussions.microsoft.com> wrote in message 
>> news:48E40D61-4180-43FC-B9D9-293C462F1AD5@microsoft.com...
>>> How can I find the closest match larger( or smaller) than a reference 
>>> value
>>> in a row of unsorted data
>>
>>
>
> 


0
biffinpitt (3172)
12/21/2005 3:27:02 AM
Thanks for the clarification Biff;
Jim

"Biff" wrote:

> Hi!
> 
> It's only referencing a single (row) array so there is no column argument.
> 
> Simply determine the minimum deviation from the lookup_value. Since the OP 
> stated that it could be either above or below the lookup_value we have to 
> use the ABS function so that negative deviations are made equal to positive 
> deviations.
> 
> Biff
> 
> "JMay" <jmay@cox.net> wrote in message 
> news:hnSpf.61035$WH.17922@dukeread01...
> > Biff:
> >
> > This is very nice.  Can you explain or interpret how the formula is 
> > treating or bringing back the both the match(row) and the match(column) 
> > arguments of the index function?
> >
> > Confused here...
> > TIA,,
> >
> > "Biff" <biffinpitt@comcast.net> wrote in message 
> > news:uv1qMsSBGHA.272@TK2MSFTNGP11.phx.gbl...
> >> Hi!
> >>
> >> Try this:
> >>
> >> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
> >>
> >> A5 = lookup_value
> >>
> >> =INDEX(A1:J1,MATCH(MIN(ABS(A1:J1-A5)),ABS(A1:J1-A5),0))
> >>
> >> Biff
> >>
> >> "Nick Krill" <Nick Krill@discussions.microsoft.com> wrote in message 
> >> news:48E40D61-4180-43FC-B9D9-293C462F1AD5@microsoft.com...
> >>> How can I find the closest match larger( or smaller) than a reference 
> >>> value
> >>> in a row of unsorted data
> >>
> >>
> >
> > 
> 
> 
> 
0
JimMay (35)
12/21/2005 11:59:02 AM
Reply:

Similar Artilces:

Can Enter/Match on a Returned Shipment (863636)
Need to resolve this issue. It is an issue with all distribution clients who are processing returns against POs ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=560e3fb7-1f...

limit number of rows 7 colloms in a worksheet
is there a way to limit or set the number of rows & collums in a worksheet ? thanks david --- Message posted from http://www.ExcelForum.com/ "davidbrowne17" <davidbrowne17.ya1sm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya1sm@excelforum-nospam.com... > is there a way to limit or set the number of rows & collums in a > worksheet ? No. All worksheets have 256 columns by 65536 rows. You can hide unused rows/columns. But why bother? Hi David, Put this in the ThisWorkbook code module. Adjust to suit the area. Private Sub Workbook_Open() Wo...

NUMBER FORMAT #9
CREATE A NEW NUMBER FORMAT SO THAT THE SELECTED DATES WILL APPEAR ONLY AS THE FULL NAME OF THE DAYS OF THE WEEK. ...

Why are my numbers disappearing in excel yet it totals them?
I have a spreadsheet that I have filled out the individual cells with number. These cells are totaling correctly, however when I open the spreadsheet the individual number I entered are showing blank.... I moved my mouse around in the spreadhsheet and all of a sudden the numbers appeared and then disappeared. Check the font color. The default is black. Also check the cell color. Default is "no fill". If the font color was changed to white, you would only see the content after you select the cell. Remember to Click Yes, if this post helps! "Donna S...

No account number shown in printed checks
Using Money 2007 Deluxe. Printed a series of checks from MS Mmoney this morning, but the account numbers didn't print on the checks. Each of the payees has an account number entered in the appropriate place, in the "Go to Payees" detail listing. Can't figure out what's going on here! Thanks for any assistance. Dave On Sat, 17 Mar 2007 08:37:03 -0700, Dave M. <DaveM@discussions.microsoft.com> wrote: >Using Money 2007 Deluxe. > >Printed a series of checks from MS Mmoney this morning, but the account >numbers didn't print on the checks. Each of...

Where do I receive or find a delivery receipt?
To test how delivery receipt works I sent an email to myself, requesting delivery receipt, (so I know that it was received) but what form does delivery receipt come in? Same thing with read receipt, I sent myself one, respond yes when I opened the message, but haven't received anything I can see that is a read receipt. Where would I find it? Delivery receipts are generated by the recipient's mail server, if it is supported. As for the read receipt, have you hit send/receive after reading the email? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the d...

How to remove dashes and slashes form a sequence of numbers & lett
Hi I have a sequence of numbers in column D and I require to extract just the numbers and letters to column E. D 190/0-01 31-0014 pp7/44-1 uf-744-5 E 190001 310014 pp7441 uf7445 Any pointers would be much appreciated. Kind Regards Celticshadow Put this in E1: =3DSUBSTITUTE(SUBSTITUTE(D1,"/",""),"-","") and copy down as required. Hope this helps. Pete On Oct 14, 11:34=A0am, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > > I have a sequence of numbers in column D and I require to extract just th= e > ...

refer to second column of listbox
Hi, I have a multi selected listbox. How can I refer to the second column of the listbox? Me.lstContract.ItemData(varItem).column(1) ??? Dim varItem As Variant For Each varItem In Me.lstContract.ItemsSelected gstrReportFilter = "[Ck_ContractID]='" & Me.lstContract.ItemData(varItem) & "'" ''DoCmd.OpenReport "rptCoFundActivity_k" '', , , gstrReportFilter DoCmd.OpenReport "rptCostShareContribution_k", acViewPreview '', , gstrReportFilter Next varItem SF ...

Insert rows and fill formulas
I have just added this on a work sheet. It works OK when sheet is unprotected but when I protect the sheet it comes up error 400. If I select Insert row on protection box it will insert row but not formulas while protected. How can I get it to work while sheet is protected. I have also setup a botton on the taskbar to do this for me but it will only do it for the day I set it up in. When I save as for the next day it wont work on the new sheet. Your help will be appreciated Regards Chris I'm not an expert but I thought the whole idea of protecting the worksheet is so p...

Newbie question: finding aliases and forwards
If I have an address such as services@somewhere.com how could I figure out where (which user) that address exists at in Exchange or where it forwards to (if it forwards versus belonging to a user)? Thanks. just go to Active Directory Users and computers and do a find with a filter by the users email Address "Joe Blow" wrote: > If I have an address such as services@somewhere.com > how could I figure out where (which user) that address exists at in Exchange > or where it forwards to (if it forwards versus belonging to a user)? > Thanks. > > > ...

Finding Transactions
I am using Money 06. Is there a way for me to locate transactions across my accounts where the 'Category' is blank? Thanks Transactions by Category report customized Category|Clear All, Category|Include unassigned income transactions, Category|Include unassigned expense transactions, Date|All Dates, Account|Select All, etc. "Bill" <Bill@discussions.microsoft.com> wrote in message news:24D7BE0D-2C92-42B2-8E58-BCF2202096D1@microsoft.com... >I am using Money 06. Is there a way for me to locate transactions across my > accounts where the 'Category' is bl...

I did install Genuine Advantage ActiveX control. But why could not find it in my IE Add-ons List?
Several days ago, I downloaded Windows Mobile Device Center from Micorosft web site for my Windows 7. As it needed Windows Genuine Verification, I installed the Genuine Advantage ActiveX control for my IE8. But now, I can not find this activeX control in my IE Add-ons List( IE -> Tools -> Manage Add-ons options of Internet Explorer ). Anyone could tell me why and where? Thanks in advance. It installed and it is not removable in any shape or form. Once done (Genuine Advantage ActiveX) it is done :-) -- Peter Please Reply to Newsgroup for the benefit of oth...

mail does not find temporay directory to open attached .xls file
Since few weeks, I cannot open attached .xls file, for the temporary directory is not found. I bypass it by saving the xls file in a a folder and then open it from there. It does't work if saved on desktop. All other attached files, pps or world, are ok -- Are all your Windows Updates current? I seem to recall there was an update for IE8 a couple of months ago which fixed the failure to open attachments. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "escofie" <escofie@discussions.microsoft.com&...

no matching symbolic information found
After I changed pc's recently, I started noticing the following. What should I do now? Loaded 'ntdll.dll', no matching symbolic information found. Loaded 'C:\WINDOWS\system32\kernel32.dll', no matching symbolic information found. Loaded symbols for 'C:\WINDOWS\system32\MFC42D.DLL' Loaded symbols for 'C:\WINDOWS\system32\MSVCRTD.DLL' Loaded 'C:\WINDOWS\system32\gdi32.dll', no matching symbolic information found. Loaded 'C:\WINDOWS\system32\user32.dll', no matching symbolic information found. Loaded 'C:\WINDOWS\system32\imm32.dll'...

Jump to start of next row teaser
Hi, can anyone assist in telling me if there is either :- a) a shortcut key b) a macro c) none of the above but another solution to jumping to the beginning of a new row on pressing a key. For example, after completeing cell m10 can you press enter (or any other key) and the cursor will automatically go to cell a11, then after you get to m11 it jumps to a12 and so on? Just to make things a little trickier, I know you can do this using the protect worksheet etc function, but I also have the data filter running which doesn't work when the protect sheet function is on. If there is a macr...

Conver General number to Currency
I have a column of numbers in Excel 2003 that are of General type. I need to insert a decimal point two positions in from the right of the existing number. When I do that however by using Format > Cells and changing it to Number with 2 decimal places, or to Currency format, it adds a .00 to the existing number instead of inserting a decimal point into the existing number, e.g., 999955 come out as 999955.00 or $999,955.00 when what I really want it to do is 9999.55 or even $9,999.55. Please help! Put 100 in an empty cell, select the numbers, do edit>paste special and select divide, ...

finding differing numbers.
How do I in a column of numbers some in duplication, how can i get a list off the entries which reflects these numbers but not in duplication. ie "numbers" 1, 1, 2, 3, 4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5 Thanks Chris Hi one way: - select your column - choose 'Data - Filter Advanced Filter' - choose a new range and 'unique entries' -- Regards Frank Kabel Frankfurt, Germany curleyc wrote: > How do I in a column of numbers some in duplication, how can i get a > list off the entries which reflects these numbers but not in > duplicat...

where do i find autoarchive on my computer
I have Winows XP and have no idea how to find the archive file. I used to have an icon on the desk top but that has disappeared. Any help would be appreciated. Your question does not have anything to do with Word. Autoarchiving is a feature of Outlook. Was archiving of mail items that you were thinking of? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Ganga" <Ganga@discussions.microsoft.com> wrote in mess...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

A2 cell reference increment
I want to reference a cell using some math: $B(1+1) which I would hope equals $B2 and the cell would then contai a reference to $B2. How does one perform math funtions to the row part of a reference? I what to be able to reference a cell that contains a cell reference So Cell A1 would contain the text B12 cell A2 would reference to cell A1 and show the contents of B12. and in cell A3, I want to show the contents of B13... but I want t take the contents of cell A1 and increment it from B12 to B13... How do I do that? I tried simple math like $B(1+1) which does no equal $B2. T...

Cannot Install / Find Driver for Microsoft 2.4GHz Transceiver USB?
I recently purchased a Microsoft Comfort 5000 wireless keyboard and mouse. I can successfully install the software associated with the keyboard and mouse, but when I plug in the USB Transceiver device Windows Install shield tells me "the hardware was not installed because the wizard cannot find the necessary software'. I've tried manually to search the disk and my hard drive and it still cannot find the software. It doesn't matter if I plug the USB device into any of the USB slots on my PC. I'm running Windows XP and have had similar install problems with ...

Is there any easy way to find out the version of MDB file ?
We are using MS Access 2003. We just find that there is a MDB file that hasn't been used for a number of years. We would like to know is there any easy way to find out which version of MS Access (Like Access 97 / Access 2.0) it is created ? When we open it, it asks us to "Convert" or "Open it". Thanks http://www.microsoft.com/downloads/details.aspx?FamilyID=2e861e76-5d89-450a-b977-980a9841111e&DisplayLang=en may be of use. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Peter" <Peter@discussions.microsoft...

Styleref
I am using Word 2003 in a Windows XP Pro environment. I have a document which uses the StyleRef field in the footers to assist the reader when navigating the, rather large, document. On one page, in the middle of a lengthy section, the StyleRef does not show the text referenced. All of the other footers in the section show the text correctly (I am using odd and even pages and they look great), there actually is text on the page using the referenced style (although that shouldn't matter), and there isn't an "empty" or blank spot on the page which has the referenc...

Last Record Numbering
I need to find a way to display in a form text box the highest numerical value entered in any one of four fields for the last record entered. For example: If Field_1 is 0001 Field_2 is 0008 Field_3 is 0005 Field_4 is 0004 When the form is opened, the textbox would display the number 0008. The textbox should display the highest of the four fields of the last record. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Already responded to in another newsgroup. It is rarely necessary to post and re-post the same question in multiple ...

Downloaded "Statement Quantity" does not match Money 2007
I have been using Money's Account Download service with my financial institution (AG Edwards) for over a year with absolutely no problems. Last week I downloaded my last batch of transactions and Money could not reconcile what was in the software vs. what was being reported by AGE. It suggested I add 19 shares of a stock I do not own so that Money and AGE could synch up. Not only have never owned this symbol, but my online AGE account doesn't show that I own it either. Yet the Position Matching screen in Money shows a position for this stock in the "Statement Quantity&q...