Q: I read in some texts that a consistency ratio (actually inconsistency ratio) of less than 0.1 (10%) is good. I am not sure if your consistency ratio is a consistency ratio (i.e. the higher the percentage of the CR, the better and the more consistent the results are) vs inconsistency ratio (i.e. the consistency ratio percentage in your spreadsheet should be less in order to be more consistent).
Can you please let me know if a lower of higher percentage of the consistency ratio reflects a better more consistent response? Also, how important is the CR in the interpretation of results? If two consecutive rounds of solicited info yields very similar results, would that be acceptable even if the consistency ratio may not be good?
A: The CR in my spreadsheet is exactly the same you can find in the literature. A value less than 0.1 (10%) is good, but the threshold of 0.1 is a rule of thumb . Lower values are better than higher values, but values above 0.1 can be acceptable. It depends on the nature of your project. When you process the inputs from a group (several participants), it happens that individual CRs are above 10%, but the consolidated matrix CR is ok. Please read also my comment here.
Q: I’m very new to AHP and I want to use it to identify which country is the best location to offshore a certain function of a company for my MBA project. I need to find relative importance of different factors for such decision and the relative ranking of different ountries from those factors.
How do I use this excel for such purpose? Do I run it multiple times; first for finding the priority of the factors, and then for the comparison of the countries one by one for each of the factors? And lastly multiply the priorities of the factors by each country’s priority? Is there an easier way via your template to do it?
A: There is no easier way. My template only calculates the priorities of factors in one single category of a hierarchy. If you have different categories, you have to run it multiple times (once in each category/ sub-category); then calculate the final weighting factors and make the evaluation of alternatives in your own sheet. NEW since Dec 2013: You might also use my online tool BMPSG AHP hierarchy.
I cannot generalize my template, as the hierarchy could be very different from one to another project.
The AHP Excel template works under Office Libre and Excel version MS Excel 2013. The workbook consists of 20 input worksheets for pair-wise comparisons, a sheet for the consolidation of all judgments, a summary sheet to display the result, a sheet with reference tables (random index, limits for geometric consistency index GCI, judgment scales) and a sheet for solving the eigenvalue problem when using the eigenvector method (EVM). Latest version: 2022-07-08.
Within the input worksheets (questionnaires) priorities are calculated using the row geometric mean method (RGMM).
Three consistency indices (the consistency ratio CR, the geometric consistency index GCI and overall dissonance Psi) are calculated. The level of consistency needed (α) is implemented as a variable input field in the summary sheet, and can be set between zero and one.
If CR exceeds α, the top 3 inconsistent pair-wise comparisons on the input sheets are highlighted, to allow the participants an adjustment of their judgments. The judgment resulting in lower inconsistency is proposed.
Final priorities are shown in a summary sheet; their calculation is based on the eigen vector method (EVM). For the solution of the eigenvalue problem the power method algorithm is applied with a fixed number of 20 iterations.
Different judgment scales are implemented.
Errors of the EVM and RGMM are show beside the calculated priorities.
Either individual participants, or an aggregation of individual judgments (AIJ) based on the weighted geometric mean of all participants’ judgments can be selected.
The template does not include the hierarchy of the decision problem and the final aggregation of weights, i.e. it is only suitable for finding the weights in each category or sub-category. For the definition of a hierarchy and evaluation of alternatives see here.
Sensitivity analysis of the final result is not included.
How to use the template?
A detailed description (pdf) is attached in the download file.
When you use the template for your research, please make a reference to the author’s paper.
Please cite: Klaus D. Goepel, (2013). Implementing the Analytic Hierarchy
Process as a Standard Method for Multi-Criteria Decision Making In
Corporate Enterprises – A New AHP Excel Template with Multiple Inputs, Proceedings of the International Symposium on the Analytic Hierarchy Process, Kuala Lumpur 2013. DOI: https://doi.org/10.13033/isahp.y2013.047
A new topic was introduced just recently: “Value-driven leadership”. I started with a general article, and added a personality profiling tool (NEO-select). If interested, you might participate and send back the completed questionnaire to get a full report.
Please also be so kind, if you use parts in your web-site or blog, to give credit and a link to my site. I hope you will find some useful information, let me know your feedback or contact me directly for any suggestions.
AHP stands for Analytic Hierarchy Process. It is a multi-criteria decision making method, originally developed by Prof. Thomas L. Saaty. AHP derives ratio scales from paired comparisons of criteria, and allows for some small inconsistencies in judgments.
Inputs can be actual measurements, but also subjective opinions. As a result, ratio scales (weightings) and a consistency index will be calculated. For decision making with multiple inputs from different stakeholders, the geometric mean of individual inputs is used.
Mathematically the method is based on the solution of an Eigen value problem. The results of the pair-wise comparisons are arranged in a matrix. The first normalized Eigenvector of the matrix gives the ratio scale (weighting), the largest Eigenvalue determines the consistency ratio.
Deriving weights for a combined performance index,
Deriving a consolidated scale of importance from different inputs.
We used AHP for the evaluation of weights for the combined service index and the hotline performance index. We also applied the method to evaluate the importance of different strategies, using multiple inputs from different colleagues, and to find the ranking of financial key performance indicators for the operational performance assessment of our companies.