Consistency ratio in spreadsheet comparisons
Learn how the consistency ratio works in a spreadsheet AHP comparison, how to read it, and what to fix when pairwise judgments conflict.
Contents
- Building the pairwise comparison matrix
- Deriving priorities and the consistency index
- Reading the consistency ratio and fixing conflicts
- Questions about consistency ratio in AHP
- What does the consistency ratio explain in a spreadsheet comparison?
- How do I calculate the consistency ratio in a spreadsheet?
- What consistency ratio is acceptable in AHP?
- What should I do when the consistency ratio is too high?
The consistency ratio in spreadsheet comparisons tells you whether the judgments you entered in a pairwise comparison table hold together logically. You build the matrix of pairwise comparisons, compute a priority vector, and compare the resulting consistency index against a random benchmark to get the ratio. The page explains the steps in a spreadsheet, what the number means for criteria weighting, and when the judgments need another look.
Building the pairwise comparison matrix
A pairwise comparison matrix places each criterion against every other criterion in a square grid. In an AHP (analytic hierarchy process) sheet, the diagonal holds ones because a criterion equals itself. Each off-diagonal cell holds the strength one criterion has over another, and the mirrored cell holds the reciprocal value. Entering the upper triangle and filling the lower triangle with reciprocals keeps the sheet consistent by construction.
Deriving priorities and the consistency index
The priority vector of a pairwise comparison matrix comes from its principal eigenvector or from a normalized column average. A spreadsheet can approximate the eigenvector by averaging each row after dividing every entry by its column sum. The consistency index measures the gap between the largest eigenvalue and the number of criteria. A matrix with perfect logic returns a consistency index of zero.
Reading the consistency ratio and fixing conflicts
The consistency ratio divides the consistency index by a random index that depends on the number of criteria. A low ratio means the pairwise judgments agree well enough to trust the criteria weighting. A high ratio points to a loop of judgments that contradict each other, such as A over B, B over C, and C over A. Revisit those cells rather than forcing the number down.
Questions about consistency ratio in AHP
What does the consistency ratio explain in a spreadsheet comparison?
The consistency ratio explains how much the pairwise judgments in a spreadsheet depart from perfect logical order. It compares your matrix consistency index with the index of a randomly filled matrix of the same size. A low value supports the criteria weighting you derived, while a high value flags judgments that conflict.
How do I calculate the consistency ratio in a spreadsheet?
You calculate the consistency ratio in a spreadsheet by multiplying the priority vector by the comparison matrix and dividing each result by its priority. Average those ratios to get the largest eigenvalue, then subtract the criteria count and divide by one less than the count. Divide that consistency index by the random index for the matrix size to finish the ratio.
What consistency ratio is acceptable in AHP?
An acceptable consistency ratio in AHP is a threshold you set before judging the matrix, and a value near zero signals strong logical agreement. Many AHP guides treat a ratio around one tenth as tolerable, though the cutoff is a convention rather than a law. The cutoff depends on how much weight the decision carries and how many criteria you compare.
What should I do when the consistency ratio is too high?
A high consistency ratio means the pairwise comparisons contain contradictory judgments that weaken the criteria weighting. Find the cells that form a conflicting loop, such as one criterion beating a second that beats a third that beats the first. Re-examine the evidence behind those entries and revise the judgments instead of adjusting the arithmetic.