XLSTAT: Analytic Hierarchy Process (AHP) in Excel

 

Background

AHP is a decision aid method based on a criteria hierarchization. It is better adapted when the criteria number remains reasonable, and when the user is able to evaluate 2 by 2 the elements of his problem. The AHP feature proposed in XLSTAT has the advantage of not having any limitations on the number of criteria, of subcriteria and of alternatives and allows the participation of a large number of evaluators.

 

Dataset For Example

The (attached) data represent a multi-criteria decision-making problem for selecting a new car.

• Evaluators: Steeve, Owen, and Jack

• Criteria: Cost, Safety, Capacity, Style

  • Cost subcriteria: Purchase price, Fuel cost, Maintenance, Resale

  • Capacity subcriteria: Number of passengers, Cargo capacity

• Alternatives: Six car models evaluated across all criteria and subcriteria

• Data is organized in a table, with evaluators scoring each alternative.

 

 

Setting Up

Step 1: Generate the AHP design

• Open XLSTAT.

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

• The Designs for AHP analysis dialog box will appear.

In the General tab:

• Select the car list as Alternatives.

• Choose the criteria column for Criteria.

• Select the 4 subcriteria columns in the appropriate field.

• Choose the Evaluators label column.

• Click OK to generate the design, which appears in the AHP design sheet. It includes a data summary, Saaty table, and instructions for filling the comparison tables.

 

The Saaty table guides the 3 evaluators (e.g., Owen) in completing their pairwise comparisons.

For example, Owen rated cost vs. style as 7, meaning cost is very important compared to style, based on the Saaty scale.

 

 

Step 2: Run the Analysis

 

• Click on the OK button to launch the computations.

• Results are displayed in a new sheet named AHP.

 

Interpreting the Results

The first outputs are tables and graphs showing the mean results across evaluators, followed by individual results.

Results include:

• A criteria table

• One or more subcriteria tables

• An alternatives table

• Optional bar charts (if graphical output is enabled)

In this example, the cost criterion—especially the purchase price subcriterion—has the greatest impact on the decision.

Based on the priorities, the Element car emerges as the best choice. However, when considering all criteria together, the Odyssey appears to be the best overall choice based on the sum of weighted scores.

  

 

Going further with AHP analysis

The XLSTAT AHP feature offers the possibility to test the data consistency by calculating two parameters: the index of coherence and the ratio of coherence. This test allows checking the inconsistencies which could be entered in the comparison tables. For example, if the ratio of coherence is greater than 10% then it is recommended to review the evaluation of the comparison table concerned.