# 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?

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?

3. ## Re: Euclidean Distance Matrix

I think that might work.

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?

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?

6. ## Re: Euclidean Distance Matrix

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

7. ## Re: Euclidean Distance Matrix

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1