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!
When ı start the document, and select the participant 0 (consolidated) I am receiving error. Could you please explain to me what is the problem? I downloaded already the latest version. But ı could not get the consolidated results?
Thanks
Mehmet, I just tested the template again with 3 criteria and 3 participants, and its working fine. Can you check the sequence of input sheets? Is it In1, In2. In3 … or In1 and In2 reversed? In my first upload of the new version I reversed 1 and 2. If you still have the problem, please send me your filled-out sheet, then I can have a look. Regards, Klaus
Dear Klaus,
Can the problem i encountered orignate from the language of Excel? I am using Excel with Turkish language, Does Excel with English (original language) solve the problem?
Thanks,
mehmet
Dear Mehmet,
difficult to say. I don’t really believe; maybe it’s the excel version. Can you send me your filled-out template to check?
regards, Klaus
Dear Klaus,
Could you send me an email (above) so that i can send you my file to check?
I have 3 criteria, 3 participannts example and i am not geting the consolidated results. Thanks,
mehmet
Hi Klaus: the excel template does not cater to comparisons between less than three items. Is it possible to use the template in any manner so as to consolidate inputs from multiple respondents in cases where comparison is done between two items. Thanks, SRoy
Hi SRoy, unprotect the summary sheet, select cell B5, go to excel menue “data”, “data validation”, and change the validation criteria from range 3 to 10 to 2 to 10. It will then work for 2 criteria. Regards, Klaus
Klaus,
Thank you for the August 12, 2013 version of the AHP Excel template. I have question on the Summary sheet. What does the number 0.37 in cell G32 means?
Hi Rakesh,
it is used for the conditional formatting of the GCI field (Geometric consistency index): for values above 0.37 the GCI field will turn red (similar to CR turning red if CR exceeds alpha given in cell K7). Usually you don’t need it, as CR is used to judge the consistency.
regards, Klaus
This is helpful. Thank you very much.
Hi… i’m a first timer user of this tool..just want to know how to insert sub criteria for each criteria?
Hi Aida, you have to use one template for each category/sub-category. For the calculation of global priorities and evaluation of alternatives under the complete AHP hierarchy you can use my online tool BPMSG AHP hierarchy.
Okay… Thanx for your quick response.. 🙂
I have used your template and it’s great! Thank you for that 🙂
However I got results where on tabs In1-In6 I got values between 20-30 for crietion X and then on summary tab the criterion X got RGMM, which was over 30. How can it be over all the sub components as they all were equally important? Am I doing something wrong, did I misunderstood something or is there a bug?
Hi Erno,
Sheets In1-Inx use RGMM, not eigenvector. It is used to find the inconsistencies. The priorities displayed are not the final results. Only in the summary sheet the eigenvector is calculated for the selected participants, to give the AHP priority result. I never compared the two methods, so it could be that the results are different. But to be sure, I could have a look at your data, if you could send me your template.
Ok, that might be the case then – thank you for your fast reply! I just tested the sheen with Wikipedia’s AHP example case and the numbers seemed to be consist with the article.
I can send the sheet for you, but I haven’t find your e-mail address. I’m probably blind 🙂
As explained in my e-mail: all input sheets use the row geometric mean method (RGGM) to display the priorities on the input sheets. RGGM is an approximation only, therefore you can have differences compared to the eigenvector method (EV). The final EV result is only shown in the summary sheet.
Hey Klaus,
Love the template. Using it for a Sr. Design Project in school. Quick question. My numbers for the consolidated matrix keep returning “#VALUE” instead of an actual number. All other alternatives are fine but it refuses to consolidate and spit the best alternative out at me.
I appreciate any help.
Dear Charles,
Thanks for feedback. Difficult to troubleshoot w/o the data. Are you using the latest version? #value should not appear. Could you send me the actual workbook with data?
Regards, Klaus
Did you get my email?
Yes, thanks. The problem is that some references in the formulas are missing, because you deleted some of the not used worksheets. Even if not used, they are referenced to for consolidation.
Regards, Klaus
Hello Klaus,
I really lke you page, but I cannot start your template. When I open it MS office crashes. Libre Office does so too.
Do you have an idea why?
Thank you for you effort and the free(!) download.
Regards
Jean
Hi Jean,
It is the first time I get this feedback. Actually the template does not use any macros and should open without any problems. Are you using Excel 2010? I could send you the unzipped version via email, maybe its a problem of zip. Let me know, then I will email. If anyone else had this problem, please feedback.
Have used it often, never faced this problem
Hey Klaus,
With 7 respondents I have a consensus inicator of 75%. Is this a low/medium/high score and are their scientific sources to underpin this is a low/medium/high score?
Thanks in advance,
Johanneke
Hi Johanneke,
The consensus index ranges from 0 to 1, so 75% is the upper quarter, i.e. high. You can display the result graphically, then you also get an idea, how the similarity of the distribution is. The other way is to use my other template and make the cluster analysis, to find sub-groups.
Regards, Klaus
hye klaus,,
sorry if my question is out of the topic
i want to ask how can we do the pilot test or reability test for ahp questionnaire? i`m mean how to put data in the worksheet? if the questionnaire just ordinary, I can make it but for ahp i don`t know…
Hi Ergina,
The AHP questionnaire is the input for pair wise comparisons, to setup the decision matrix. There are two parameters, how you can measure the reliability: the first one is the consistency ratio CR, giving a measure how logically consistent the answers were given by one participant. Guideline: CR lower than 15% … 20% ok. The second one is the AHP consensus indicator, showing the variation between participants. Guideline: higher than 90%: high consensus, < 60% … 50% no consensus. Both parameters are calculated in the template.
Regards, Klaus
Hi,
I have priority data on 14 neighborhood characteristics divided into 4 categories that a household considers in residential location with a total of 182 households. Can I use AHP? How do I formulate my lower level? Should I take all respondents for each variable separately for ex: comparing all 182 HH responses for a vegetable market, or Should I compare all the variables in the category one for a household and build 182 tables?
Thank you,
Aruna Reddi
Dear Aruna,
difficult to answer your questions, as I don’t know the objective of your project (what do you want to find out?), and what data you have (for example: your 182 HH resonses are pairwise-comparisons? You have already the responses?)
In principle each household should do pair-wise comparison for the categories and then for all sub-categories (neighborhood characteristics). Then you could aggregates and calculate the weights (importance or ranking) of the characteristics.
regards, Klaus