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

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

  1. Hi Klaus,

    First of all, thank you for the excel template file. I’d appreciate it if you could help me with something. Current template consists of 10 criteria and 20 participants. Do you have this template for 15 criterias? I have reviewed AHP-OS, but it gives the results for only one participant. How can I obtain common results of 5 participants in a study with 15 criterias?

    Regards.

    1. Hi Mert,
      unfortunately, there is no Excel template version with more than 10 criteria. You can use AHP-OS for 5 participants. Define the project and use the Group Input Link to get inputs from your 5 participants. Just input different names for the pairwise comparisons. (You need to logout as project owner). See Section 7 in the AHP-OS manual.
      Regards, Klaus

  2. Hi,

    1- May I ask what selected participant in excel sheet means and what it is used for?

    2- Also what about Weight parameter: I understand this is to allow weigh a participants’ opinion compared to others. But is there a way to apply this weight in only some of criteria and not all of them. Does this require ANP?

    Regards,
    Yaser

    1. Hi Yaser,,

      1 – “selected participant” will show the individual result of the selected participant using the eigenvector method on the summary sheet.
      2 – It is not possible to apply the weight to only some of the criteria. Does not require ANP, but was not implemented into the excel sheet by me.

      Regards,
      Klaus

      1. Thanks Klaus, Do you see any value in my proposal? I’m trying to weight each person only on criterion which are in his/her areas of expertise. Does this have any added value or effect at all in AHP in your opinion.

        Regards,
        Yaser

        1. Yaser, my personal point of view: keep it simple. It will be difficult to justify weights for your participants; you would need to define transparent and clear criteria for their “expertise”. Actually, my (free) AHP online SW allows partial judgments, participants can submit judgments for a subset of criteria.
          Regards,
          Klaus

  3. Good day – thanks for developing this spreadsheet.
    I want to ask you one thing, can we use this excel template for 11 criteria? as it’s showing that it can be used for maximum 10 criteria. i tried to convert it for 11 criteria but all in vain.
    Can you please help me out in resolving this issue. Regards

    1. The Excel template is limited to 10 criteria, extension is too much effort. You can use my free AHP online software AHP-OS
      Regards, Klaus

      1. Thanks for your quick response.
        AHP online software is good to be used but i cannot use it for data collection purposes as their is no option for respondents to submit their response. While in case of excel sheet i can share the file with respondents and ask them to get it filled and send it back to me. Any other kind suggestion? and if you can help me out in resolving this problem.
        Regards, Nida

        1. Hi Nida,
          I have users of the online SW with up to 300 participants in their projects. You can provide a link or session code to your participants to provide their inputs. Please see Para 7 in the manual.
          AHP-OS manual
          Regards, Klaus

  4. Hello Klaus,

    My fellow student and I are working on a master thesis where we want to use the input of 16 Hospital Buying DMU’s related to a particular product.

    We expect feedback from 1-3 members per respective Hospital DMU which we I like to consolidate using AHP in a consesus AHP for that DMU. Each member will respond seperate.

    At the end we hope to have been able to create an AHP analysis of all 16 DMU’s.

    How can we enter the AHP analysis of each of the 16 DMU’s into a new AHP project to calculate the overall AHP decision making preference off all 16 hospital combined ?

    Regards,
    Frank

  5. Good day – thanks for developing the spreadsheet. Nice, clean and functional. Well done.

    I am trying to achieve a prioritization of products (ie participants), using 8 criterion. I can follow the setup of the criterion on the summary page. I see how it flows to the in tabs and populates it there. I get the A/B and scale part.

    However, shouldn’t de relative ranking of criterion not be fixed for all participants? As ultimately I want to see how each product (participant) ranks against each criterion.

    Am i missing something here?

    Kind regards,

    Marcel

    1. Hi Marcel, the spread sheet is for criteria evaluation only, not for alternative evaluation. Then you could use my free online software AHP-OS.
      Regards, Klaus

      1. Hi Klaus, thanks. I was afraid of that 🙂 Need a spreadsheet as I need to share the materials later on with client.

        If ok, i might want to embark on a project to use it as a foundation for a project portfolio prioritization tool.

        Thx

        1. Put your alternatives as criteria, and the criteria as “participants” (In1 … In8). Then you can calculate the priorities of your alternatives. You just need to do the weighted sum over the weights of all criteria.

          1. BTW: with AHP-OS you can export all data (inputs and results) as csv files for further processing with Excel.

    2. Dear Marcel

      Is it possible to do the sensitivity analysis on Excel?

      Regards

      Sarbast

      1. Sensitivity analysis makes sense for alternatives. The template only handles criteria.
        In principle and in general sensitivity analysis can be done with excel

  6. Hello
    I would like to thank you for your great application. I have a question. Can I use this app when I have subcriteria? Does this app still work?!
    I would really appreciate it if you answer me.
    Thanks.
    hadikatoly@gmail.com

    1. Hi Hadi,
      the online software works with complete hierarchies up to 6 levels. The excel template for one category only.
      Klaus

  7. hi
    I Am doing a GIS base research and i need to use paired comparison in order to rank (weight) the 4 criteria i use.So i try a example here with 2 participants and download the CSV file and analyze. so can someone tell me what it is mean by
    ” Consensus 0.720973″ and which row in the excel gives me the final weights for my 4 criteria ??
    thanks

  8. Hi, I’m really grateful to you for the template you’ve provided as it could at least help me out with my Ahp Calculation. I did some adjustment to it as I have 26 respondents (the template was only meant for a max of 20). I am only focusing on the prioritization of 6 factors. AHP is a new method for a research in my field of study (education). So, it was a bit confusing for me to understand it and to convince others as I am still trying my very best to understand it through readings and to make it relevant to my study. I am also comparing the result with the one I get through Expert Choice v.11. The ranking results are the same, except for the percentage of each factor. However, my ultimate goal is to rank the factors accordingly and your AHP Excel Template helps. If only I could get a version of your template with 26 respondents (more than 20)….rather than using the adjustment version (my own manual copy & edit version)…it would be a great help.
    Thank you.

    Roselila
    University of Malaya

    1. Rosella, thanks for your feedback! Just use my online software AHP-OS. It’s also free and you can handle a practically unlimited number of participants and also complete hierarchies and alternative evaluation. Just need to register once with your e-mal.
      Regards, Klaus

      1. Hi,

        How do I add participants? It seems like I can only submit one questionnaire with one account. I have 80 participants. Thanks.

        Regards,Edward

        1. Hi Edward,
          the excel template can only handle 20 participants and one level of hierarchy. If you are talking about my online SW, the number of participants is practically unlimited. Participants don’t need to be registered users, they just have to click on the link you provide. Please read my post about group decision making here.
          Klaus

  9. Thank a lot for sharing this great AHP calculation template for free. I’m an urban planner from Tehran (Iran) and I’m currently working on a neighborhood regeneration project with a consulting firm and would like to use this template to collect inputs from stakeholders and weight our development criteria.
    In one case, we have 10 criteria. When I try to include them all in the excel template, I found out that people get confused when criteria number 9 and 10 are at the very end of the comparison table. I tried to change the criteria table and include 9 and 10 with the other criteria (j=9 and j=10 going right after j=8), but I failed – it’s too complicated for me to make that change. I was wondering if you have any suggestions or advice for me about how I can change that part.
    Many thanks in advance.

    1. Yes, you are right, it’s a bit confusing. I only extended to 10 criteria because of requests from some users.
      The whole excel workbook is quite complex, and I do not recommend to change it. My recommendation: use my AHP online SW AHP-OS. You can handle up to 15 criteria and it is designed for group inputs. Much more user friendly than the excel sheet for complex projects. With the latest version all input matrices, aggregated matrix and priorities can be easily downloaded in csv text file format and imported into excel.
      Hope this helps.
      Regards, Klaus

      1. Many thanks for your fast reply. Your AHP-OS is very helpful and user friendly, but unfortunately we won’t be able to use it. Since our client is a governmental one, all the text we use need to be in Farsi (Persian language)… 🙁
        Thanks anyways.

          1. Yeah I tried. When I try to make a hierarchy with Farsi font, it doesn’t recognize the nodes.

  10. Boa tarde.
    Estou utilizando a sua ferramenta, a qual está me auxiliando muito. Porém tenho uma dúvida. Estou utilizando 6 critérios apenas, no entanto notei que o cálculo de normalização não bate os valores quando faço na mão e então percebi que os cálculos da sua planilha, aba 10×10 que não altera os valores referentes ao número de critérios. Ela está calculando para 10 critérios, mesmo que colocando no inicio o valor n = 6. Eu tentei alterar isso desprotegendo a planilha, porém confesso que não entendi muito bem os cálculos. Isso de fato é uma falha ou eu que não estou sabendo utilizar a ferramenta?
    Grata.

    1. Unfortunately I don’t speak Portuguese …
      The 10×10 calculation algorithm is always done as 10×10. Regardless of the number of criteria the result should be correct for your actual number of criteria. Question: Are you using the latest version of the template? Otherwise please update to the latest version. Let me know, if there is a discrepancy in the calculated priorities depending on the number of criteria. It should be correct and was tested with different examples from the literature. English please.
      Regards, Klaus

Leave a Reply

Your email address will not be published.

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

;