CODEWITHSUNDEEP
excelcomparison
gmorissette
gmorissette

Reputation: 291

Find column with superior values through row comparison

I am trying to find a formula that compares the data of two neighbouring cells on the same row, finds which one of the two contains the largest amount and does this throughout both columns to then compile in two cells (one for each column) the number of times one column has had superior results to the other and vice-versa. Matching values are ignored.

Put otherwise

For column A: =IF(A1>B1,+1,+0) throughout both columns

For column B: =IF(B1>A1,+1,+0) throughout both columns

Have a look at my example in case my explanations aren't clear enter image description here

Upvotes: 0

Views: 95

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

Try this formula, entered in a cell not in columns A or B, 

=SUMPRODUCT(--(A:A>B:B))

Similarly for columns B

=SUMPRODUCT(--(B:B>A:A))

Upvotes: 1

Related Questions