View original (French)

EXCEL: calculating Euclidean distance

Solved
Dale-06 -  
 Flosedel -
Hello,

Here is my problem: I have the coordinates of several points, and I would like to obtain the Euclidean distances between all these points. The calculation is relatively simple, but the number of points is around 350, which means over 60,000 distances to report on a double-entry table. Until now, I haven't found a way to automate the useful data for the calculation, which makes my work almost unmanageable...

Thank you in advance for any help.
Configuration: Windows XP Internet Explorer 7.0

4 answers

  1. Marc_Grt
     
    In row 2, starting from column D, you put the 350 x values and in row 3, again starting from column D, the 350 y values.
    In column B, starting from row 4, again the 350 x values and in column C, still starting from row 4, the 350 y values.
    The formula in cell D4 is as follows =((D$2-$B4)^2+(D$3-$C4)^2)^0.5
    Use the square root instead of ^0.5, but I don't know the function in French; it's sqrt in the US version.

    Then drag this formula in both directions.
    4
    1. Dale-06
       
      Thank you very much, it looks good to me.
      0
    2. xkristi Posted messages 4336 Registration date   Status Member Last intervention   564 > Dale-06
       
      Be careful with the version of Excel you have
      if it's not 2007, you are limited to 254 columns

      a detail to put your x and y values in columns
      there is the copy-paste special transpose
      --
      xkristi
      I do not hold the truth, I willingly leave it to you...
      0
    3. Dale-06 > xkristi Posted messages 4336 Registration date   Status Member Last intervention  
       
      Oops...

      Actually, the trick that Marc-Grt gave me doesn't work... Indeed, after extending the calculation to other cells, the data used for the calculations in the other cells are no longer the original ones (x and y) but distances (which skews all the other distances)! The problem actually comes from the incremented copy. It should be such that in the same column, column A for example, the calculation always refers to certain rows (those of x and y in rows) but not to others (those of x and y in columns)...

      Example: for A(x1;y1) and B(x2;y2), the calculations in column A should always refer to x1 and y1 of this column but not to those of other columns.

      Is that possible?
      0
    4. xkristi Posted messages 4336 Registration date   Status Member Last intervention   564 > Dale-06
       
      Hello!

      For A(x1;y1) and B(x2;y2), the calculations in column A must always refer to x1 and y1 of that column but not to those of other columns.

      Possible, YES
      You place a $ before and after the letter
      which results in always referring to that cell
      $X$1


      --
      xkristi
      I do not hold the truth, I willingly leave it to you...
      0
    5. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482 > xkristi Posted messages 4336 Registration date   Status Member Last intervention  
       
      No, comrade xkristi! The formula from Marc_Grt in post 3 clearly indicates where to place $!
      However, you did well to point out the limitation to 255 columns; that means we will need to create 2 tables of 175 columns on separate sheets.
      --
      Retirement is great! Especially in the Caribbean... :-)
      Raymond
      0
  2. xkristi Posted messages 4336 Registration date   Status Member Last intervention   564
     
    'Hello!

    An Excel two-way table?
    Can you give an example of what you have and want?

    --
    xkristi
    I don't hold the truth, I'll gladly let you have it...
    0
    1. Dale-06
       
      Coordinates (example)
      Distances A B C D A 1 1
      A ? ? ? B 3 2
      B ? ? ? C 6 1
      C ? ? ? D 3 3
      D ? ? ?

      Table 1

      In the end, I need to be able to fill Table 1 (which normally measures 350x350...) based on the coordinates on the right.
      0
  3. Inconuu
     
    I didn't understand anything.
    0
    1. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      It doesn't matter, this discussion wasn't meant for you.
      --
      Retirement is great! Especially in the Caribbean... :-)
      Raymond
      0
  4. kiki
     

    How far is it from Château Renault to the medical school in Nantes?

    0
    1. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      2 km as the crow flies, 3 km on foot, 4 km by car, 26 km by boat (because you have to take a big detour).
      --
      Retirement is nice! Especially in the Caribbean... :-)
      Raymond
      0