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.
Excel Template
-
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.
Limitations
- 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.
Reference
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
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!
Thank you very mach for this usefull software. My CR is 8.3%. Iwant to know if my data is correct or it should be repeated again?
Below 10% is very good. Input is consistent and you can use.
Thank you very much, You helped me alot to do my project.
Thank you very much for sharing this precious work of yours. I’m a translation/interpreting studies major studying in South Korea. I’m trying to compare and find weights between 6 textual elements that affect difficulty of a given text(in English and Korean). In doing so, I want to apply this pair-wise comparison method (which my advisor doesn’t really approve of..). Anyway, I deeply appreciate your work. Thank you again. Just one question:
I input 2 participants. Participant 1 showed CR of 8.x% and Participant 2 showed 16.x%. So I was a bit worried because Participant 2’s CR was too high (above 10%). But somehow the consolidated CR turned out to be quite ok (4.6%). Is this normal? i.e. Can I accept the result and use it?
No worry, I had the same in some cases. You can use the combined result.
Thanks for your feedback!
Klaus
Thank you for your assurance. I just watched the AHP video clip too and I find it extremely useful. Thanks.
thank you very much, that helped me to calculate environmental quality, i’d like little more accuracy.. regards
thank you for it. i have 4 section and 11 factor . therefore my Questionnaire is long. are you show any way . hellp me plizz
Sorry, but more than 8 factors in Excel makes the template to complex (11 factors = 121 matrix elements, 60 comparisons) Any possibility to futher break down your problem to reduce the number of factors?
How would I go about modifying the worksheet to allow me to prioritize more than 8 elements? (Warning – I am an AHP novice)
Thanks for your help.
Kelly
If you study my template, you could extend it. But I don’t recommend. Having more than 5-8 criteria makes the pairwise comparison more complex and results in a risk to get higher inconsistency index. See my video about practical application of AHP/ANP: http://youtu.be/ydKGNb4bgYY. Better try to break down in additional categories and use the template for each category.
I have to compare only two criteria at Level 2 of my hirarchial problem. SInce the worksheet has 3X3 to 8X8, how can I solve that ?
You can do a simple calculation: for 2 criteria there is only 1 comparison. If you have criteria A and B, and A is n-times more important than B, the weights are w(A) = n*w(B). Normalized: (n+1)*w(B) = 1, w(B) = 1/(n+1).
Example: A is 3 times more important than B
(3+1)*w(B)=1, w(B)= 1/4
w(B) = 25%, w(A) = 75%
Thanks.
Few more questions ..
1) with two factors, consistency can’t be found right ?
2) I have 50 respondents who compared some factors for my research, I used geometric mean response in each matrix to arrive at weight. Are there any limitations of this approach ? Any alternatives ?
ad 1) right
ad 2) no limitation. In addition you could look at the statistics of the comparison, to get some information how the spread of answers is.
Thank you …
Thank you for sharing!
Thank you very much for your videos on youtube and your excel sheet to AHP.
thank you very much, it helped me
Thank you. It is looking good and it is user friendly!