Updated AHP Excel Template Version 2014-05-09

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:

thrh

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.

Welcome to BPMSG – May 2014

Concepts, Methods and Tools to manage Business Performance

Dear Friends, dear Visitors,

The latest beta version of my AHP online software (AHP-OS) has now additional features to manage complete AHP projects. AHP stands for Analytic Hierarchy Process, and is a decision support tool.  To use the full functionality, please register and log in; it’s all free.

You can store complete decision hierarchies, use them to estimate the weights of criteria and sub-criteria and evaluate up to seven decision alternatives.

AHP is also helpful to support group decision making; participants can input their individual judgments and a consolidated group result is calculated. I have prepared a practical example, where you can participate, input your judgments and view the overall group results and consensus. Just click on the link and try it out.

The development is still continuing. I am further optimizing the handling and plan to implement additional analysis, especially for group decision making. Bookmark the page and revisit from time to time to get the latest updates.

Now please enjoy your visit on the site and feel free to give me feedback – it is always appreciated.

Klaus D. Goepel,

Singapore, May 2014

BPMSG stands for Business Performance Management Singapore. As of now, it is a non-commercial website, and information is shared for educational purposes. Please see licensing conditions and terms of use. Please give credit or a link to my site, if you use parts in your website or blog.

About the author

AHP-OS online software – Frequently Asked Questions

Note: All answers assume that you are a registered user and logged in.

Q: How do I save a new AHP hierarchy definition?

A: In the Session Administration Menu click on New.

The hierarchy window is displayed. Define a new hierarchy in the text input field and click on Submit new hierarchy, then Save in the Hierarchy Input Menu.

The group session window will open. Optionally provide a project short description and  click Go, and the defined hierarchy is stored and shown as type “H” (Hierarchy) in the project table of the Session Administration Menu.

Q: How can I define and store a project for the evaluation of alternatives?

A: Start with a hierarchy definition, save and resume from the project table by clicking on the session code link. Complete all pairwise comparisons (Click on AHP in the hierarchy) to define the weights of criteria. (Before alternatives can be evaluated, criteria weights need to be defined.) Submit for group evaluation and view the result. In the group result page click on Use consol. priorities.

Then click on Evaluate Alternatives in the decision hierarchy display. Provide number and names of your alternatives and save a new project. It will be saved as type “A” (Alternative Evaluation). From there you can resume and start the alternative evaluation.

Q: How can I use a defined and stored hierarchy definition as template for a new project or new group session?

A: Click on the session code link of the project, you want to use, in the Stored AHP project session table.

a)      If the project has no participants, the project will be opened in the group session input mode. Click on Leave, then Close in the Active Session Menu.

b)      If the project has participants, the project will be opened in the AHP Group result window. Click on the Resume link at the bottom of the page, then Leave and Close in the Active Session Menu as in a).

You can now re-use the hierarchy definition and save as a new project.

Participate in an AHP group session – AHP practical example

This is a practical example of an AHP group session, using AHP-OS, where you can input your judgment and see the consolidated group result of all participants.

Imagine, you plan to buy a tablet computer. Decide on the importance of criteria, like display size, battery life etc. Click on the image below, input your name, click on AHP and start the pairwise comparisons.

What feature is more important and how much on a scale from 1 to 9? Once finished,  submit your input for group evaluation and see the results.

[php snippet=15]

View the detailed group results.

AHP Projects

With the latest update of my free web based AHP software you can now manage complete projects and group sessions. To use the full functionality, register a new account (It’s all free!) I had to implement the registration in order to manage and protect the data.

After login the project session page will be shown. Here your AHP projects are listed in a table, each of them with a unique session code.

A click on the session code will then open the project, either in the hierarchy display mode (the project has yet no participants),  where you can modify and update the hierarchy structure,  or in the group result mode, where you get the consolidated group result of all participants with a breakdown by categories and individual participants.

The project menu allows you to view project details, start a new hierarchy or delete existing projects.

After you have defined a new hierarchy, click on Save/Start new Group session. Enter a project short description, click Go, Ok and done, and the new project is saved in you project list.

Let me know in case you face any problems. A short description of the program as pdf can be found on the AHP-OS start page.

AHP group decision making

The figure below shows, how a group session is conducted to determine group priorities using BPMSG’s AHP online system. The group session chair initiates a group session (You need to be registered and logged in). A six character session code is generated. Participants can use this session code to log into the group session and provide their judgments. Try out a practical example, where you can participate and input your judgments

Example showing the result for two participants. See also my post about group decision making.

XIO qjBHlfw OT

AHP Online Software – Version 2014-03-15

I just released a new version of my AHP Online Software. It includes the possibility to do group sessions: several participants can make the pairwise comparison, and you can view the consolidated group result.

After definition of the hierarchy using the AHP online system, click on “New Group Session” (You need to login  as  registered user). A session code will be provided. Enter your name, start the pairwise comparisons and submit for group evaluation.

Note down the session code, and provide the link, shown under the active group session, to your participants to log in and do their pairwise comparisons.

You can view the group result, once data are submitted, with a click on “View group results”. You might always review results by using the session code and your name using the AHP Group Session link.

Please be aware that this version is still a beta version. Kindly feedback any problems or bugs.

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!

ks V

AHP Excel Template

NEW! BPMSG’s free AHP online software: For a quick evaluation of priorities use my AHP online calculator. If you need to handle a complete AHP hierarchy, try out my new AHP-OS System.

Download page of the AHP Excel Template:

Latest Version
Please download the latest version from here

The work is licensed under the Creative Commons Attribution-Noncommercial 3.0 Singapore License. For terms of use please see our user agreement and privacy policy.

Older Versions for tracking purpose: please contact the author.

OLPcoEo nRuJl m
;