Ranking Criteria Systematically - Pairwise Comparison.xlsm

ncwalker

Trusted Information Resource
#1
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

Elsmar Forum Sponsor

Chennaiite

Never-say-die
Trusted Information Resource
#3
.....
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.
 

ncwalker

Trusted Information Resource
#4
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

ncwalker

Trusted Information Resource
#5
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

#6
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!
 

ncwalker

Trusted Information Resource
#7
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.
 
#8
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!
 
Thread starter Similar threads Forum Replies Date
blile Increasing PFMEA occurrence ranking after non-conformance FMEA and Control Plans 4
Ashok sunder Is it possible to reduce Risk likelihood and impact Post control Ranking after corrective action taken for risk? FMEA and Control Plans 1
Ashok sunder Is it possible to reduce FMEA Occurrence and Detection Ranking after corrective action taken for customer complaints? FMEA and Control Plans 6
N Process FMEA Occurrence Ranking for New Process FMEA and Control Plans 3
K Process FMEA Ranking Table for for Landing Door of an Elevator FMEA and Control Plans 7
B Ranking Continual Improvement Projects Quality Tools, Improvement and Analysis 5
O PFMEA - Reducing Severity (S) Ranking - Manufacturing Process Design Changes FMEA and Control Plans 4
M Ranking Changes for Non-"Mobile-Friendly" Sites Forum News and General Information 10
M Changing FMEA template to include Detection Ranking for the PFMEA FMEA and Control Plans 2
C Occurrence Ranking: What is the basis for 1 in 1,000,000? APQP and PPAP 4
C How to evaluate the Detection Ranking of a new Design Control FMEA and Control Plans 1
K FMECA (Critical Analysis) Format and Ranking Table AS9100, IAQG 9100, Nadcap and related Aerospace Standards and Requirements 6
C Poka Yoke and FMEA Detection Ranking FMEA and Control Plans 3
Z PFMEA Detection Controls and Ranking - Prevent Shipment FMEA and Control Plans 3
K Probability of Occurrence Ranking for Low Volume Manufacturing vs High Volume FMEA and Control Plans 11
B FMEA Ranking Rating Best Practices FMEA and Control Plans 10
S Registrar Ranking - Looking for feedback on SAI Global Registrars and Notified Bodies 8
H DFMEA Severity Ranking of Non-Compliance to EMC Standards FMEA and Control Plans 2
G FMEA Occurrence Ranking - Based upon what? FMEA and Control Plans 6
R FMEA Detection Ranking for Testing Machines FMEA and Control Plans 8
D Company Percentile Ranking - Customer Concern and Warranty Costs Benchmarking 4
M DFMEA Detection Ranking in AIAG FMEA Manual 4th Ed. - Explanation wanted IATF 16949 - Automotive Quality Systems Standard 6
O What is the best approach for Audit Result Ranking? General Auditing Discussions 4
V Detection ranking in FMEA FMEA and Control Plans 4
G FMEA Severity Ranking for an Automotive Audio System FMEA and Control Plans 9
Y Severity, Occurrence and Detection FMEA Ranking Tables - Guidelines? FMEA and Control Plans 8
ScottK And the real ranking of Olympic winners* is: Coffee Break and Water Cooler Discussions 2
tqsrocky TS 16949 Registrar's Ranking & Market Share IATF 16949 - Automotive Quality Systems Standard 12
M Critical or Significant Characteristics or Key Indicators Ranking FMEA and Control Plans 7
J Ranking Severity of Non-Conforming Material Defects Nonconformance and Corrective Action 5
B FMEA Occurrence Ranking in Microelectronics FMEA and Control Plans 24
J Ranking Forbes Automobile industries. The World's Biggest Public Companies World News 14
A Is there any kind of Ranking available for selecting the Registrars? Registrars and Notified Bodies 31
H ISO/TS16949 Certification Body Ranking - Which one do you prefer? IATF 16949 - Automotive Quality Systems Standard 2
A Process FMEA - Help in deciding "detection ranking" of a failure mode FMEA and Control Plans 5
S RPN Ranking - Put a formula in an access database or Excel .xls spreadsheet Excel .xls Spreadsheet Templates and Tools 12
S VDA vs. AIAG - FMEA Ranking FMEA and Control Plans 4
K Severity Occurrence Detection Ranking Scale - 1 to 10 vs. 1 to 5 FMEA and Control Plans 20
D Ranking Audit Effectiveness with RPN General Auditing Discussions 3
Y FMEA Ranking and Prioritization - Failure Modes with higher severity first? FMEA and Control Plans 21
A FMEA Manual - Page 53, Table 8 says Detection Ranking FMEA and Control Plans 3
M PFMEA Severity Ranking - 9 is with warning and a 10 is without warning FMEA and Control Plans 5
J FMEA - Method of Determining the Occurrence Ranking FMEA and Control Plans 4
B FMEA - Reduced Detection Ranking by Use of Preventative Maintenance FMEA and Control Plans 6
B AS9100 8.4.1 Supplier Selection/Evaluation criteria and reevaluations AS9100, IAQG 9100, Nadcap and related Aerospace Standards and Requirements 3
I Mother bobbin final inspection criteria Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
R Procedure, Frequency and Acceptance Criteria for Replicate, Recalibration, Before-After and Intermediate Checks ISO 17025 related Discussions 8
P Design FMEA - Detection Rating criteria ISO 14971 - Medical Device Risk Management 3
J Criteria Matrix To Initiate An A3 or 8D ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 2
Q Best criteria to measure Corrective Action effectiveness - Poor Maintenance ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 10
Similar threads


















































Top Bottom