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

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

Ali says:

2013-02-20 at 12:54

Hi, thanks a lot ..I’m new in using Ahp. would you please explain about the “selected participant”… what does it mean?

Reply
1. bpmsg says:
  
  2013-02-20 at 13:59
  
  Hi Ali, when you have a group of experts, stakeholders or decision makers doing the pairwise comparisons for the same set of criteria, then each of them is one participant. I number them from 1 to 10, and the selected one is the one, whose result (priorities) will be shown in the summary sheet. Selecting “0” shows the consolidated result of the whole group.
  
  Reply
Rick says:

2013-02-19 at 04:35

Within version 08.02.13, the weights beside the criterion in the “Table” do not show weights other than 0 for selection of n>3, the initial value. The formula is present in the cells (=O41, O42…), and the data is available within the Eigenvector column, however it does not display. I have made the change in both protected and unprotected modes, but the error persists.
Thanks, Rick

Reply
1. bpmsg says:
  
  2013-02-19 at 07:53
  
  Rick, thanks for your feedback. Unprotect sheet summary. Select weigths (O18:O27). Click “conditional formating”, “clear rules”,”clear rules from selected cells”. Then the values will be displayed correctly, and you can format them in the way you want. It is a strange effect, only appears on one of my PCs, on the other it works fine. I uploaded a modified version, but not sure whether it works for everyone.
  Regards, Klaus
  
  Reply
Zehra says:

2013-01-06 at 00:45

Hi,

I’m trying to understand the logic of eigen values, power method, the iterations in your excel (version 11.12.12) and I was wondering if you may help with below:

1. Do you have any recommended sites that explains those concepts easily to a math naive?
2. In 8×8 sheet, during the first iteration, why the row totals are always divided by 8, although there may be less number of participants?
3. In 8×8 sheet, although I have 4 participants (though 4×4 in my m_summary matrix), the empty cells (between replies of participants 5-8) also get values during iteration, scaling, normalization etc.. (in row K) Is this normal? After 12th iteration, they become zero, but may it impact the value of others?
4. How do you find the Random Index?

Thanks a lot for your support, your sheet is really useful

Reply
1. bpmsg says:
  
  2013-01-06 at 15:38
  
  Hi Zehra,
  thanks for your feedback!
  1. You find a lot of material in the net, when you search for “power method”. As one example I gave the link here: http://college.cengage.com/mathematics/larson/elementary_linear/4e/shared/downloads/c10s3.pdf
  2. I treat the Eigenvalue problem always as an 8×8 problem, regardless of the actual number of criteria. (as all other elements – except the diagonal – are then 0) Apparently it does not change the principal eigenvalue. Maybe one of my visitors can explain it in a good way.
  3. The diagonal elements are “1”, therefore you get initial values, but – as you said – they converge to zero. It has no effect on the result, as you see in the check calculation. Anyway, if you only have 4 criteria, you can easily modify the calculation sheet and reduce to 4×4.
  4. The random index comes from the original paper. As I understood it is based on randomized input using the method.
  Hope this helps. Regards, Klaus
  
  Reply
  1. Zehra says:
    
    2013-01-07 at 03:22
    
    Thanks a lot Klaus,
    I will use AHP for finding ideal outsourcing vendor for my MBA project, in a similar way to below paper. Your excel is very useful and handy to implement it. Thanks again.
    
    http://smgapps.bu.edu/smgnet/Personal/Faculty/Publication/pubUploads/wp2006-10.pdf?wid=1770&did=685&Filename=wp2006-10.pdf
    
    Reply
    1. S Roy says:
      
      2013-02-14 at 14:53
      
      Hi Zehra:
      
      Stumbled upon the following link: appeared similar to your area of interest
      http://www.faqs.org/patents/app/20120010925
      
      Rgds,
      
      Reply
ashay prabhu says:

2012-12-06 at 20:41

Mate, I take my hats off to you !
Great tool and very applicable in asset management prioritisation process.

Reply
Alvaro says:

2012-11-27 at 23:48

A very useful tool indeed…… Thank you for sharing it…

Reply
Harris says:

2012-11-05 at 21:35

Overall, one of the most complete worksheets seen. Great job, well done.

Perhaps you may add:

1) a reset button which actually resets all number or inputs
2) a bit more accuracy (perhaps a couple of digits)

Choice Factor A Factor B Factor C Factor D Priority Your S/W
Factor A 1 2 5 7 0.5232 0.54

Factor B 1/2 1 3 5 0.2976 0.28

Factor C 1/5 1/3 1 3 0.1222 0.1

Factor D 1/7 1/5 1/3 1 0.0570 0

lamda (max) = 4.069
CI = 0.02

Your software:

lamda (max) = 4.038
CI = 1.4

zero in cell 024 but 7.3% in M43

also in tab multInp, based on the above numbers M12 should be 1/3, not 1, in any cause it’s seems wrong.

Reply
1. bpmsg says:
  
  2012-11-06 at 08:13
  
  Harris, thanks for your feedback, appreciate. I tried to reproduce your figures in my template with the same inputs, but I see 7% in cell O24 (rounded from 6.6% in cell M43 – as it should be – not zero). Also in the tab multInp cell M12 is 1/3, not 1, as you describe. Eigenwert is 4.077 and CR 2.8%.
  In the summary sheet I set n=4 (4 criteria), N=1 (1 participant), p=0 or 1 (consolidated result or result of participant 1)
  The priorities (Eigenvector) are 52%, 30% , 12%, 5.8% in the first estimate (no iteration) converging to 54%, 28%, 12%, 6.6% as result after 6 iterations. (You may see it in the tab ‘8×8’).
  Did you use the latest version Oct 16th of the template? Maybe you could send me your excel with your actual results, so that I can double check.
  Thanks, Klaus
  
  Reply
  1. Harris says:
    
    2012-11-06 at 18:29
    
    Klaus, thanks for your prompt reply. You are absolutely correct, apologies.
    
    I had forgotten to input “A” in the more important tab (Input 1) when comparing factor C with factor D. This had as a consequence in the tab multInp cell M12 not to appear as 1/3.
    
    Now it’s ok.
    
    Reply
Edward says:

2012-10-29 at 03:53

The Excel template is a good teaching tool and it is very useful in practical application. Thank you also for providing a practical explanation regarding how to use and interpret the Consistency Ratio (CR). Can you provide an explanation for how to use and interpret the Eigenvalue (lambda)?

Reply
1. bpmsg says:
  
  2012-10-30 at 08:23
  
  Edward,
  the principal Eigenvalue (lambda) is used to calculate the consistency ratio CR (CR = (lambda-n)/(n-1)/RI). RI is a statistical random index, resulting from random pairwise comparison imput simulations. In the ideal case (no inconsistency) lambda = n, and therefore CR = 0. You can do a simple sample calculation for the case of two criteria (n=2). With two criteria there is no inconsistency possible, and lambda = n = 2.
  
  Reply
VBA says:

2012-10-22 at 09:55

Thanks for sharing your great Excel. We use AHP in Brazil too.

Reply
Pascal Brokmeier says:

2012-10-17 at 19:59

Error in file: If you want to have more than 3 people participating it messes up the calculation.
Solution:
On summary sheet in m_summary add more if(B9=4;m_p4;if(B9=5;m_p5;….) so it will be till 7. you stopped at 3 for some reason.

Reply
1. bpmsg says:
  
  2012-10-17 at 20:57
  
  Pascal,
  thank you so much for the feedback, I really appreciate. The bug results in the incorrect display of weighting factors for individual participants 4 to 7, though the consolidated result for more than 3 participants was correct. I have updated the template and uploaded it as version 16.10.12.
  Thanks again for your help,
  Klaus
  
  Reply
  1. Gavin says:
    
    2012-10-24 at 08:50
    
    Hi, firstly, well done this is a fantastic tool. I have been scouring the net for something like this to show my colleagues since I witnessed an AHP presentation at a conference. One question I have though is in my demo of this model to my colleagues we had 4 inputs and 7 criteria however on the summary page it only shows a weighting for the first three elements, is this normal? Becuase we can’t see the weighting of the remaining 4 elements
    
    Reply
    1. bpmsg says:
      
      2012-10-29 at 14:57
      
      Gavin,
      did you download and use the latest version (16.10.12)? In the previous versions there was this bug for more than 3 participants. If you have the problem with the latest version, please send me your excel file, then I can check.
      
      Reply
Dcell_1t says:

2012-08-21 at 12:45

Hi, thanks so much for the template… It was very helpful.

I didn’t used the geometrical mean for multiple users, I will put everybody on a meeting room and fight until everybody agrees on a number.
What I made different is to use a scroll bar to modify the number on the comparisons, it is the same but only looks more fancy.

thanks!

Reply

Excel Template

How to use the template?

Reference

Download

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

Leave a Reply to VBA Cancel reply