Formula for determining if point A on a 2D plane is within radius X of point B

Hello all. My question combines two things I'm rusty at: Excel and geometry. I have a 2D 'map' with various locations on a coordinate grid. I am trying to find a formula that would determine whether location A is within radius X of location B.

Another user asked a different but related question a few years ago ["Formula(s) to find locations that are within radius of each other"], but in regards to locations on a sphere (i.e. the Earth). While the suggested answer looks relevant to that use case, I'm guessing that it would not directly apply to my own 2D scenario.

Thanks in advance for any thoughts.

So, you have the coordinates of locations A and B and want to know if the distance between them is less than or equal to R ?

Tell us what cells you are using, or better still, attach a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon (Attachments button) does not work on this Forum, so don't try to use that.

Hope this helps.

Pete

Pete_UK, thanks for the recommendation, and for the step-by-step guidance on uploading.

The now attached sample workbook includes a selection of locations and grid coordinates. For present purposes, let radius R equal 50 units. Thanks again to any with thoughts.

You just need to brush up on your Pythagoras Theorem - this distance between two points is the square root of the difference between the x-coordinates (squared) and the difference between the y-coordinates (squared). As an Excel formula, this would translate into:

=SQRT((B2-B3)^2 + (C2-C3)^2)

for the distance between Alpha and Beta. However, as you have many locations, I presume you want to calculate the distance between all of them, so in the attached file I have repeated the names of the locations across row 1 from column E onwards, and used this formula in F2:

=SQRT(($B2-VLOOKUP(F$1,$A$2:$C$11,2,0))^2 + ($C2-VLOOKUP(F$1,$A$2:$C$11,3,0))^2)

This can be copied across and down in the upper triangle of the grid, where you can read this as FROM the location in column A and TO the location in row 1. This is very similar to the above formula, but the second coordinates are obtained using a VLOOKUP function based on the name in row 1.

The distance between Alpha and Alpha is obviously zero, so I have shown the main diagonal of the grid as n/a and shaded this grey.

I have used cell D2 to contain your "radius", so this can easily be adjusted if you need to, and I have used the lower triangle of the grid to indicate yes or no if the distance falls within the value of D2 using this formula in E3:

=IF(INDEX($E$2:$N$11,MATCH(E$1,$A$2:$A$11,0),MATCH($A3,$E$1:$N$1,0))<=$D$2,"yes","no")

Again, this can be copied down and across as required.

Hope this helps.

Pete

Pete_UK, thanks very much for your answer and the customized workbook. You're absolutely right about the usefulness of refreshing on basic geometry ( embarrassed!).

Your answer definitely addresses my question, so I've marked the thread as "Solved." I also now realize that this mini-project is going to be a little more complicated than I had thought, but that's a matter for another thread. Thanks again, Pete_UK!

Glad to help.


You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete