search for x-y co-ordinates

Hi folks
how do I search for the points which are very close together in an array of 
points (represented x,y co-ordinates)?
Any help will be greatly appreciated


0
Utf
12/15/2009 11:00:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1073 Views

Similar Articles

[PageSpeed] 29

If your X coordinates start in cell A2, and your y coords in B2, then in C2 
array enter (enter using Ctrl-Shift-Enter) the formula

=SMALL(SQRT((A2-$A$2:$A$???)^2+(B2-$B$2:$B$???)^2),2)

Replace the ??? with the row of your last XY pair.

Then copy down to match your XY list.

Then in D2, enter the formula

=IF(C2=MIN(C:C),"We're closest","")

and copy down to match.  At least two cells will say "We're closest" (if 
there is a tie, you may have 3 or 4 or more points that you will need to 
pair up - but you could help with that by sorting your values by the X value 
first).

HTH,
Bernie
MS Excel MVP



"twin peaks" <twinpeaks@discussions.microsoft.com> wrote in message 
news:B53000D3-0F04-4BAD-898B-BF5E1444BEEF@microsoft.com...
> Hi folks
> how do I search for the points which are very close together in an array 
> of
> points (represented x,y co-ordinates)?
> Any help will be greatly appreciated
>
> 

0
Bernie
12/16/2009 4:02:54 AM
Thanks Bernie
Do not understand replace ??? with the row of your last XY pair.
Tried insert actual values or say, A5,B5 but don't work
Kindly explain


"Bernie Deitrick" wrote:

> If your X coordinates start in cell A2, and your y coords in B2, then in C2 
> array enter (enter using Ctrl-Shift-Enter) the formula
> 
> =SMALL(SQRT((A2-$A$2:$A$???)^2+(B2-$B$2:$B$???)^2),2)
> 
> Replace the ??? with the row of your last XY pair.
> 
> Then copy down to match your XY list.
> 
> Then in D2, enter the formula
> 
> =IF(C2=MIN(C:C),"We're closest","")
> 
> and copy down to match.  At least two cells will say "We're closest" (if 
> there is a tie, you may have 3 or 4 or more points that you will need to 
> pair up - but you could help with that by sorting your values by the X value 
> first).
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> 
> "twin peaks" <twinpeaks@discussions.microsoft.com> wrote in message 
> news:B53000D3-0F04-4BAD-898B-BF5E1444BEEF@microsoft.com...
> > Hi folks
> > how do I search for the points which are very close together in an array 
> > of
> > points (represented x,y co-ordinates)?
> > Any help will be greatly appreciated
> >
> > 
> 
> .
> 
0
Utf
12/17/2009 1:37:01 AM
If you have XY data from rows 2 to 103, replace the ??? in the formula with 
103:

 =SMALL(SQRT((A2-$A$2:$A$103)^2+(B2-$B$2:$B$103)^2),2)

Bernie


"twin peaks" <twinpeaks@discussions.microsoft.com> wrote in message 
news:E40452A3-3F82-4DF4-A452-713B4751001C@microsoft.com...
> Thanks Bernie
> Do not understand replace ??? with the row of your last XY pair.
> Tried insert actual values or say, A5,B5 but don't work
> Kindly explain
>
>
> "Bernie Deitrick" wrote:
>
>> If your X coordinates start in cell A2, and your y coords in B2, then in 
>> C2
>> array enter (enter using Ctrl-Shift-Enter) the formula
>>
>> =SMALL(SQRT((A2-$A$2:$A$???)^2+(B2-$B$2:$B$???)^2),2)
>>
>> Replace the ??? with the row of your last XY pair.
>>
>> Then copy down to match your XY list.
>>
>> Then in D2, enter the formula
>>
>> =IF(C2=MIN(C:C),"We're closest","")
>>
>> and copy down to match.  At least two cells will say "We're closest" (if
>> there is a tie, you may have 3 or 4 or more points that you will need to
>> pair up - but you could help with that by sorting your values by the X 
>> value
>> first).
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "twin peaks" <twinpeaks@discussions.microsoft.com> wrote in message
>> news:B53000D3-0F04-4BAD-898B-BF5E1444BEEF@microsoft.com...
>> > Hi folks
>> > how do I search for the points which are very close together in an 
>> > array
>> > of
>> > points (represented x,y co-ordinates)?
>> > Any help will be greatly appreciated
>> >
>> >
>>
>> .
>> 

0
Bernie
12/17/2009 3:23:14 AM
Reply:

Similar Artilces:

Look for: Search In => Choose folders=> Search subfolders not working
When I search for an old email using Outlook's Look for: box and specify which folders to search in via the "Choose folders=>Search subfolders" checking a folder does not actually cause any subfolders to be checked. Unless there is some other setting and/or option hidden away somewhere, the "Search subfolders" check box is not working. I must manually check all the desired subfolders. What am I doing wrong? -- Regards, Peter Sale Santa Monica, CA USA To email me, just pull 'my-leg.' ...

Unanswered NonConsecutive X Axis
I posted this earlier and never got a response, so I have added some more detail and am reposting in hopes of solving my issue... I have a single column of data AJ11:AJ58 which contains a formula that results in OPEN or CLOSED. I need to reference this column in multiple formulas as well as in the X Axis of a chart. Not only do these values change, but they are also not necessarialy consecutive. For example, one pass results in a range of OPEN cells (which is what I need to reference) of AJ25:AJ52 and another common pass results in AJ11:AJ12,AJ21:AJ58. Is there a way to name this dy...

Office V.x updates: which in which order?
With apologies for not finding quick coherence in the various threads related to this, I don't have enough information to figure out how to update Office V.x. One site http://support.microsoft.com/default.aspx?scid=fh;EN-US;macoffx indicate v.X 10.1.2 Update is the most recent, but also talk about "service release "SR-1" But from another site http://www.microsoft.com/mac/downloads.aspx#OfficeX there are clearly updates 10.1.2 through 10.1.5. I've tried several times to install them in order, and sometime always goes wrong (there is a long list, including repeated...

If Cell has x then 10 other cells get automatically filled
We have a form made in Excel that we want to have automatically filled in. We have a drop down box in Cell A1 with 5 selections. If we select choice 2 from the drop downs we want to automatically pull a set of data from another worksheet and populate 10 different cells in the form with the data pulled in. If we select choice 5 from the drop downs we want to automatically pull a set of data from another worksheet and populate 10 different cells in the form with the data pulled in. Hi Hardcourter, On sheet 2 of the workbook, A1:A5 holds the same list as the drop down list on sheet ...

Search last 4 digits in an (account number)
I store bank accunt and credit card numbers in various Contact Notes. For example, a credit card # as xxxxxxxxxxxx1234. Outlook contact search would not find this contact if I enter the search for "1234". Is there a way to do this in Outlook 2007? TIA PS - this search works fine in Outlook Mobile. outlook searches from the beginning - so it will only work if you use xxxxxxxxxxxx 1234. Other search tools (like google desktop) may find it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: htt...

converting 1.x database to 2.0 from DOS files
I saved the 1.x database on floppies. Now I need the database again and need to upgrade it. How do I copy the floppies with the 1.x database so that it will work with access 2.0 in an XP pro environment? ...

Search & replace in formulae
Is it possible to do a search and replace which includes the cell formula contents ? I have a workbook that has cells which link to the contents of a cell in another workbook and I'd like to know if the naming has to remain constant or if I can replace a part of the name periodically. ie each year. thanks "Iain Rhodes" <iain@pricejam.com> wrote in message news:8ec201c49682$858c5d20$a501280a@phx.gbl... > Is it possible to do a search and replace which includes > the cell formula contents ? I have a workbook that has > cells which link to the contents of a cel...

Searching for Newsgroups
I subscribe to a Usenet service for accessing various news groups. After I download all the newsgroups from that server I can not find a way to search for the newsgroups I have interest in. When I put something like "RV" in the search box on the top right corner, it searches all of my Emails rather than just the list of groups that had "RV" in the title. Help tells me that there is a " Display newsgroups containing" box but for the life of me I can't find it. Any thoughts out there? I am running Entourage 12.2.3 Dunc Click the bar in th...

Search engine optimization
Hello all! My question today regarding this issue is: I have noticed some sites have the following on their sites "Source". meta name= "robots" content="Index, follow" meta name= "revisit-after" content="15 days" or (30 days) What does this function accomplish as far as search engines go? and will it really imporve your results ( rankings) Like for instance "robots" "index, all" what is the difference between this and saying "robots" "index, follow" I'm a bit confused about this. Can someone ...

Active-x
Hi, i wrote an active-x control in atl and i use it in my MFC project (Add to project->component and controls...) but when i am adding a new method to the active-x the project that use it dont recognize it, What i need to do in the MFC project that make it see the new method?? When in put an active-x in a project it add an *.h and *.cpp files of that active-x automaticly how can i change them??? When i am working with simple atl com object i can change it compile it and in the MFC project i am import the new tlp file but with active-x it is diffrent! Thanks Easiest way is to whip up a...

x or y error bars? where are they?
I cannot find the x or y error bar tabs in the format data series menu. What's the chart type? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Chantel" <Chantel@discussions.microsoft.com> wrote in message news:FE95F339-7B03-4244-9021-715FAAD6B7E6@microsoft.com... >I cannot find the x or y error bar tabs in the format data series menu. What do you mean by chart type? It is a bar graph "Jon Peltier" wrote: > What's the chart type? > > - Jon > ------- > Jon Peltier, Micros...

Normalizing the y axis #2
I want to get a visual "apples to apples" effect in my charts. However, Excel doesn't seem to offer a way to "normalize" the y axis (that's the vertical axis, right?). If the data in the range goes from .01% to .88% for one data set and from 1% to 7% for another data set, Excel changes the y axis accordingly, which displays the illusion that the resulting charts are not much different from one another. I would prefer to take the highest data range (7% in this example) and the lowest (.01% in this example) and apply that as the y axis for both charts, so that the us...

Can I search identical fields from multiple tables?
I have a database to log details about staff in my school. I have a tables for staff details, courses, absence, educational visits etc. In each table I have a date field to show when the member of staff is not present. I want to produce a query to search all tables by entering a date - e.g. to bring back all results for staff who are not in school on a certiain day because they are either ill, on a course or on an educational visit. The query will work by entering a date once - it will then search all the other tables and bring back the results. Is this possible? Do I restructure? ...

Status Bar Text with Active X Controls
I have treeview and listview Active X controls on a form. When in form view when either of these controls has the focus I would like to display some descriptive text in the status bar. Unlike other Access controls, these Active X controls do not have a status bar text property. Is there any work around for this, so I don't have "Form View" displayed in the status bar? Ken You need the acSysCmdSetStatus and acSysCmdClearStatus actions of the SysCmd method Look up SysCmd in Access Help HTH "Ken Warthen" <KenWarthen@discussions.microsoft.com> wrote in...

What does outlook search when you search Contacts ?
When you do a search in oulook for contacts, what is the criteria that outlook searches? Or I guess how does outlook search? Why is it not consistant? The reason I am asking is that we have our customers contacts in public folders. In our customers we have 6 contacts for XYZ Foods. If you search in outlook for just "XYZ" it only comes back with 4 of the six. If you do a search for "XYZ Foods" it returns with all 6. And if you just entered "Foods" no contacts were found. And we actually have a few companies in our contacts who's names are a two part name ending...

excel VBA
How would i search a range of a whole column instead of specific cells like .Range("a1:a300")? --- Message posted from http://www.ExcelForum.com/ ..range("a:a") ..columns(1) ..range("a1").entirecolumn are a few ways. "Zygoid <" wrote: > > How would i search a range of a whole column instead of specific cells > like .Range("a1:a300")? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ahhh! Thanks!! I was trying .Column(1) i see i neede an "s" in there Thanks a...

Multiple fields using to search
Hi, Something i've been batteling for weeks with and theres probably such a simple answer to it and i just cant see it: Riiiight, i have this form (lets call it frmClientSearch) On this form i have a 2 list box thinghys; One called lstEmployee which is populated with Employee names and the other lstCity which is populated with City Names. Then i have a command button (cmdSearch) which, once click and values selected in both the lst boxes, should return only the values that are equal to both the lst boxes in a new form called frmSearchResult. (Hope i make sense) So... I know how ...

Search, Search, Thanks
In the last couple of weeks I have been fighting with different ways o synching my laptop and my home pc. I have ended up with numerou duplicate entries, duplicate address books and duplicate contact lists ALL of my questions/problems I was able to resolve simply by searchin through the forums. Chances are if you have a problem you're not th first. There are some really good people on these forums that know alo more about Outlook than I do. Just my .02 Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages dir...

Display search results
Using OneNote 2010 Beta: file tabs are on the left; how can I make search result tabs appear on the right? Now they don't appear at all: the first search result is displayed in the middle of the screen (that's fine) but no way to move to the next search result, nor any way to scrool them all the search results. I'd like to see all the search result tabs listed on the right so I can choose the one I want (as was the case with the version of OneNote that got replaced by 2010 beta). Many thanks for any help. -- CH I can't really visualize what you want, but h...

Slow excel X ?????
Hello, I'm using a G4 and I recently upgraded from OS 9 to OS 10.2. Then upgraded from Office:Mac 2001 to Office:Mac X 2004. The problem is now my spreadsheets run verrrrryyyyy sssllllloooooowwwwwllllllyyyyyy Does any one have any suggestions on how I might speed this up? All the Best, Nathan Sargean -- Message posted from http://www.ExcelForum.com I find that XL04 will sometimes run a bit slower than XL01 depending on what other apps are running (largely, I believe, since OSX is a preemptive multitasking system and OS 9 isn't). Other times it runs somewhat faster. Howev...

Excel 2003, graph y axis labelling problem
When I produce a graph in an excel spreadsheet, part of the label for the y-axis gets cut off and does not show up. If I select the graph and do a whole page print preview the problem does not show up. However, I always cut and paste my graphs into word documents in order to illustrate my results. This is a problem since the y-axis is then inadequately labelled. Does anyone know how to fix this? (I already have Office 2003 SP2) Thanks It's a well known problem. The work around (which I hate) is to add a couple of spaces and a period after the Y axis label and change the format ...

Active X Controls Shrinking on Excel worksheet
I have a workbook and sometimes the ActiveX controls such as listbox, options, checkboxes on the worksheet shrinks or expands while clicking on the controls. Does somebody has experirenced same kind of problem or somebody knows the fix. The only to fix this problem is to restart the machine again. Thanks in advance. ...

Including imap folders in outlook 2003 search folders
Dear all, Is it possible to include imap accounts/folders as providers for "virtual" search folders in outlook 2003? At the moment, I only see the option to check folders in my personal folders but not to include imap folders. The reason that I ask is that I would like to be able to get a complete view of follow up tasks across all my accounts (1 pop3, 2 imap and 1 hotmail). Only the pop3 account gets delivered into the personal folders and is included in the follow up a nd unread search folders. Thanks for your help in advanced. Raj ...

Search Form Problems
I have a form that has two fields in it. The first field is for a ID number, and the other is for the projects name. I am having a problem with using the form for as a search tool. Some of the values are duplicates in the drop down, I wanted to know how to properly filter the combo box so that the values can only be seen once. The Row Source is the name of the table that the fields are from, however this only words for the ID combo box and not the project name one. For some reason the values are from the ID column in the project name's combo box as well. To counter this problem, I si...

Search
Is it possible to search on the results on a cell? For example, a cell may have a formula such as =3+5 You can search and find the 3 or the 5. The cell with show 8, is it possible to search for an 8 and have it find this cell? TIA Yes. In the Search dialog, choose Options and select Look in Values. -- Kind Regards, Niek Otten Microsoft MVP - Excel "berj" <berj@adelphia.net> wrote in message news:WbednSOFT7Cbp57fRVn-1Q@adelphia.com... > Is it possible to search on the results on a cell? > > For example, a cell may have a formula such as > > =3+5 >...