Using spreadsheet to make a pairwise comparison matrix

Question

I want to use a spreadsheet to take a column of rankings and turning into a pairwise matrix. Suppose I had a column like so:

Candidate	Ranking
A	1
B	5
C	3
D	2
E	4

I want to create a 5x5 matrix where each cell displays a 1 if the row candidate beats the column candidate, and a 0 otherwise, like so:

(I don't know how to make a table of more than 2 columns here, so I used an image.)

I was able to make this table by manually making the first cell of each column individually, using an if statement, and dragging down, but I'd like to make a single formula that can do the whole table at once.

Answer 1

Row Beats Column

Readable (D1:I6,K1:P6)

=LET(t,A2:A6,c,B2:B6,title,"T",
    h,HSTACK(title,TOROW(t)),
    r,TOROW(c),
    d,IF(r=c,"",IF(r>c,1,0)),
    VSTACK(h,HSTACK(t,d)))

Short (D1:I6,K1:P6)

=LET(t,A2:A6,c,B2:B6,title,"T",
    r,TOROW(c),
    VSTACK(HSTACK(title,TOROW(t)),
        HSTACK(t,IF(r=c,"",IF(r>c,1,0)))))

Matrix Only (E2:I6,L2:P6)

=LET(c,B2:B6,
    r,TOROW(c),
    IF(r=c,"",IF(r>c,1,0)))

Ingredients

K1: =HSTACK("T",TOROW(A2:A6))
K2: =A2:A6
L2: =IF(TOROW(B2:B6)=B2:B6,"",IF(TOROW(B2:B6)>B2:B6,1,0))

Answer 2

The following formula will do what you want:

=IF(G$1<>$F2,IF(INDEX($B$2:$B$6,MATCH($F2,$A$2:$A$6,0))<INDEX($B$2:$B$6,MATCH(G$1,$A$2:$A$6,0)),1,0),"-")

example: