Google-sheets – Getting the column header if the row value is the greatest of the entire row

google sheetsgoogle-sheets-queryworksheet-function

I think that the best way to describe what I want is with a image

Check the image. Each row will have a description (Of a feature of the programming language, for example) each language will have a rate. The languages with the biggest rates will have their names shown in the "Winner" column.

I want the formula to use in the C column.
It have to respect 2 things:

I can add more languages (More columns)
I can add more descriptions (More rows)

The best I could formulate was this:

=FILTER(C1,C2=max(C2:2)) That will, in the first row, return only Ruby if Ruby has the max value. I couldn't make it generic to repeat for every column.
=FILTER(C1:Z1,C2:Z2=max(C2:Z2)) Based on the above but it returns an error

Best Answer

FILTER is the way to go, but you need to combine it with ARRAYFORMULA:

=ARRAYFORMULA(JOIN(",",FILTER(C$1:Z$1,C2:Z2=max(C2:Z2))))

(the $ signs make it easy to autofill the formula)

Related Solutions

Google-sheets – How to specify “everything except the top row” as a range

There's an easy way. For example, for a sum, you can specify it like this:

=SUM(G2:G)

So basically, specify the first cell you want, then the name of the column. That way, you can skip as many rows as you want.

Google-sheets – How to refer to two different columns in a COUNTIF function

Use a combination of the ARRAYFORMULA, SUM and arithmetic operations

Here's the function to count all of the clicks by a 26 year old:

=ARRAYFORMULA(sum((A:A=26) * (C:C="true")))

There are 3 parts to this operation.

The ARRAYFORMULA takes care of looping over the specified range
The SUM manages counting all of the true results

Essentially, true is being converted to 1 and false is being converted to 0. Boolean logic is done by using arithmetic operations.

An AND operation uses multiplication:

(1 * 1) = 1 - (True && True) = True
(1 * 0) = 0 - (True && False) = False
(0 * 0) = 0 - (False && False) = False

An OR operation uses a combination of the *SIGN function and addition:

sign(1 + 1) = 1 - (True || True) = True
sign(1 + 0) = 1 - (True || False) = True
sign(0 + 0) = 0 - (False || False) = False

Note: The sign function is necessary because of the way boolean addition works differently than arithmetic addition. Basically in boolean addition 1 + 1 = 1, in arithmetic addition 1 + 1 = 2. Obviously, arithmetic addition will mess up the count so you need to run the results of the addition operations through a sign function. The sign function returns 1 if the value is positive, 0 if the value is 0, and -1 if the value is negative.

Lets say you wanted to count the clicks for all users between age 20-25:

=ARRAYFORMULA(sum(sign((A:A=20) + (A:A=21) + (A:A=22) + (A:A=23) + (A:A=24) + (A:A=25)) * (C:C="true")))

Best Answer

Related Solutions

Google-sheets – How to specify “everything except the top row” as a range

Google-sheets – How to refer to two different columns in a COUNTIF function

Related Topic