Just a small correction in the summary sheet: Now the correct name of the selected scale is displayed.
You can download the latest version of the AHP Excel Template from here.
Please consider a small donation, if you find the template useful for your work.
In the implementation of both, AHP excel template as well as AHP online software, inconsistent judgments are highlighted and recommendations for consistent judgments are given. How it is done, and what is the method behind?
The method is based on Saaty’s article “Decision-making with the AHP: Why is the principal eigenvector necessary“. European Journal of Operational Research 145 (2003) 85–91. He describes three methods how to transform a positive reciprocal matrix to a near consistent matrix.
In my implementation I construct the matrix εij = aij wj/wi to identify the three judgments for which εij is farthest from one. In Saaty’s paper it is shown as table 3, in my excel template it is called Consistency Error Matrix. As I have to do it on each individual input sheet using as an approximation the RGMM (row geometric mean method) results, before calculating the eigenvector solution in the summary sheet.
If you are a registered user of my AHP online software, please keep in mind and be aware:
After 3 months of inactivity, your user account will be deactivated automatically. Please reactivate, using the link provided in the e-mail, if you want to keep your data, otherwise your account and all your data will be deleted 48 hours after deactivation.
This is done to ensure a slim database and help to keep the software fast and responsive. Please also avoid multiple registrations under different user names and e-mail addresses. You can keep up to 20 projects in your account, and it should be sufficient for the majority of users.
AHP online program limits:
Thank you for your cooperation, and PLEASE – as a registered user – help to support this website with a small donation. I do not have a commercial interest, but I spend a lot of time, sharing my knowledge for free, and I have running costs to keep the site alive.
Thank you!
When I was asked just now in an e-mail, how my AHP online software operates, I realized that I have never published a description about it. Therefore, here a short summary:
The AHP-OS software implementation is based on my AHP excel template. I have published one paper about it in 2013. All mathematical formulas used, are shown in this conference paper or in the excel template manual on the last 2 pages. The online version is realized in PHP, using OOP (object oriented programming), running on a Linux server.
The core module is the ahp class. It contains the routines to fill the decision matrix from the pairwise comparisons, to find the dominant eigenvalue using the power method, and to calculate the consistency ratio.
A second important class is the hierarchy class. It translates the text file – defining the hierarchy – into a hierarchical data array. When I implemented the software, I wanted to get a flexible tool, which can be used for all kind of hierarchical problems. This was actually more difficult than the implementation of all mathematical calculations, especially under the aspect of safety and malicious online attacks. The feature also differentiates the online version from the excel implementation. The excel template cannot handle hierarchies at all.
The third important class implements the database and its management. I use SQL with either sqlite or mysql drivers. There are 4 tables:
The rest of classes contain supporting functions, like html in/output, excel download and consolidation for group results. I use a basically two open source packages, phpMailer and phpGraphlib, the rest is all realized by myself from scratch.
Please consider a donation, it will help me to maintain the website and program.
Update 2017: AHP-OS software implementation (pdf)
Sometimes I receive the question, how to validate the correct implementation of my AHP excel template or my AHP online software, i.e. are the results correct and reliable?
Please read my paper about the AHP-OS software implementation (2017).
Thanks to feedback from Frank, this updated version got some minor modifications for the case of two criteria only.
Download the latest version of the AHP Excel Template.
In this version I changed the minimum number of criteria from three to two; the number of criteria ranges now from 2 to 10.
It seems in the Turkish language version of Excel the selection of the consolidated matrix (“0”) gives an error. I did some minor modifications, but I am not sure whether it is now working. Please give me your feedback.
Download the latest version of the AHP Excel Template.
Thanks to a comment from Patric, I corrected a wrong reference in the multInp sheet. The matrix of participant 13 was referring to the matrix of participants 12. Download the latest version of the AHP Excel Template.
In the analytic hierarchy process you define a set of criteria and sub-criteria arranged in a hierarchy, to do pairwise comparisons and find the weights of criteria or decision alternatives. In my AHP excel template the number of criteria is limited to ten, in my AHP online software to 15. Still sometimes I am asked to extend and allow for more criteria.
There are three reasons not to exceed the number of 9 criteria in any AHP project. Two of them are quite clear and published in the literature:
See also my post here.
The third reason is not so obvious and not so well known. It is based on the limited 1 to 9 AHP ratio scale for the judgment. The maximum preference you can give to one criterion is 9, i.e. this criterion is 9 times more important than all other criteria. Assume, you have only two criteria, then – if you fully prefer one over the other – the preferred one will result in a weight of 90%, the other gets a weight of 10%. The weights depend on the number of criteria, the maximum weight or maximum priority wmax is always
wmax = M/(n + M – 1)
with M = 9, the maximum of the AHP scale and n the number of criteria. The below diagram shows wmax as a function of the number of criteria.
Clearly you can see that for 10 criteria the maximum possible weight reduces to 50%, or in other words, although you give full preference to one criterion, it only gets a weight of 50%! For more than ten criteria the weight will be below 50%. This is the reason, why the number of criteria should never exceed the magical number seven plus or minus two.
Thanks to feedback from Benedikt, this latest update contains a minor change, to show the convergence of the power method, when calculating the eigenvalue. In the summary sheet a threshold (squared Euclidean distance d2) can be set, to show how many iterations it takes, until the change of the approximated eigenvector is below the given threshold. By default the value is set to Thresh: 1E-07:
In the above example it takes 7 iterations until d2 is below 1E-07. The actual difference is 3.5E-08 (EVM check). As the number of iterations in the template is fixed to 12, care should be taken if the value reaches 12.
You might download the latest version from my AHP template download page.
