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!
Thanks BMPSG for the free download. I will give you more feedback after using it. Again thank you very much
Peter
Klaus,
I’ve found a bug on the template.
In the worksheet “In4”, the consistence acceptance always is 0.3, it’s not referenced to “=Summary!K7”
Besides this, I think everything is okay.
Like I’ve said before via email, thank you for sharing this outstanding work
Gabriel.
Hi Gabriel,
thanks! I just corrected it and also found that – on some of the input sheets – cell F17 (weight of individual participants) was actually locked in protected mode. It’s also corrected.
Regards, Klaus
sorry if I ask this question, but I`have been confused!!! I want to know after complete the sheet , where it shows the best participant?!
Afsane, there is no “best participant”. You select the participant (= persons doing the pair-wise comparison) in the summary sheet under “p=” selected participant. If you select “0” you will get the consolidated judgement of all partcipants. If you are the only person to fill out the pair-wise comparison, then you have only 1 participant. “p=1” and “p=0” is the same.
so I should compare criterions result (p>0) with consol (p=0) and select the participant who has better and the most similar criterions with the consol ?! I find out the participant with this situation is the participant who has lowest CR ! Isn`t it?!
Either you use the priorities of the whole group (p=0, the “group result”), or you just look at individual participants. CR is a measure, how consistent a pairwise comparison was done. Too high CR could result in rank reversals. Therefore it gives you some information, how reliable the outcome (priorities) are.
thanx a lot for details and more information 😉
best regard
Dear Klaus,
I decided to write this question to the wall here instead of writing you an email. So you cna perhaps share your knowledge with more people.
Thank you so much for making the tool accessible for interested people and thank you for writing down these very good instructions.
I have one question, which did not become clear to me while reading the manual. It is about the Consistency acceptance level in field K7. The excel file says that alpha=0,1 is Saatys suggestion. Now I do not know what alpha is supposed to be. As far as I know Saaty has suggested the accepted concistency level to be 10% (factor o,1) of the mean inconsistency of a random filled matrixes (which depends on the sive of the matrix). For a 5×5 matrix this would be e.g. 1,12 x 0,1= 0,112.
So is the consistency acceptance in field K7 the percentage of the mean or is it already the concistency threshold?
Thanks in advance.
Jan
Dear Jan,
The consistency acceptance in K7 is simply the threshold for the consistency ratio CR. So instead of using Saaty’s rule of thumb of 0.1 for the threshold you can increase to 0.15 or 0.2.
Regards, Klaus
Just for further clarification: CR is the consistency ratio, i.e. consistency index (CI) over random index (RI). Therefore the size of the matrix is already reflected, as RI varies with the number of criteria. Please also see in the annex of the template description.
Hello,
Thank you for your detailed instructions on adding participants and updated spreadsheet. I was finally able to add a few more participants after learning about array formulas.
As I am a step-by-step person and don’t have a full understanding of the formulas used, I’m wondering if I need to adjust the 10×10 tab that is referenced for the normalized principal Eigenvector? Currently, we have 12 participants and when I add the information for any beyond the 12 the weights don’t change on the Summary tab.
As we will be using the weights that come from our 4 criterion for the next step in our process, I want to ensure that the data is correct.
Any help you can provide would be greatly appreciated.
Thank you!
There is no change in sheet 10×10 required. But one important step is to modify the calculation of the geometric mean in sheet multInp:
Mark B9:K18 (consolidated matrix) and modify the formula ={(M9:V18*B22:K31* …*B74:K83)^(1/N4)} to include the added participant’s matrixes. Sorry, forgot to include in my step-by-step instruction below.
Regards, Klaus
Thank you so much for all your help, that adjustment seemed to do the trick!
Dear Klaus,
I have a problem in expanding the program to include 70 participants. If you could help in providing a modified program or a copy/add procedure, it will be very much appreciated.
Regards, Athikom
Hi Athikom,
not sure, whether it makes sense with 70 participants. Each participant = 1 worksheet, and I don’t know the limitations of Excel. I used 20 max in the past. Modifying:
1. Unprotect sheet In10; create a copy of the sheet In10 and rename to In11.
2. Go to “Formulas – Name Manager” and delete name “Matrix10” with scope In11.
Mark matrix cells of the decision matrix in In11 (C79:L88) and define new name “Matrix11” with scope workbook.
3. Go to Sheet multInp, unprotect sheet.
Add additional matrix, e.g. copy/paste from matrix 10
(2 matrices per rows, same structure as for matrix 1-10)
Mark content cells of new matrix and define new name “m_p11”
Set it {=Matrix11} (array, see below)
4. Go to Summary Sheet, unprotect sheet.
Mark matrix starting at line 38 and add new matrix m_p11 in the formula:
{=IF(p_sel>0;CHOOSE(p_sel; m_p1; m_p2; … ; m_p10; m_p11);MatrixC)}
Select field C7 (number of participants)
Data – Data Validation: change range from 1 to 10 to 1 to 11.
Continue in the same way for additionla participants.
{} = array input – mark cells and use Ctrl-Shift-Enter
All matrices in the input sheets are named “Matrixn”, n = 1 to max. number of participants.
The matrices in the multInp sheet are named “m_pn”.
Dear Klaus,
Thank you very much. Your step-by-step instruction is very clear. I do appreciate your help. In addition, I noted your comment on the 70 participants. Is there any theoretical limitation (AHP)?
Regards,
Athikom
Dear Athikom,
there is no theoretical limitation, just a practical one. Usually you gather the inputs for important decisions from the main stakeholders and subject matter experts, and they are limited in number … If you have a big group of decision makers, it also make sense to analyse the degree of consensus. I have developed an AHP consensus indicator for group judgements based on Shannon entropy. Could be interesting for your application.
Dear Kluas,
Your advice is appreciated.
Hello, im doing my master thesis using AHP. my Professor suggested me to use AHP software to analyze the data but i have no idea where i can download the software for free and another thing is that im having trouble calculating consistency ratio.
Iam looking forward to hearing from you.
Thanks.
Hi Nergui,
You can download the software (excel template) from my web site here. Try to understand the AHP method first, watch my video and/or read material in the web, there is a lot available.
Regards, Klaus
Hi Klaus, thank you for the quick reply, here is another question. how to extend the number of participants?
Nergui.
because the number of participants of my survey is 20.
Klaus hi,
Further to an earlier communication of mine, I would like to take the opportunity and ask the following questions:
a) Concerning the consolidated result of respondents, you use geometric mean of replies; I guess for better results. Do you believe that arithmetic mean can be used instead? In any case for AHP process, for the calculation of priority vector and eigenvalue, geometric mean is used (calculation of the nth root of the products of the values in each row…etc)
b) Which is the most feasible way to increase the number or participants? (up to 10 are allowed), but you mention extendable.
Thanks in advance for your help.
Dear Harris,
a) Aggregation of individual judgments (AIJ) or aggregation of individual priorities (AIP) have to be seen separated from the priority derivation method like row geometric mean or eigenvalue vector. Even the aggregation of priorities can be done using weighted geometric mean (WGMM) or weighted arithmetic mean method. For consistent matrices WGMM should give the same results as AIJ. But for the arithmetic mean I didn’t find much literature.
b) Extension for more than 10 participants can be done by copying the input sheets and rename plus add (copy/paste) in the matrix sheet. How many do you need? Twenty or 25 I could do for you and email the modified template. (Provided you give me a copy of your final work/publication.
Regards, Klaus
Hi Klaus:
WRT “For consistent matrices WGMM should give the same results as AIJ”: How do you arrive at “consistent matrices” in an AHP processing model.
Also, How do we arrive at the number of participants: say when we are taking the viewpoint of a stakeholder group.
Thanks in advance, SRoy
Hi S Roy
Consistency depends on the actual judgment of participants. Unfortunately people are not always consistent, you cannot do much about it. AIJ helps to improve consistency. My recommendation is also to use the balanced scale instead of the standard 1-9 AHP scale. It’s implemented in my template.
Number of participants depends on the projects (how many stakeholders do you have to involve?). One of my largest AHP projects was multinational; I had to involve 65 people in total. I did some aggregation in between, e.g. per country. In another small project I just involved 2 decision makers.
Regards, Klaus
Thanks, Klaus:
In case of an AHP model where stakeholders (actors) are mentioned at a particular level, how is the pairwise comparison done for that level: who would do this? Is it that the stakeholders themselves would do pairwise comparisons of themselves?
Thanks, S Roy
Yes, stakeholders themselves.
Thanks for your prompt reply.
a) True, it is hard to find literature for arithmetic mean, in any case the result wouldn’t be accurate enough.
b) It goes without saying that work will be fully referenced. Once I make up my mind about final number of participants, I will let you know.
hi dear Klaus
Thank you very mach for this use full software.
I have 22 Criterion. may u email the modified template?!!
Regards, Afsane 🙂
Hi Afsane,
do you have 22 criteria or 22 participants?
Ten criteria is the maximum, I already extended from 8 to 10. (see my comment here)
My recommendation is to further break down into categories and sub-categories, if you have more than 10 criteria.
If you have more than 20 participants, please see here.
regards,
Klaus
thanx dear Klaus 😉
Hi.., I’m still newbie for AHP, especially about expert choice software. Can you explain to me about combined participant’s result?
what steps should I take to get the final result?
Regards,
Sorry, I don’t have and don’t know the expert choice software.
Sir
I am very new for AHP. I wants to start with it on a production scheduling problem. Can anybody tell me how can i use AHP as beginner. From where i can learn to how to solve a problem using AHP
Hi Manjeet, best you watch my video and search for AHP in the net. There are a lot of articles available, some from Saaty, the originator of the method, with explanations about practical applications.