Anthony Mellor
asked on
Find the closest item using x,y coordinates between two numbers
I had this question after viewing Find 10 closest x,y coordinates between two huge x,y data set.
Update: it seems one cannot use AND/OR in an array formula.
I am stuck at this point, where the AND test fails, I think because it is not testing the correct pairs of dates:
Array entered (shift-ctrl-enter), applies Pythagoras:
IF(AND(-85<($D5-$R$1:$R$33 36),+60>($ D5-$R$1:$R $3336)),
SUM(IF(SQRT(($B5-$P$1:$P$3 336)^2+($C 5-$Q$1:$Q$ 3336)^2)=S MALL(SQRT( ($B5-$P$1: $P$3336)^2 +($C5-$Q$1 :$Q$3336)^ 2),1),$O$1 :$O$3336,0 ))
,FALSE)
Update: currently the above has become a very long formula, no longer using AND. I am replicating the entire latter part of the formula within the two initial constraint tests, just to find with index/match, the date in table2 to check against.
I thought I had it working but now I am not sure my results are correct.
The above returns the reference number of the closest item: or at least, it does when the failing constraint is removed.
Example is attached.
"Distance1" is not constrained and seems to work.
Distance2 is meant to be constrained as above and is where I am stuck.
I have been using as my guide the above previous question/answer, though I want only the most close and then I want a constraint based on a further field (a "month" number).
(I am not using Excel dates, so my "dates" are straight numbers, because data is from a legacy system)
Excel version Office 365 current, Mac.
Example.xlsx
Update: it seems one cannot use AND/OR in an array formula.
I am stuck at this point, where the AND test fails, I think because it is not testing the correct pairs of dates:
Array entered (shift-ctrl-enter), applies Pythagoras:
IF(AND(-85<($D5-$R$1:$R$33
SUM(IF(SQRT(($B5-$P$1:$P$3
,FALSE)
Update: currently the above has become a very long formula, no longer using AND. I am replicating the entire latter part of the formula within the two initial constraint tests, just to find with index/match, the date in table2 to check against.
I thought I had it working but now I am not sure my results are correct.
The above returns the reference number of the closest item: or at least, it does when the failing constraint is removed.
Example is attached.
"Distance1" is not constrained and seems to work.
Distance2 is meant to be constrained as above and is where I am stuck.
I have been using as my guide the above previous question/answer, though I want only the most close and then I want a constraint based on a further field (a "month" number).
(I am not using Excel dates, so my "dates" are straight numbers, because data is from a legacy system)
Excel version Office 365 current, Mac.
Example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.