Min distance between one point and one range of point

**np101** · 10-23-2011, 10:03 AM

hi, i want to write a formula to find the closed point of one list to one specified point, attached file show my question solved whit spreed sheet, but i want to convert it to one formula (VBA). can every one help me?

**daddylonglegs** · 10-23-2011, 11:02 AM

If you want to eliminate column G and use a single formula then try this "array formula"

=MIN(TEXT(SQRT((D$3-A3:A500)^2+(E$3-B3:B500)^2+(F$3-C3:C500)^2),"[>0]General;99999")+0)

confirmed with CTRL+SHIFT+ENTER

**np101** · 10-23-2011, 02:20 PM

oh my god! tank you man

) i don't know what is the array formula!!! but tanks a lot!

**np101** · 10-23-2011, 02:27 PM

but a minor question: i can't drag down this formula to apply to another row in blue color! (yes i add some another data below the blue column !) when i prefix reference in formula whit $ for drag down, it doesn't work

**arthurbr** · 10-23-2011, 02:53 PM

An array formula is one that should be entered with Ctrl+Shift+Enter after putting your insertion point in the formula bar.
The formula will then be delimited by braces. ( adding braces manually will NOT work)
Any change in the formula requires you have to commit again with CSE