New AHP Excel template with multiple inputs

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!

4.7/5 - (477 votes)

331 thoughts on “New AHP Excel template with multiple inputs”

  1. Hi Klaus,

    I do appreciate your work as it seems to be the only available free tool on the web. I am doing my master thesis and need to analysis ranking of choices for 65 people. It would be really helpful if you can share with me the formula you use for the multiple participants. I tried to use online version of the software but it is a bit time-consuming.

    Also, I need your advice. Do you think it will be more precise to consolidate only the results of participants whose CR is less than 0.1

    Thank you for sharing your time, and your passion.

    Reply

    1. Dear Samir,
      all formulas are shown in the pdf description. To calculate the consolidated decision matrix, the geometric mean of all individual matrix elements is calculated. (pg 6, section D).
      You could use 4 templates (20+20+20+5), and then copy the values of the consolidated matrix of each template (from the summary sheet) into a 5th template to the “multInp” sheet (under participant 1 to 4). Then you would get the total consolidated result of all 65 participants.

      Regarding your second question: I would not take out participants with CR >10%. Ten percent is a rule of thumb only, please see my post here. Once you do the consolidation, anyway the averaging will reduce CR significantly.

      Regards, Klaus

      Reply

  2. Dear Klaus,

    first of all thank you very much for sharing this free version.
    I wrote my last paper with the help of your software and I have to mention that the handling is very easy and self-explaining.

    In the next months I have to write my Master-Thesis and I want to combine a AHP Evaluation with interviews of experts but the problem is that research group for the AHP have to be larger then 20 people (according to my Prof.).

    Is there any possibility to extend this template? Because I tried it by myself but the result was very sobering.

    I also have looked for other free softwares but I don´t find any template like this……

    Maybe you have any suggestion or Tip?

    Thank you in advance for your comments.

    Beste Grüße aus Deutschland,

    Julian

    Reply

    1. Dear Julian,
      please see the comments here and here.
      An alternative would be to use my AHP online software. Once registered (free), you can manage different projects, and the software has no limitations in the number of participants. All results can be downloaded to excel for further processing.
      Gruss aus dem tropischen Singapur,
      Klaus

      Reply

  3. Lieber Klaus,
    Vielen dank fur sharing the knowledge with all of us… Till watching your video on youtube, i was struggling to figure out some parts but now all is clear… it is time to work on excel 😉
    Alles Gute and God bless u!,
    Çağrı,
    PhD Candidate and Researcher, TURKEY

    Reply

  4. Dear Prof. Klaus,

    First I would like to thank you for this amazing excel template. It’s been so helpful.

    My problem is I can’t seem to get the consolidated weightings working. After putting all the inputs, when I choose p =0, I keep getting an error message like “#REF!” in the related cells.

    Kind regards,

    Melih

    Reply

    1. Dear Melih,
      are you using Excel in the English version? We had this problem once, and it seemed to be a problem in the regional settings of Excel or Windows. Please see here. You could send me your template, and I could test it on my PC. Unfortunately, last time we were not able to resolve the issue, as it was working on two different PCs here.
      Regards, Klaus

      Reply

      1. Ok, I’ll try to run the template on another computer where a different version of MS Office is installed.
        I’ll keep you posted about the results.

        Thank you.

        Reply

        1. I tried the template on another computer and it worked.

          Thanks again for your reply.

          Reply

          2. melih bey,
            aynı sorunu bende yaşıyorum p=0 olduğunda hata veriyor bu sorunu nasıl çözebilirim teşekkür ederim.

  5. I would like to ask you what mechanism is used to identify inconsistent points and propose corrections?

    Reply

  6. Dear prof. Klaus

    Thank you for your Excel tool. I need to use the tool but I have about 26 participants. How could I change the tool to become useful in this problem.

    Thank you

    Reply

    1. Please see my comments here and here. You might also use my online software instead of the excel template, there is no limitation on the number of participants.

      Reply

  7. Dear Prof. Klaus,
    I would like to thank you for your amazing excel template for AHP. It is useful and helpful.
    Alireza Mohammadi

    Reply

  8. Dear Klaus

    First I would like to thank you for your amazing excel template for AHP. It is useful and helpful.

    I have built my own excel sheet because I have 1 objective, 2 criteria and 3 sub-criteria for each criteria. In every sub-criteria I have from 2-5 sub-sub-criteria. I have about 20 participants

    I have some questions
    1- Do we need to do CI and CR for every participant and for all participants?
    2- How to do them?
    3- I have done a random test to measure CI and CR and surprisingly CI was (-1.20) and CR was (-1.07). Is this possible that CI and CR minus? or do you think there were mistake?

    Again thank you for your help
    Abdul

    Reply

    1. I recommend to calculate CR for each participant in order to see how consistent is his/her judgment.
      Calculate CR according the formula given by Saaty, or use the linear fit given by Alonso/Lamata. You can find more info in my postings and description of the template/software.
      λ should be GT n for inconsistent matrices. CI cannot be negative. Probably there is a mistake in your calculations.
      You can use my online software for complete hierarchies and 20 or more participants.
      Regards, Klaus

      Reply

  9. Greetings from Turkey!!!
    Dear Prof. Klaus,

    I am truly delighted that you provide this service for free.
    I am very appreciated and glad to see such people around the world.
    If you ever visit our country, especially Cappadocia Touristic Destination, I would love to accommodate you in my home and thank you.
    I use your program for my Phd thesis which is about strategic destination management.

    Best Regards….

    Reply

    1. Dear Emre,
      thanks, I appreciate. Have been in Turkey (Istanbul & Ankara), never Cappadocia. Hope the software can help you in your thesis.
      regards from Singapore, Klaus

      Reply

  10. Dear Prof. Klaus,

    I cannot thank you enough for the amazing website and great tools! Your effort is highly appreciated.

    I have 15 respondents and I have inconsistency in ALL of them, however, the overall consistency ratio of the consolidated result is 4%. Is this normal?

    PS Can I send you my results?

    Best Regards,

    Mohamed Ragab
    Researcher, Dublin Institute of Technology

    Reply

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

;