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. Is it possible to calculate the weight uncertainties in Excel using the Monte Carlo Method you’ve mentioned earlier in this thread? Also, in case of more than 1 participant where we’re using the AIJ matrix, should we reduce by the square root of the number of participants or the ‘n’th root, where n is the number of participants?

    I’ve sent you my calculations to your E-mail. I do hope you get some time to respond. My analysis is almost over. I needed some help in calculating the weight uncertainties. For a data set as large as mine with 353 respondents, do we need to calculate the weight uncertainties of each participant? Would it benefit the analysis? Would that lead to a new finding?

    Can you suggest how I could calculate the weight uncertainties of my aggregated matrix please?

  2. Is it possible to extrapolate the idea in the Excel Sheet to calculate consensus for 320 individual responses? My details in a snapshot are as follows:

    1. No. of categories- 15
    2. No. of individual respondents: 320

    Is it possible to get a result in Excel?

      1. Well, Dr. Klaus, I finally did it. I want to send you my calculation and findings if you would allow it. I wanted to know how to conduct a sensitivity analysis.

        1. Glad to hear that you did it. You are welcome to send your calculations and findings:
          drklaus at bpmsg dot com

          1. For the “Weight Uncertainties in AHP-OS”, can you give the calculations of how the weight uncertainties are calculated?

  3. I have more than 20 pairwise matrices. Would it be correct if I used 20 first then their consolidated matrix as 21st and go on with the rest?

    1. i have a similar problem, i had 30 experts responding to my questionnaire, how do i go about this

      1. use the online software AHP-OS.
        https://bpmsg.com/ahp

        Please note:
        1. Only as the project owner, you need to register and login, participants don’t need registration.
        2. Log out and refresh the browser before you input participant’s data.

        Regards,
        Klaus

  4. Hello, Dr. Klaus. May I know if it is possible to change the scale of the rating? I used Saaty’s 1980 scale wherein the intensity of importance is only from 1 (equally important) to 7 (extremely important). I was hoping I can still use this template despite the ranking scale being 1 to 9 if such scale can still be edited. Thank you very much!

    1. I was just wondering if I proceed in ranking it as 1-7, if it will affect my data.

      1. Thank you so much for your response. No worries. I tried playing around with my data and you are correct, it does not affect the hierarchy of the elements since whatever is highest no matter the scale is still recognized. Appreciate the help!

  5. Greetings, I’m using the AHP template and my PSI shows #REF. What does it mean?

    1. Hi Nayli, #REF looks like a missing excel link (reference). If you don’t need PSI, you can just ignore it. PSI shows the overall dissonance and a value greater than zero indicates an ordinal inconsistency in the pairwise comparison matrix. If I open the template, it shows “n/a”.

  6. Dear Dr. Klaus,
    My name is Levi Paye from Liberia, West Africa. I am writing my MSc. thesis on Groundwater Potential Assessment using GIS-based Multi-criteria Analysis. Based on the pairwise comparison, I would like to use the AHP Excel Template to calculate the weights for factors that control groundwater recharge. Please, I would need your opinion or advice regarding my intention. Thanks for the great work.

    1. Just go ahead. Use either the Excel template or my free online software AHP-OS (More criteria and the possibility to build a hierarchy)

  7. Is EVM (Tomashevsky) and Weight Uncertainties in AHP-OS one and the same? How are they different?

      1. Dr. Klaus

        I have been downloaded AHP Calculator but I have a problem in row 9 and 10 ” for 9&10 unprotect the input sheets and expand the question section (“+” in row 66)”. I already tried to follow this instruction but It did’nt work. There is any kind of your suggestion?
        Thankyou in advance

        1. How to help when you don’t specify the problem?
          Unprotect works without password, just key “Enter”
          Klaus

  8. Dr. Klaus Sir, does this template allow for calculation of weight uncertainties, in a manner similar to what you have shown in your AHP-OS site? Link: https://bpmsg.com/weight-uncertainties-in-ahp-os/

    The practical example does not seem to show this. I looked at it very carefully.

    Sir, has this feature been incorporated into the excel sheet? Is that the +/- value indicated in the summary sheet? How exactly do the calculations differ among the 2 (Excel sheet and website)?

    1. Yes, the template shows uncertainties as in the online software. Calculation is done in the same way as in the online software.

      1. Shri. Dr. Klaus,

        Would you be able to show this in your practical example: https://bpmsg.com/ahp/ahp-group.php?sc=bynujY

        Sir, my second query was with respect to the + & – values. In the AHP-OS, different values are shown for + & -. However for the EVM method of Tomashevsky, they are shown as a single value. How do I explain this sir?

  9. Can you also include a separate Excel Worksheet to calculate the sentisitivity analysis please?

    1. Sorry, but I haven’t planned for it. Sensitivity analysis only make sense when you have evaluated alternatives, and this is also not included in my excel template. It is only realised in my online software AHP-OS.

Leave a Reply to Kishore Thomas John Cancel reply

Your email address will not be published.

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

;