# 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.

5/5 - (1 vote)

## 46 thoughts on “AHP Excel Template”

1. Chronos says:

Hi Klaus,

Thank for your new version of AHP. I want to know how far the practicability of using geometric mean for averaging an experts’ rate instead of using traditional average rating value which is equal to total rate given by expert divided by total # of experts.

What i am discover that traditional that is difficult in dealing with completely different judgement. As an example expert A rates 2 Expert B rate 5 while expert 3 rate 1/7. As a result (2+5+1/7)/3=???. With a geometric mean, it is capable to deal with this situation like (2x5x1/7)^1/3=0.48.

However, for journal writing, i am a bit worry and not really confident to use the geometric mean for judgment consolidation since there is not much in the literature. I am really hope to hear your opinion on this matter.

Thanks

1. Hi Chronos,
Thanks for feedback. There are two points:
Firstly, you have to use the geometric mean for the consolidation of pair-wise comparisons, as AHP uses a ratio scale. In case you consolidate the priorities (outcome), you might take the arithmetic mean.
Secondly, for me it is a basic question about using the concept of means to come to group decisions. Assume, you have two strategies A and B, and you need to decide, which one to follow. Expert 1 favorites A, expert 2 says B. Is it a good way to follow both half hearted with 50%? In this case a mathematical decision method can not help. The only way is to return to the original problem, discuss with the experts, revise the inputs or make a definite decision for A or B.
I use the concept of diversity, to analyze and cluster the inputs from groups, and to get a better insight into the experts views. Just now I submitted a paper for the upcoming ISAHP2013 conference, where I will highlight these points.
Finally, as long as you don’t have an alternative, use the concept of means, being aware of the above. My experience: the consolidated outcome is usually accepted, as all group members know, they were heard and their inputs are reflected.
Regards, Klaus

2. Simon Musaeus says:

Hi Chronos,
I am presently writing a doctoral study in Business about the application of AHP, and the topic of aggregation of priorities has drawn considerably my attention. There is not too much literature about the aggregation, but you may want to start using this one

Dong, Y., Zhang, G., Hong, W.-C., & Xu, Y. (2010). Consensus models for AHP group decision making under row geometric mean prioritization method. Decision Support Systems, 49(3), 281–289. doi:10.1016/j.dss.2010.03.003

Apart from that Google Scholar should give you some good results searching “Aggregation of Results” and “AHP”. If you do not have access to a commercial or university database, you may want to install Mendeley (www.mendeley.com) and search there. Hope this helps!

Cheers.

2. Simon Musaeus says:

Dear Klaus,
thanks a lot for the very practical information on AHP. Now I would like to hear your opinion about when and how to apply in the aggregation of the results Aggregation of individual judgments or Aggregation of individual priorities. The literature is not hundred percent consistent about that. Until today I find that AIP is rather used for groups with individual interests on decision outcome, whil AIJ is rather used in groups which pursue a common interest, such as managers of one single company. In AIP the resulting priorities from the Eigenvector would be aggregated by building the weighted arithmetic average of all given priorities for one single factor or alternative from all evaluators. In AIJ, the results of the judgments for every pairwise comparison would be aggregated by using the weighted geometric average (as it is a ratio scale). Priorities later will have to nebe normalised. On one and the same set of evaluations I have found out that both methods create quite different results, including rank-reversals. Could you comment on your experience? Thanks!!

1. Dear Simon,
interesting questions! I actually never thought through this. For me it was clear and most natural from the beginning to use “AIJ” with geometric mean as an input to the method. I guess one reason is that it is more logic and easier to discuss in a panel of experts about different ratio weightings in the pairwise comparisons than to discuss the output weights after running through the method individually. The whole AHP method is based on the fact that it is easier to compare pairwise than to give weights (out of the stomach or out of the blue) to a whole set of criteria. Would be interesting to hear opinions from others.
Thanks and regards, Klaus

3. Chris says:

First of all, thank you for the excel sheet. I have a question however.
I have input from 6 people, my CR is 4.2. It seemed right as everybody answered quite similar. However when I put in a seventh person, who I filled in completely opposite of all the others, the CR goes down? Why is that, I expect the CR to go up when filling in a valuation opposite to the others.
Kind regards

1. Hi Chris,
Thanks for your feedback. Actually CR is a measure of the consistency of one person’s (or also the consolidated) inputs in each matrix. Assume someone inputs A more important than B, B more important than C. Now logically A has to be more important than C. If he puts C more important than A, consistency ratio will increase! So it’s not whether he answers opposite to all others, but whether he is consistent in his answers. Therefore CR can go down.
I assume, your 4.2 means 4.2%.
Regards, Klaus

4. I’m not sure where you’re getting your info, but good topic.

I needs to spend some time learning more or understanding more.
Thanks for excellent info I was looking for this info for my mission.

5. Maja says:

Thank you very much!!! The tool is very helpful .

6. sunshine says:

Hi Klaus,
Thank you for sharing this template with us. It is really helpful. I have a question regarding the number of the criteria. Can the number of the criteria increased to more than 8, like 10 above? Thank you and looking forwards your reply.

1. Hi,
sorry for my late reply, just back from a business trip. Yes, in principle the number of criteria could be increased. But I stick to a maximum of eight, because my experience shows that much more is not practical, and the pairwise comparison easily results in inconsistencies. 8×8 is also a limit with respect to accuracy using Excel.
You could try to extend by yourself, all sheets are protected w/o a password, so after unprotecting they can be modified. Better break down the problem in additional categories, if possible.
regards, Klaus

7. A new version of the template will be published soon. It allows now multiple inputs from up to 7 participants and runs under Excel 2010.

8. Tara says:

I downloaded this spreadsheet as well. However, I am trying to rank a set of criteria as well as the best alternative for that set of criteria. I am not sure how to rank the alternatives in relation to the criteria. This spreadsheet allows for weights to be created in isolation but not together. Help please!

1. Hi Tara,
Thanks for your feedback. Yes, right. The evaluation of alternatives has to be made manually, or with your own simple excel sheet. To do it, just multiply the weight for a specific value in each sub category of the selected alternative with the weight of the main categories and sum up to get the outcome for this alternative. In the simplest case, if you have three main categories, each gets 33.3% (equal weight), or you use also AHP for the main categories. Have you seen my video on YouTube? It’s explained and shown there. Look for http://www.youtube.com/bpmsg
Regards, Klaus

9. Wind says:

Hi Klaus,

I did download AHP Excel Template above but still have no idea how to use it. Do you have manual that shows how to use AHP Excel steps by steps?
I have 15 respondents. How to calculate the combined respondents’ judgment?

1. Hi,
My excel sheet is not directly suitable for multiple inputs. You have to calculate the geometric mean for all matrix elements, before you run the AHP algorithm. There is a prepared worksheet in my excel file (multiInp) and a short explanation in readme.txt or in the comments to this post. But the whole requires manual changes of the excel references. Don’t have a step-by-step manual, sorry.
Regards, Klaus