How to use the BYROW function

Table of Contents

1. BYROW Function Syntax
2. BYROW Function arguments
3. How to count the top 10 numbers in a cell range per row
4. How to count the bottom 10 numbers in a cell range per row
5. How to count the top 10 numbers in a cell range (column-wise) per row
6. Get Excel *.xlsx file

Excel 365 formula in cell H4:

Explaining the formula

Step 1 - Calculate the 10th smallest number

The SMALL function returns the k-th smallest value from a group of numbers.

Function syntax: SMALL(array, k)

SMALL(B4:F13,10)

becomes

SMALL({527,79,121,76,128;219, ... ,241},10)

and returns 145.

Step 2 - Compare the 10th smallest number to all numbers in the cell range

The greater than sign lets you compare values in an Excel formula, this example shows the greater than sign and an equal sign combined.

These characters are logical operators, they return TRUE or FALSE.

B4:F13>=SMALL(B4:F13,10)

becomes

B4:F13<=701

becomes

{527,79,121,76,128;219, ... ,241}<=145

and returns

{FALSE, TRUE, TRUE, TRUE, TRUE; FALSE,  ... , FALSE}

Step 3 - Replace boolean values with numerical values

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(B4:F13<=SMALL(B4:F13,10),1,0)

becomes

IF({FALSE, TRUE, TRUE, TRUE, TRUE; FALSE,  ... , FALSE},1,0)

and returns

{0, 1, 1, 1, 1;0,  ... , 0}.

Step 4 - Build the LAMBDA calculation

The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

Function syntax: SUM(number1, [number2], ...)

SUM(a)

Step 5 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,SUM(a))

Step 6 - Pass values to the LAMBDA function

BYROW(IF(B4:F13<=SMALL(B4:F13,10),1,0),LAMBDA(a,SUM(a)))

becomes

BYROW({0, 1, 1, 1, 1;0,  ... , 0},LAMBDA(a,SUM(a)))

and returns

{4; 1; 0; 1; 0; 1; 1; 0; 1; 1}.

Back to top

5. How to count the top 10 numbers in a cell range (column-wise) per row

The image above shows numbers in five columns #A to #E, each column has a separate built-in conditional formatting applied to it "Highlight top 10 items". It highlights the 10 largest values in blue to make this easier to spot and count to make sure the formula is correct.

This example demonstrates a formula that counts the top 10 numbers per row, this is now doable using the new BYCOL and BYROW functions. The first row (row 4) across columns #A to #E has no blue cells. The formula in cell H4 returns 0 (zero) for that particular row.

Excel 365 formula in cell H4:

Explaining the formula

Step 1 - Build the LAMBDA calculation

We are interested in calculating the 10th largest number in each column. The LARGE function is useful for this calculation.

The LARGE function calculates the k-th largest value from an array of numbers.

Function syntax: LARGE(array, k)

LARGE(a,10)

Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,LARGE(a,10))

Step 3 - Pass values to the LAMBDA function

The BYCOL function lets you pass an array to the LAMBDA function.

BYCOL(B4:F34,LAMBDA(a,LARGE(a,10)))

returns

{725,658,656,555,544}.

The 10th largest number in column #A is 725, 658 in column #B, and so on.

Step 4 - Check which numbers are greater than or equal to the 10th largest value

The greater than sign lets you compare values in an Excel formula, this example shows the greater than sign and an equal sign combined.

These characters are logical operators, they return TRUE or FALSE.

B4:F34>=BYCOL(B4:F34,LAMBDA(a,LARGE(a,10)))

becomes

B4:F34>={725,658,656,555,544}

and returns

{FALSE, FALSE, FALSE, FALSE, FALSE; FALSE, ... , FALSE}

Step 5 - Convert boolean values to their numerical equivalents

The parentheses let you control the order of operation, we need to compare cell range B4:F34 to {725,658,656,555,544} before we convert the boolean values.

(B4:F34>=BYCOL(B4:F34,LAMBDA(a,LARGE(a,10))))*1

The asterisk character lets you multiply values in an Excel formula, it also works with boolean values. The result is their numerical equivalents:

TRUE - 1
FALSE - 0 (zero)

(B4:F34>=BYCOL(B4:F34,LAMBDA(a,LARGE(a,10))))*1

becomes

({FALSE, FALSE, FALSE, FALSE, FALSE; FALSE, ... , FALSE})*1

and returns

{0, 0, 0, 0, 0; 0, ... , 0}

Step 5 - Build another LAMBDA calculation

We are now going to sum numbers per row.

The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

Function syntax: SUM(number1, [number2], ...)

SUM(a)

Step 6 - Build another LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,SUM(a))

Step 7 - Pass values to the LAMBDA function

The BYROW function lets you pass a row of values based on an array to the LAMBDA function.

BYROW((B4:F34>=BYCOL(B4:F34,LAMBDA(a,LARGE(a,10))))*1,LAMBDA(a,SUM(a)))

becomes

BYROW({0, 0, 0, 0, 0; 0, ... , 0},LAMBDA(a,SUM(a)))

and returns

{0; 0; 2; 1; 2; 0; 2; 4; 1; 0; 3; 3; 3; 2; 1; 1; 2; 0; 3; 0; 2; 1; 3; 2; 1; 2; 2; 2; 3; 0; 2}

Back to top