TOPSIS, or Technique for Order Preference by Similarity, is one of the MADM methods that can solve three-level problems. When we say a three-level problem, we mean a problem with a goal, criteria, and alternatives. You can see the structure of a three-level problem in this slide. We have four criteria and three alternatives.
Here are the TOPSIS steps. We will follow these steps in Excel, for example.
- Find weights for criteria.
- Form a decision matrix for alternatives.
- Normalize the decision matrix.
- Form a weighted normalized decision matrix.
- Find positive and negative ideals.
- Find the distance from the positive and negative ideals.
- Find the closest distance from the positive ideal and the farthest distance from the negative ideal.
To design questionnaires for criteria, you can refer to AHP tutorial part 2. In that video, we explained in detail how to tailor a questionnaire for a pairwise comparison matrix. In the third part of the AHP tutorial, we also explained how to compute the weights and inconsistency of a pairwise comparison matrix. Thus, we assume that you have watched these videos and have found the weights for the criteria. For alternatives, you can use documented data or assign a number on the Likert scale to each cell. For instance, if the convenience of an alternative is very high and your Likert scale ranges from 1 to 10, you can assign a value of 10. Indeed, for alternatives, we do not have a pairwise comparison matrix.
Download excel file for TOPSIS here
In the decision matrix columns may have different scales. In this case, you need to convert them to the same scale, usually between zero and one. We can achieve this by normalizing the numbers, either by dividing each number by the sum of numbers in the corresponding column or by dividing each number by the square root of the sum of squares of numbers in the corresponding column. We prefer to use the latter option for normalization.
So, to normalize the matrix, we first need to find the sum of squares of each column using the SUMSQ function. Simply type “=SUMSQ(” and select the column of interest, then close the parenthesis and press Enter. You can copy this formula to other columns by dragging the cell to the right. Next, divide each cell in the column by the corresponding sum of squares. To do this, enter the formula “=cell/sum_of_squares” in a new cell and press Enter. You can then copy this formula to all the cells in the column. Note that when dividing all values in a column by a fixed number, you can press F4 after typing the fixed value to automatically fix the cell reference.
To form the weighted decision matrix, you need to multiply the criteria weights by their corresponding column in the normalized decision matrix.
For positive criteria, the positive ideal corresponds to the maximum value, and the negative ideal corresponds to the minimum value. However, for negative criteria, the positive ideal is the minimum value, and the negative ideal is the maximum value. A positive criterion refers to a criterion where a larger value is preferred, while a negative criterion refers to a criterion where a smaller value is preferred. In this case, price and fuel are negative criteria, while convenience and model are positive criteria.
Finally, we need to find the distance of each row from the positive and negative ideals. To do this, you can use the following formula in Excel: =SQRT(SUMXMY2(first vector, second vector)). Here, the “first vector” refers to the row of values in the weighted normalized decision matrix, while the “second vector” refers to the values of the positive or negative ideal.
To find the final weights, we need to divide the distance of each alternative from the negative ideal (dj-) by the sum of distances from both negative and positive ideals (dj- + dj+). These weights are unnormalized. To normalize them, you can divide each weight by the sum of all weights.