# Euclidean Distance Matrix

1. ## Euclidean Distance Matrix

I am creating a 100X100 matrix with Euclidean Distance from the master attributes sheet (See attached workbook). The matrix will be created on the Euclidean Distance sheet. The formula that I am using is as follows:
= ((risk of item 1 - risk of item 2)^2 + (cost of item 1 - cost of item 2)^2 + (performance of item 1 - performance of item 2)^2)^(1/2)
I need to compare every item like this and put the result in the corresponding cell of the Euclidean Distance sheet. How could I do this without having manually enter the equation for every item?  Register To Reply

2. ## Re: Euclidean Distance Matrix

Is Item 1 in Column A and Item 2 in Row 1 or visa versa?
Assuming the first then ``Please Login or Register  to view this content.``
This equation (in B2) can be dragged across and down. However, you will get a number error when performance of 1<performance of 2 (SQ Root of neg number) Is this what you are looking for?  Register To Reply

3. ## Re: Euclidean Distance Matrix

I think that might work.  Register To Reply

4. ## Re: Euclidean Distance Matrix Originally Posted by jdmay I think that might work.
That is what I was looking for but is there a way to fix the problem with the negative numbers?  Register To Reply

5. ## Re: Euclidean Distance Matrix

Yes, put an IF statement around the formula checking to see if you get the Sq root of a negative number like so ``Please Login or Register  to view this content.``
Replace "NA" with whatever you desire in those cells, 0, -, "" (blank). Does that work for you?  Register To Reply

6. ## Re: Euclidean Distance Matrix

How can you end up with a negative number in a distance formula?  Register To Reply

7. ## Re: Euclidean Distance Matrix

The negative values in the distance can be solved by taking the absolute value each value =ABS()  Register To Reply