XLSTAT: Electre 3 multi-criteria decision analysis in Excel

 

Dataset For Example

The (attached) data represent the evaluation of ten cars on seven criteria (example taken in Roy 1977). The dataset is summarised in the following performance table.

 

Cars are represented in columns and criteria in rows.

The criteria are:

• price of the car in Franc (prix)
• maximum speed in km / h (Vmax)
• consumption at 120 km / h in liters (C120)
• volume of the car trunk in dm3 (Coff)
• time taken to go from 0 to 100 km / h in seconds (Acce)
• braking distance at 130km / h (Frei)
• sound level in db (Brui).

To account for varying importance of criteria in decision-making, assign weights that reflect their significance.

This analysis uses three thresholds—indifference (q), preference (p), and veto (v)— in a linear format. Each threshold is defined by two parameters: a slope (α) and an intercept (β), represented in separate numeric columns as shown below: 

  

We finalize data preparation by assigning an evaluation direction and threshold definition direction for each criterion, using numeric values of 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

• Open XLSTAT.

• Click on the Marketing/ Decision aid menu and select the Electre feature.

• The multicriteria decision aid test dialog box appears.

In the General tab:

• Select columns A–J for the Performance matrix, column L for Criteria weights, and column K for Row label.

• Set the Threshold format to Linear function, then assign two columns for each threshold: Indifference, Preference, and Veto.(Note: If using Constant format, only one column per threshold is needed.)

   

In the Options tab:

• Specify the columns for Criteria evaluation direction and Threshold direction.

  

In the Outputs tab:

• Enable any or all of the following options:

  

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The results begin with descriptive statistics for each car, followed by the concordance matrix, credibility matrix, and the final outranking matrix, as shown below.

The outranking matrix is diagonal and uses "I", "P", "R", and "NP" to indicate if option i is indifferent, preferred, not comparable, or not preferred to option j (read left to right). XLSTAT also provides a summary table of these outcomes (P, NP, R) to help derive the final ranking.

For example, the R21TS ranks first as the best option. P205G and RCLIO share second place, while R25BA ranks last, being the least aligned with the criteria.

 

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.