How to use BYCOL() and BYROW() to evaluate data across columns and rows in Excel

Image: iStockphoto

Most Microsoft Excel functions are autonomous—one result value for each function or formula. For example, you might use SUM() to return the sum of all the values in a single column. If you want to do the same for adjacent columns, you copy the SUM() function or manually enter the function for each column you’re evaluating. Using Excel’s BYCOL() and BYROW() functions, you can enter one function and return an array result set. What’s the advantage? For the most part, using one function instead of many will be more efficient. However, as you’ll see, when combined with other Excel functions, these two functions can do a lot of grunt work.

SEE: 83 Excel tips every user should master (TechRepublic)

Windows: Must-read coverage

I’m using Microsoft 365 on a Windows 10 64-bit system. I recommend that you not upgrade to Windows 11 until all the kinks are worked out. These functions are available only in Microsoft 365 and Excel for the web. For your convenience, you can download the demonstration .xlsx file.

First things first: Excel’s LAMBDA()

Both BYCOL() and BYROW() use the new LAMBDA() function. A LAMBDA function is an anonymous function, according to Microsoft, but that description isn’t helpful. An Excel LAMBDA() function is similar to a user-defined function, but you don’t need VBA. In short, Excel’s LAMBDA() lets you create custom and reusable functions and name them using meaningful names using the form:

LAMBDA([parameter1, parameter2, …,] calculation)

where the optional parameter arguments are a value that you pass to the function. It can be a cell reference or a literal string or number. The calculation argument is the logic you want to execute. Once all that’s correct, you save it all by using the Name Manager to give it a name. To use the function, you simply enter the function name at the sheet level, as you would any other function.

It’s good to know that this new capability exists, but we won’t be using LAMBDA() to create a new function. Instead, we’ll be using it to return an array result set from within the BYCOL() and BYROW() functions.

About Excel’s BYCOL()

BYCOL() is one of Excel’s newish dynamic array functions that combines the values in referenced columns and changes the column/row structure by applying LAMBDA() to each column.

This Excel function uses the following form:

BYCOL(array, LAMBDA(column))

where array is the source data and LAMBDA() is the calculation that will be used to evaluate each column in array. LAMBDA() can return only one value for each column.

The thing to keep in mind is that Excel’s BYCOL() evaluates source data by columns but returns a row of results. Let’s look at a quick example. Figure A shows the results of using BYCOL() in cell H3:

=BYCOL(Table1[[Date]:[Commission]],LAMBDA(column,MAX(column)))

Figure A

Use Excel’s BYCOL() to return a row of maximum values from each column.

If you’re not working with a Table object for your source data, your references to Table1 will be traditional, such as C3:E13 for array.

The thing to remember is that this setup evaluates each column thanks to LAMBDA(), which evaluates one column at a time to return the result array. Each result value is the largest value in its column.

You could do the same thing by adding a Totals Row to the Table object (Figure A). Doing so gives you individual values; using BYCOL() returns an array.

The real power here is that you can substitute MAX() with just about any Excel function. For example, Figure B shows the sum of columns D and E. I changed the array reference from [Date]:[Commission] to [Value]:[Commission] to remove the Date column and then changed MAX to SUM. (It makes no sense to add dates within this context, but mostly, I want you to see how easy it is to modify the function. If you want to practice a bit, try applying MIN() and AVERAGE().

Figure B

Using Excel’s LAMBDA() function, you can execute any number of calculations against several columns with only one function.

We’ve used some simple calculations, but thanks to LAMBDA(), we can specify more complex calculations. For instance, the following function counts the number of values in Value and Commission that are greater than 200:

=BYCOL(Table1[[Value]:[Commission]],LAMBDA(column,SUM(–(column>200))))

As you can see in Figure C, all 11 values in Value are greater than 200 and 9 values in Commission are greater than 200.

Figure C

Excel’s LAMBDA() function can handle some complex calculations.

You might also be wondering what the hyphens do. They convert the results of the expression, column>200, to a series of 1s and 0s, instead of TRUE and FALSE, respectively, so SUM() can sum the resulting array of 1s and 0s. You could also use COUNTIF():

=BYCOL(Table1[[Value]:[Commission]],LAMBDA(column,COUNTIF(column,”>=” & 200)))

but it was a fun opportunity to introduce you to the–logic.

At this point, you’re probably wondering why you would use BYCOL(). It does look a bit scary and in these examples, mostly unnecessary so let’s look at a more reasonable and complex example. Figure D shows a simple set of monthly values for six different people. We want to list all months that have a value greater than 1,750, which we’ve entered into C1. By using an input cell, we can change the benchmark value without having to modify the actual function:

=FILTER(Table2[[#Headers],[Jan]:[Jun]],BYCOL(Table2[[Jan]:[Jun]],LAMBDA(column,COUNTIF(column, “>=” & C1))))

Figure D

Use Excel’s BYCOL() to analyze data.

OK, this particular function is a doozy, but look what it accomplishes! It returns a filtered result array that contains the months by name that include a value equal to or greater than the benchmark value in C1. There are four: Feb, April, May and June. It’s pretty cool really. If you don’t know how to use FILTER(), read How to use the FILTER() dynamic array function in Excel.

You’ve learned a lot, but we still need to review BYROW().

About Excel’s BYROW()

Once you understand BYCOL() and how LAMBDA() works with it to return an array result set, adding BYROW() is easy. It works the same as BYCOL(), but as you might suspect by now, it evaluates rows instead of columns.

This Excel function uses the form:

BYROW(array, LAMBDA(row))

where array is the source data, and LAMBDA() is the calculation that will be used to evaluate each row in array. LAMBDA() can return only one value for each row. Everything you learned about BYCOL() applies to BYROW(), so we can skip right to an example. As you can see in Figure E, the Excel function:

=BYROW(Table1[[Date]:[Commission]],LAMBDA(row,MAX(row)))

returns the largest value in each row, excluding the Personnel values. The results require a bit of explanation. The returned array set is a set of date serial values (the format is General). As you can see, they’re all much larger than the Value or Commission values. Again, in this contrived example, it doesn’t make a lot of sense within the context of the data, but it’s interesting to see something unexpected! If you formatted the values, they would be the same dates you see in column C.

Figure E

Use Excel’s BYROW() to evaluate by rows.

As with Excel’s BYCOL(), you could do this with several MAX() functions, but that would require 11 functions and would return 11 result values. Excel’s BYROW() requires one function and returns an array result set. Now let’s look at something more complex.

Figure F shows the result of the Excel function

=BYROW(Table1[[Value]:[Commission]],LAMBDA(row,COUNTIF(row,”>=” & 200)))

This function returns the number of values greater than 200 in each row, inclusive of Value and Commission only. It’s the same as the earlier BYCOL() example, but it evaluates rows instead of columns.

Figure F

Excel’s LAMBDA() function can handle complex calculations.

As before, let’s apply this Excel function to a more complex situation using the data set in Figure D. In the BYCOL() example, we returned an array set of months. Now, let’s return an array set of employees who have a value greater than or equal to the benchmark value in C1. Figure G shows the BYROW() function at work:

=FILTER(Table2[Column1],BYROW(Table2[[Jan]:[Jun]],LAMBDA(row,COUNTIF(row, “>=” & C1))))

Figure G

BYROWS() combined with FILTER() does a lot of work for us.This function is almost identical to the BYCOL() function used earlier; I changed the FILTER() array to the names in column B and then changed all the BYCOL() functions to BYROW() functions. That’s it! I also changed the benchmark value in C1 (because all of the employees have met the 1,750 value). Four employees have a value greater than or equal to 1,900: James, June, Luke and Martha.

SEE: Office 365: A guide for tech and business leaders (free PDF) (TechRepublic)

Granted, the actual functions are complex, but they do a lot!

I’ve kept the tutorial examples simple on purpose, but now that you know how these Excel functions work, you can start applying them to your own work with more complex requirements like those shown in Figures D and G.

Source by www.techrepublic.com