Replace custom function in Google Sheets with standard functions

Question

I have a range of cells, and I want to accrue the number of times a column has the max value for its given row.

Sample:

       headers ->  a    b   c   d   e   f   g   h
                   0    0   12  18* 1   0   0   0
                   30*  0   15  25  0   0   0   0
                   35   0   19  31  0   0   31  50*
                   40   10  19  31  0   2   5   55*

expected:

 #max val per row-> 1   0   0   1   0   0   0   2

The maximum values are marked with an asterisk. The column a scores 1 because it has the maximum value in the second data row, the column d scores 1 as well because it has the maximum value in the first data row and the column h scores 2 because it has the maximum value in the third and fourth data rows. The rest of columns don't have the maximum value in any row, so they get a 0.

For just one row, I can copy this formula for for each column and it would do it, but I need something that applies the max row-wise COUNTIF(B2:B10, MAX($B2:$B10)).

I have written this google apps script, but I don't like its responsiveness (seeing the "Loading..." in the cell for almost a second is kind of exasperating compared with the snappiness you get with native functions):

function countMaxInRange(input) {
  return [input.map(function(row) {
    var m = Math.max.apply(null, row);
    return row.map(function(x){return x === m && 1 || 0});
  }).reduce(function(a, b){
    var s = Array(a.length);
    for (var i = 0; i < a.length; i++) {
      s[i] = (a[i] + b[i]) || 0;
    }
    return s;
  })];
}

Any ideas on how I could replace that code with built in functions? I don't care adding auxiliar rows or columns, as long as it is a constant number of them (that is, if I extend my dataset I don't want to manually add more helper rows or columns for each new data row or column).

I think I could add an extra column that collects the header of the column with the max value for each row; and then for each data column count how many times their header appears in that auxiliar column, but does not seem very clean.

Answer 1

FORMULA

=ArrayFormula(TRANSPOSE(
MMULT(
N(TRANSPOSE(NamedRange1)
=
INDEX(
QUERY(TRANSPOSE(NamedRange1),
"SELECT "&JOIN(",",("MAX(Col"&TRANSPOSE(ROW(NamedRange1))-INDEX(ROW(NamedRange1),1)+1)&")"
)),
2)
),
SIGN(ROW(NamedRange1))
)
))

where NamedRange1 is named range referred to the range.

Conditional formatting:

• Apply to range: A1:H4
• Custom formula: =A1=MAX($A1:$H1)

Explanation

Summary

The above formula no requires extra columns, just to set the range as a named range. In the formula NamedRange1 was used but it could be customized according to your preferences.

The result is a 1 x n array where n is the number of columns of NamedRange1. Each column will have the count of occurrences of maximum values by row on the correspondent column.

Featured "hacks"

• ARRAYFORMULA returns an array of values.
• Ranges greater than 1 x 1 are handled as arrays.
• Using an array as argument with some functions and operators works in a similar way than a loop. In this case, this features is used to create a SQL statement to get the maximum value of each column of the input data. Note that the input data for QUERY is the transpose of NamedRange1.
• N coerce TRUE/FALSE values to 1/0 respectively.
• MMULT is used to make sums by rows

Note: the +0 shown on the image was inserted to force Google Sheets to keep the breaklines introduced on an edit of the formula without breaklines because if there are not significant changes to the formula, the breaklines are automatically removed due to the formula/result caching feature of Google Sheets.

Reference

MMULT Usage by Adam Lusk.