Thanks to the feedback from Pascal a bug is corrected, when you select the results of individual participants in the summary sheet. Selection/display of results from participant 4 to 7 was not shown correctly.
Please go to the AHP template download page to get the updated version.
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.
This update corrects a bug in the calculation of the consistency ratio CR. In the 8×8 sheet the correct random index is now selected from the table depending on the actual number of criteria.
Please go to the AHP template download page to get the updated version.
Version 12-08-13 of the template allows for decimal input figures in the pairwise comparison sheets. It is now possible to enter a decimal value between 1 and 9 instead of integers 1 to 9. This simplifies the combination of templates, in case you need to extend to more than 20 participants.
This latest version also handles the different judgment scales for non-integer inputs.
Kindly let me know in case you find any problem with this new version. Feedback is appreciated always! You can download this latestes version from my AHP template download page.
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.
Alternative for complex AHP projects: AHP free online software.
Limitations
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
Download AHP Excel Template Version 2022.07.08 (zip)
Please consider a donation, it will help me to maintain the website and program. An explanation of AHP (Analytic Hierarchy Process) is given in my video here. For terms of use please see our user agreement and privacy policy.
Your comments are always welcome!
Thanks to all visitors and viewers of my AHP video. Views exceeded 10000, quite a lot for such a special topic. I hope, you all enjoyed and could take something usefull out of it.
Here the Youtube link to this AHP video
Another practical application for ANP is in preparation: The Marketing Mix. Will still take some more time, as ANP is more complex than AHP.
Currently I am working on the leadership topic, still trying to get more people to try out the personal inventory.
You will receive a full report free of charge when sending the file back to me. (bpmsg at bpmsg dot com)
For those, interested in nature: have a look at my sunbird video, its in HD and some of the shots are really nice in quality.
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.
An explanation of the method can be found in my video on Youtube.
An Excel sheet, doing the calculation for up to 10 criteria, can be found in the download area. Download AHP slides.
AHP can be applied in many fields:
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.
Here you might download the slides of my video, explaning the analytical network process ANP.
Download Slides 
The analytic hierarchy (AHP) and analytic network process (ANP) are two multi-criteria decision methods (MCDM), originally developed by Prof. Thomas L. Saaty.
ANP is a more general approach, based on the description of the problem by means of a network instead of a hierarchy as in AHP. On the other hand, ANP is also more complex in its application.
In my latest video presentation, pros and cons for both methods are shown, and a few tips for the practical application of AHP, and setting up a network for ANP are listed.
Download the slides
Link to video in Youtube
Feel free to comment and feedback here!
