CODEWITHSUNDEEP
excelexcel-formula
mfaiz
mfaiz

Reputation: 535

Most efficient way of finding nearest co-ordinate based on condition in excel

I am trying to find the nearest 3d co ordinate to a data set of co ordinates in another table based on condition:

Data set:

X           Y           Z           Cat
680.762963  188.8037037 487.3962963 A
690.862963  185.562963  518.5185185 B
791.8481481 196.0962963 474.9222222 B
751.8518519 195.237037  466.4851852 B
692.5925926 170.7407407 466.2962963 A
713.7037037 251.8518519 472.4074074 C
710.3703704 208.5185185 472.4074074 C
767.4074074 210.3703704 472.4074074 A
758.8888889 236.2962963 472.4074074 C
636.9703704 174.2296296 484.6666667 A
625.0888889 171.7555556 494.862963  C
381.4814815 168.5185185 537.4074074 A

Now lets say I have another set of co-ordinates and now i want to find the nearest co-ordinate in the data set if it is in a particular category.

Lets keep it easy and say origin.

0,0,0 C

I want the answer to be:

0 0 0 C [Distance] [X] [Y] [Z]

where [x],[y],[z] is the number from the data set, and [Distance] the straight line distance between the co-ordinates sqrt((x-x)^2 ....)

Further expanding on it i have 100s of other points that i need to compare it against.

I have used google sheets and it is relatively easy to do there, but I want to use excel.

Upvotes: 0

Views: 1517

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

The following formula will find the shortest distance from your point to a point in the list with a matching category letter. The formula is placed in cell J2 for the data layout pictured below:

=AGGREGATE(15,6,SQRT(($A$2:$A$13-F2)^2+($B$2:$B$13-G2)^2+($C$2:$C$13-H2)^2)/($D$2:$D$13=I2),1)

Now that you have a value you to match against, determine the row where that value you came from. Since that value could hypothetically match a distance from a different category letter match the category letter at the same time. In the event that there are multiple matching points, the first one in the list will be returned. Place the row result in an index formula. repeat the formula for each column of the coordinates. I used a column counter to grab the appropriate column. Place the following formula in K2 and copy to the right to M2.

=INDEX($A:$C,AGGREGATE(15,6,ROW($A$2:$A$13)/((SQRT(($A$2:$A$13-$F2)^2+($B$2:$B$13-$G2)^2+($C$2:$C$13-$H2)^2)=$J2)*($D$2:$D$13=$I2)),1),COLUMN(A1))

For multiple data points, place them in row 3 downwards and copy the range J2:M2 downwards to match.

POC

NOTE: You may (probably) run into issues with rounding errors which will cause your calculate distance in J2 to not match with itself when recalculated in adjacent cells. Depending on the level of accuracy you need, your could put the distance formula for all cases inside a ROUND() function and set the returned number of decimal places short enough that the rounding error disappears.

Upvotes: 1

Related Questions