I have a list of customers and lat/long coordinates (3 columns:c,u,v respectively) I want to create 5 columns to resolve as the closest 5 customers.
So far I’m using
min(sqrt(([lat1]-[lat_array])^2+([long1]-[long_array])^2))
To minimize the distance but I’m running into a problem where lat1 is a member of lat_array and likewise with long. Thus I have 0 as a return. I need to minimize in a way that doesn’t say a is closest to itself, or even better, finds 2nd closest and 3rd closest.
I also need to include this formula in another that will say
If [lat1,long1] is 2nd closest to [lat70,long70] displays [customer70]
Sorry for formatting, I’m on mobile