New AHP Excel template with multiple inputs

The AHP template works under Windows OS 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: 2017-10-11.

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).
  • Two consistency indices (the consistency ratio CR and the geometric consistency index GCI) 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 12 iterations.
  • Different judgment scales are implemented.
  • 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 is given in the pdf document here: AHPcalc-v2017-03-29.pdf

Reference

Please always make a reference to the author and website.

Download

Download AHP Excel Template Version 2016.05.04 (zip) download

Please consider a donation, it will help me to maintain the website and program. At least rate the template from 1 star (poor) to 5 stars (excellent) below. 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!

New AHP Excel template with multiple inputs
4.7 (93.33%) 18 votes

Incoming search terms:

  • ahp model excel
  • ahp calculator excel
  • ahp questionnaire example
  • ahp questionnaire template
  • ahp questionnaire format
  • ahp questionnaire sample

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

  1. Dear Klaus,

    I conducted an AHP with 19 participants using your great excel tool. Now, I am wondering why the overall C.R. is different to the average of each individual C.R. of the 19 participants?
    Can you explain to me why there is a difference and if it has to do with the process of normalization. Are more iterations needed by an increasing number of participants?

    Kind regards,
    Jan

    1. Dear Jan,

      CR is calculated from the consolidated decision matrix (dm). The elements of the consolidated dm are the geometric mean of each participant’s matrix element. It is not the average of all individual CRs. CR of the consolidated matrix is always lower than CR of the individual matrices. I remember an article, where this was shown mathematically.
      The number of iterations does not depend on the number of participants, but on the consolidated dm.

      Hope, this helps.
      Regards, Klaus

      1. Dear Klaus,

        Thank you. Yes, this helps! But, why is the CR of the consolidated matrix always lower than the individual CR?
        Another question: Is it possible to check the transitivity of the pairwise comparisions of each participant?

        Kind regards,
        Jan

        1. Please have a look at: “An experiment on the consistency of aggregated comparison matrices in AHP” by R. Aull-Hyde et al., European Journal of Operational Research 171 (2006) 290–295: … given a sufficiently large group size, consistency of the aggregate comparison matrix is guaranteed, regardless of the consistency measures of the individual comparison matrices, if the geometric mean is used to aggregate.

          Unfortunately, I don’t have implemented any algorithm to check transitivity of the p.c.

          Regards, Klaus

  2. Dear Klaus,

    Thank you for that great Tool. I used it for a questionnaire recently.
    Now, a second questionnaire will be conducted and I have 12-16 alternatives. Is there a possibility to expand your tool to 12 or 16 alternatives?

    Kind regards,
    Jan

    1. Dear Jan,
      12 is now possible. Actually, the restriction it is not so much a question of the software capability, but a questions of the method. Please see my post here. It does not only apply to the criteria, but also to the alternatives. In my work I use sometimes another method for alternative evaluation, if the number of alternatives is much more than 10. Please see also my answer on research gate here.

      1. Dear Klaus,

        Thank you for your answer.
        I think, I found a way to handle 12 alternatives. I will perform one AHP for my 4 main criteria/alternatives and 4 seperate AHP with 3 sub-criteria/alternatives each afterwards. After this, I can correlate the weights of the sub-criteria/alternatives to the main criteria/alternativs. This should work.

        Kind regards,
        Jan

  3. Thanks for the software.
    Now that l have it, i only need to look for data to insert into it.
    Want to modify it so that it becomes compatible with tender evaluations.
    Thanks in abundance.

    Let me get to it

  4. Dear Klaus,

    Your template doesn’t calculate the Consensus Index for some scales. Is it ok?

    1. For some reason Consensus indicator is calculated for linear, inv linear and balanced scale only. You can change by changing the formula in field O7 of the summary sheet (unprotect first). Just add the scale number “K5=n” in the part OR(K5=1; K5=4 …). I don’t recall the reason, why I excluded the other scales.
      BTW I detected a small bug: field O5 needs to contain the formula: “=LOOKUP(K5;R2:X2;R3:X3)” to display the correct scale name.

  5. The template is really useful for the studies. But maybe you could recommend where I could find a template for Fuzzy AHP with triangle fuzzy numbers? I would really appreciate it.

  6. how can i add the participant to became 53? because in your excel template it it limited to 20 participant only..

  7. Hello,
    I am thinking of using AHP in my PG dissertation. Want to ”rank the impact” of facility location and sustainability on supplier selection decisions. I will be having about 30 criteria in my survey questionnaire. In one of the replies you stated that AHP online SW can be used for more then 10 criteria. I can’t find it on the site. Can you please attach a link?

    Can you also provide me some tips s to how to get better results? Like should i consolidate those criterion in small sub groups? Maybe something else you can think of?
    Was really scared as to how i will proceed with AHP now feeling much better. Thank you 🙂

    1. Dear Sunchit,
      it is advisable to group your 30 criteria in small subgroups so that the number of criteria in a sub group is between 7 and 9.
      My online software: AHP-OS

      1. Hello Klaus,
        why between 7 and 9? Is this a general recommendation regarding fault tolerance?

        Thank you in advance. By the way: great tool!
        Greetings

Leave a Reply

Your email address will not be published. Required fields are marked *

*