Table of Contents

1. BYCOL Function Syntax
2. BYCOL Function arguments
3. How to count the top 10 numbers in a cell range per column
4. How to count the bottom 10 numbers in a cell range per column
5. Get Excel *.xlsx file

Excel 365 formula in cell H4:

Explaining the formula

Step 1 - Calculate the 10th largest number

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

Function syntax: LARGE(array, k)

LARGE(B4:F13,10)

becomes

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

and returns 701.

Step 2 - Compare the 10th largest 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>=LARGE(B4:F13,10)

becomes

B4:F13>=701

becomes

{527,79,121,76,128;219, ... ,241}>=701

and returns

{FALSE, FALSE, FALSE, FALSE, FALSE; 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>=LARGE(B4:F13,10),1,0)

becomes

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

and returns

{0, 0, 0, 0, 0; 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

BYCOL(IF(B4:F13>=LARGE(B4:F13,10),1,0),LAMBDA(a,SUM(a)))

becomes

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

and returns

{5, 1, 1, 2, 1}.

Back to top

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

{0, 2, 2, 3, 3}.

Back to top