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 - (476 votes)

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

  1. Dear Prof. Klaus

    This is such a helpful template. I am just wondering what would be an appropriate method to aggregte matrices of different number of criteria ? (with each person generate different sets of 4-6 criteria)

    E.g.
    Person 1: Criteria A B C D E
    Person 2: Criteria B D E F
    Person 3: Criteria A B C F
    etc

    Thanks in advance
    Gen

    Reply

    1. Dear Gen,
      didn’t think about this case. Usually I would try to structure the problem hierarchically and give complete branches (categories) to selected groups of participants.

      Reply

  2. Prof. Klaus,

    Thanks a lot for your help, I only want to ask if i can extent the criteria to 16
    as my research has 16 criteria and its shame that i could’t use you perfect templet

    Reply

    1. Dear Modhi,
      kindly read my latest post about the number of criteria here and also here. I strongly recommend not to exceed 10 criteria; it is better to break down in further categories, whenever possible. Nevertheless my priority calculator and software now works for 16 criteria (120 pairwise comparisons!). The excel template is limited to a maximum of 10 criteria.
      Regards, Klaus

      Reply

  3. can ahp to ranking 100 until 500 objek?
    like make a best employee of the mount.. and the subjek 100 until 500 employee.. i am sorry for my bad language

    Reply

  5. Dear Prof Klaus:

    I am doing a research on consumer preferences for Grocery Retail Outlets. I sent out 200 questionnaires to respondents. I received 137 completed questionnaires. My question is, how do I use the geometric mean to combine or aggregate the different scales from the different respondents into one group?

    Thank you.

    Geminice

    Reply

    1. Dear Geminice,
      add-up the logarithm of all individual matrix elements, divide by the number of participants, then take exp to get the consolidated matrix with the geometric mean of the elements. (You need the logarithm because of the size of resulting numbers):
      aij (consol) = exp( SUM [ ln aij] / k)
      k is the number of participants

      Reply

      1. Dear Prof Klaus:

        You’re appreciated. I will try it and see what happens.

        Cheers!

        Geminice

        Reply

  6. Prof Klaus,

    Can i know how did u calculate the consolidate matrix in the summary sheet? is there any formula? I have two sub criteria but your template didn’t accept 2 sub criteria input, so i have to do that manually. For your info, i want to calculate from 3 respondents input from pairwise comparison.

    Thanx.

    Reply

    1. Dear Aida,
      Please see my comment here. Then you can use the template for 2 criteria. If you want to do it mathematically, you have to calculate the consolidated matrix by taking the geometric mean of each matrix element of your three participants: (aij1 * aij2 * aij3) ^ (1/3)
      Regards, Klaus

      Reply

  7. Prof. klaus,

    Thanx for this amazing excel template.I just want to ask is there any other software to do a complete AHP project evaluation online.
    thanx in advance…….i will wait for your reply.

    Reply

        1. Not in my excel template, but using my AHP online system, you can export the results to excel. They will include local and global priorities. You can handle complete AHP projects. Just read my post here, or download the description from there, and try it out.
          Regards, Klaus

          Reply

          2. Prof. klaus,

            I have to input criterias with sub-criterias in your single AHP excel template.

            I have six criterias with 25 subcriterias. how to solve into a single AHP template so that i will get weight of each and every criterias with their sub-criterias.

          3. Dear Ranjeet,
            I don’t have a solution in one single Excel template. Use one for your 6 main categories, and additional ones for your subcategories. Once you have the priorities, you can still use my online solution to calculate global priorities, evaluate alternatives and re-import in excel.
            Regards, Klaus

  8. Prof Klaus,

    What if my CR value = 0.0%? i have 3 criteria and 2 of them has equal important… is it ok?

    Reply

  9. Hello,

    I Just want to share a possibility to measure alternatives with criteria in your Excel template. Exchange participants with criteria and criteria with alternatives and you perfectly have your measurement. Now every participant has to fill out his own excel, but this is usually easier. Thank you for sharing this Excel template. My slightly personalized version will be my new base of decision making.

    Best Regards,
    Patrick

    Reply

  10. Prof. Klaus,

    Thanx for the updated template. Just wondering, after we insert all the values that we received from respondents in the Excel and we get the proposed intensities because of the CR value is more than 10%, is it we just simply change the value by ourselves, or we have to come back to respondents to re-evaluate those criteria to make it consistent?

    Thanx in advance.

    Reply

    1. Dear Aida,

      you should not just change the values by yourself, then it would be a manipulation of the respondents inputs. You might modify to see the effect on the priorities, but still use the original data. You can actually accept CR > 10%. Ten percent is a rule of thumb; even 15% could be still OK. If higher, ask the respondents to re-evaluate.

      regards, Klaus

      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.

;