Electre 3 multi-criteria decision analysis in Excel

This tutorial shows how to use and interpret the Electre 3 decision aid method to classify solutions to a decision-making problem in Excel using XLSTAT.

Dataset to use in an Electre 3 analysis

The data represent the evaluation of ten cars on seven criteria (example taken in Roy 1977). The data set is summarised in the following performance table. Cars are represented in columns and criteria in rows. The criteria are: the price of the car in Franc ( prix ), the maximum speed in km / h (Vmax), the consumption at 120 km / h in liters (C120), the volume of the car trunk in dm3 (Coff) , the time taken to go from 0 to 100 km / h in seconds ( Acce ), the braking distance at 130km / h (Frei) and the sound level in db (Brui).

To allow variable contribution of the criteria in the decision-making process one could provide weights increasing with the importance of the criterion. For example: The analysis requires defining 3 thresholds: the indifference threshold (q), the preference threshold (p) and the veto threshold (v). In this example we will use a linear threshold format. Thus we need to define two columns of numeric values for each threshold, one for the slope (label beginning by alpha in the figure below) and another one for the interception (label beginning by beta) as shown below: We complete the data preparation by imposing a direction of evaluation for each criterion and a direction of definition for each threshold. Data must be of numerical type set to 1 or -1. The goal of this tutorial is to rank the cars from the best to the worst according to all given criteria.

Setting up an Electre 3 analysis with XLSTAT

1. Once XLSTAT is open, select the XLSTAT / Advanced features/Decision aid/Multicriteria decision aid test command: The multicriteria decision aid test dialog box appears.

In the General tab, select columns A-J (evaluation of cars over criteria) in the Performance matrix field. Then select column L in the Criteria weights field and column K in the Row Label field.

Choose the Threshold Format, at the right bottom of the window, as a linear function. Then select the 2 columns of your dataset to define each of the 3 thresholds: Indifference, Preference and Veto thresholds. We precise that if the constant format is selected, only 1 column per threshold is required for the computations.

4. In the Options tab, select the columns containing Criteria evaluation direction and Threshold direction variables of your dataset.​
5. In the Outputs tab, you have the choice to activate one of the following options or all: The computations start once you click on the OK button and the results are displayed on a new sheet named Electre.

Interpreting the results of an Electre 3 analysis

The first result is a full set of descriptive statistics given per cars. Then one could find the matrix of concordance, the matrix of credibility, followed by the outranking matrix that contains the final result as shown in the next figure for the tutorial example. It is a diagonal matrix composed of « I », « P », « R » and « NP». The reading direction is from the left to right, that is to say, that the element a of line i is either preferred (P), not preferred (NP), not comparable (R) or indifferent (I) to the element b of the column j. To ease the interpretation of this matrix Xlstat proposes a table summarizing the number of P, NP and R of this outranking matrix and then to deduce more easily the final ranking table below: ​ For example, the R21TS car is the best car that answers the decision problem. The P205G and RCLIO are both ranked at the second place. The R25BA less fits to the whole criteria and ranks at the end of the table.

Reference

Roy, B. (1977), Electre III, un algorithme de classement fondé sur une représentation floue des préférences en présence de critères multiples. Cahiers du Centre d’études de recherche opérationnelle, 20 (1) : 3-24.