Google-sheets – Columns populated with the number of times each row has the first, second or third largest value for any given column

google sheets

So let's say I have a sheet with data like this:

    A     B     C     D     E
1   25    25    15     5    10
2   15    15    10    10    20
3   10    10    20    15     5
4    5    20    25    25    15
5   20     5     5    20    25

Currently I have some conditional formatting (using LARGE()) that color codes certain cells to indicate the first, second, and third largest values, per column. That works great.

What I would like to add, is three more columns, populated with the number of times each row has the first, second or third largest value for any given column. Is that possible with a formula, or would it require something more complex like a custom function? I basically need to evaluate each row, but filter the data by the column each cell belongs to.

Using the example data above, this would give me values like:

     1st   2nd   3rd
1     2     0     1
2     0     1     2
3     0     1     1
4     2     1     1
5     1     2     0

I've looked into several different formula functions like FILTER() and others, but I can't get anything working.

Best Answer

Unfortunately filter() unique() and similar are not structured to work on individual cells within a 2 dimensional range, they tend to return rows or columns. The following works as long as you do not skip ranks and every row in the data set is accounted for in the ranking

For this the output goes in A2:C6 and the data is in G2:K6

This formula can be entered into the top cell of the "1st" column, A2, and dragged down the column

=countif($G2:$K2,"="&large($G$2:$K$6,1))

it counts the number of occurrences of the largest number in the data set.

Because largest() does not provide unique values we need to skip past all of the instances of the highest value to get to the second highest. Fortunately we just counted the instances of the highest value by row. We will take the sum() of that data to get us to the second largest unique value.

This formula can be entered into the top cell of the "2nd" column, B2, and dragged down the column

=countif($G2:$K2,"="&large($G$2:$K$6,sum(A$2:A$6)+1))

The third rank is similar but we need to sum both of the preceding columns:

=countif($G2:$K2,"="&large($G$2:$K$6,sum(A$2:B$6)+1))

The use of sum() here is a short cut taking advantage of the way you are structuring your output. A version of the 3rd column formula that can exist independently would look like this(using input range of G31:K35):

=countif($G32:$K32,"="&large($G$31:$K$35,countif($G$31:$K$35,">="&large($G$31:$K$35,countif($G$31:$K$35,"="&large($G$31:$K$35,1))+1))+1))

the nesting of countif()'s and large()'s gets deeper and deeper for each successive rank.

https://support.google.com/docs/answer/3093480?hl=en&ref_topic=3105474

Related Topic