Ranking Criteria Systematically - Pairwise Comparison.xlsm

N

ncwalker

First I am not sure this belongs in the Lean section of the Cove. But it seems to make sense.

The situation is ranking criteria and the application is any kind of audit or survey where you are coming up with an overall score but the criteria you are using needs a scaling factor for importance.

For example, you have a group of people and you want to decide which beer you are going to drink for the next tailgating party. And your factors are:
1) Less filling
2) Tastes great
3) Can or bottle
4) Cost
5) Visual appeal of label

Most of us would agree that weighting of label appeal as the drinker of the beer would not be very important. And should not carry as significant a ranking as, say, tastes great. But if we got a group together, there may be some serious arguing if we were asked to put these 5 factors in order from most to least important.

Enter the pairwise comparison. (Or what I was taught years ago as a pairwise comparison). The theory behind it is basically that while it is hard to put a list of 5, or 10, or whatever, into a priority list, it is less difficult to decide between only 2. So you run a pattern....

Is "less filling" more important than "tastes great?"
Is "less filling" more important than "can or bottle?"

and so on ...

Then, you add up the number of "victories" and this generates the ranking. So that if you then come up with a Likert scale, you can multiply this score by the ranking so that some factors count more than others (as they should). What you do with the ranking is up to you.

And honestly, a Pairwise comparison is easily done on a white board. But it's nice to have an Excel version. (Easier to store away in a file if needed for supporting data.)

Anyways, here is an Excel version.
 

Attachments

  • Pairwise Comparison.xlsm
    26.5 KB · Views: 1,063

Chennaiite

Never-say-die
Trusted Information Resource
.....
And honestly, a Pairwise comparison is easily done on a white board. But it's nice to have an Excel version. (Easier to store away in a file if needed for supporting data.)

Anyways, here is an Excel version.

I think Excel will be beneficial because of the fact that diversified opinion on the importance of Product features is guaranteed in the Market. Excel sheet gives room to have multiple sheets for individual opinion which can be rationalized. Market survey for Ranking product features is a good practice during Concept definition. This will help really.
 
N

ncwalker

Folks,

My apologies. This was a tool I used and not really polished.

THIS version has typos fixed and I put the password on the spreadsheet should you need to edit it.

Should have checked my work first. :)
 

Attachments

  • Pairwise Comparison.xlsm
    26.6 KB · Views: 434
N

ncwalker

Also - the comment about multiple sheets....

This has two macros in it. Once clears the table because selecting a triangular block of cells in Excel is a pain. The other steps you through the comparisons more for convenience than anything. You can just as easily type in the 1 and 0 in the upper echelon of the matrix.

The point? If you were to copy this sheet into another sheet in the SAME WORKBOOK then the link to the macros would most likely be messed up. Haven't tested it myself, but ...
 
E

element79

First I am not sure this belongs in the Lean section of the Cove. But it seems to make sense.

The situation is ranking criteria and the application is any kind of audit or survey where you are coming up with an overall score but the criteria you are using needs a scaling factor for importance.

For example, you have a group of people and you want to decide which beer you are going to drink for the next tailgating party. And your factors are:
1) Less filling
2) Tastes great
3) Can or bottle
4) Cost
5) Visual appeal of label

Most of us would agree that weighting of label appeal as the drinker of the beer would not be very important. And should not carry as significant a ranking as, say, tastes great. But if we got a group together, there may be some serious arguing if we were asked to put these 5 factors in order from most to least important.

Enter the pairwise comparison. (Or what I was taught years ago as a pairwise comparison). The theory behind it is basically that while it is hard to put a list of 5, or 10, or whatever, into a priority list, it is less difficult to decide between only 2. So you run a pattern....

Is "less filling" more important than "tastes great?"
Is "less filling" more important than "can or bottle?"

and so on ...

Then, you add up the number of "victories" and this generates the ranking. So that if you then come up with a Likert scale, you can multiply this score by the ranking so that some factors count more than others (as they should). What you do with the ranking is up to you.

And honestly, a Pairwise comparison is easily done on a white board. But it's nice to have an Excel version. (Easier to store away in a file if needed for supporting data.)

Anyways, here is an Excel version.

Hi covers!

Thanks for sharing the Excel file, ncwalker. Back when we did research for Sida some 12 years ago, we used a similar type of "pairwise comparison" multivariate analysis to help us in our policy analysis portion. We wanted to know which technical options to mitigate air pollution and climate change were palatable to the transportation industry in Metro Manila, Beijing, and in several Asian cities. What we used then was a software called "Expert Choice." Google will lead you to their website. Once set up on your laptop, the program was pretty straightforward and gives you the results right after the interview/survey.

(Note: I am not in any way related to the makers of this software nor am I selling it.)

Cheers!
 
N

ncwalker

So it appears there is an actual term for it.

Analytic Hierarchy Process

As you can tell from my avatar, paying for software is an anathema to me. :) I will stick with my spreadsheet which works just fine. (Though I do acknowledge in some instances having a "paddle" that would allow one to vote indiscretely has its advantages.)

Biggest difference is that the literature forces the contributors to sum to 1. Which is OK. Though would not affect the end result on my sheet. It is just scaling, after all.
 

thethinker10

Registered
Folks,

My apologies. This was a tool I used and not really polished.

THIS version has typos fixed and I put the password on the spreadsheet should you need to edit it.

Should have checked my work first. :)
How would you add a pause or stop button next to "Yes" and "No" once the macro has been started for the comparison? Thanks!
 

lokotas

Registered
Enter the pairwise comparison. (Or what I was taught years ago as a pairwise comparison). The theory behind it is basically that while it is hard to put a list of 5, or 10, or whatever, into a priority list, it is less difficult to decide between only 2. So you run a pattern....
 
Top Bottom