• Home
  • About
  • Contact
  • Privacy
  • Terms
  • DCMA
  • Sitemap
  • Submit
Regional Posts
No Result
View All Result
  • News
    • Sports
    • Politics
  • Business
    • Crypto
    • Marketing
  • Lifestyle
    • Entertainment
    • Fashion
    • Food
  • Tech
    • Gaming
    • Gadgets
  • Science
  • Health
  • Travel
  • World
Regional Posts
  • News
    • Sports
    • Politics
  • Business
    • Crypto
    • Marketing
  • Lifestyle
    • Entertainment
    • Fashion
    • Food
  • Tech
    • Gaming
    • Gadgets
  • Science
  • Health
  • Travel
  • World
No Result
View All Result
Regional Posts
No Result
View All Result
Home Tech

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

by Editor
November 20, 2022
in Tech
0
istock-881365070-excel-data.jpg
554
SHARES
3.7k
VIEWS
Share on FacebookShare on Twitter

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.

READ ALSO

Boost Your Online Cycling & Running with the Vingo App

The 6 best video editor apps for Android [2022]

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.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.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.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.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.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.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.

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

Share222Tweet139
Previous Post

Significant Benefits Of Online Reputation Management For Businesses

Next Post

British grandmother on death row in Texas speaks to Susanna Reid in new ITV documentary

Related Posts

Boost Your Online Cycling & Running with the Vingo App
Tech

Boost Your Online Cycling & Running with the Vingo App

January 4, 2023
The Best Video Editing For IOS And Android [2022]
Tech

The 6 best video editor apps for Android [2022]

January 10, 2023
5 Signs You Should Invest in Job Management Software for Your Trade Business
Tech

5 Signs You Should Invest in Job Management Software for Your Trade Business

December 1, 2022
Doogee’s V30 Set
Tech

Doogee’s V30 Set To Be The First Rugged Phone To Launch With An eSIM Feature

November 26, 2022
Doogee’s First Tablet T10 Will Refresh You With Ultimate Entertainment
Tech

Doogee’s First Tablet T10 Will Refresh You With Ultimate Entertainment

October 31, 2022
Google Chrome icon
Tech

Google execs knew ‘Incognito mode’ failed to protect privacy, suit claims

November 20, 2022

POPULAR NEWS

Capitol Police Release New Video of Officer Lila Morris Beating Already Unconscious Rosanne Boyland with Stick on Jan. 6 -- But Video Is Super Pixilated to Protect Morris

Capitol Police Release New Video of Officer Lila Morris Beating Already Unconscious Rosanne Boyland with Stick on Jan. 6 — But Video Is Super Pixilated to Protect Morris

November 19, 2022
Euphoria Season 2: Maddy and Cassie's New Year's Eve Outfits

Euphoria Season 2: Maddy and Cassie’s New Year’s Eve Outfits

November 19, 2022
See Photos of Type O Negative's Peter Steele Through the Years

See Photos of Type O Negative’s Peter Steele Through the Years

November 19, 2022
Helena Citron was a prisoner at Auschwitz, the notorious concentration camp in Poland, when she was romanced by Nazi officer Franz Wunsch.

Movie reveals affair between Auschwitz prisoner, Nazi captor

November 20, 2022
Last Night in Soho's Costume Designer On the Movie's Fashion

Last Night in Soho’s Costume Designer On the Movie’s Fashion

November 19, 2022

EDITOR'S PICK

A general view of the exhaust system after the catalytic converter was stolen from a 2008 Toyota Prius as seen at the Meineke Car Car Center in Hawthorne, NJ on October 2, 2021.

More than 5000 catalytic converter thefts reported in NYC

November 20, 2022
Page Hamilton Tells Young Musicians to Be Honest With Their Music

Page Hamilton Tells Young Musicians to Be Honest With Their Music

November 19, 2022
Microsoft Flight Simulator

Microsoft Flight Simulator – MilViz Talks Upcoming Aircraft: Beaver, Otter, ATR-72, C-130, & More; McClellan-Palomar Airport Announced

November 19, 2022
A Chore Coat Is Absolutely Essential and These are the Best: Carhartt, Uniqlo, Buck Mason, and More

A Chore Coat Is Absolutely Essential and These are the Best: Carhartt, Uniqlo, Buck Mason, and More

November 20, 2022

About

REGIONAL POSTS Web Magazine is an online magazine covering international news, politics, technology, health, education, and much more.Read More.

Follow Us

Submit a News | Write For Us

Feel free to contact us for submission queries. via contact form or email us at : [email protected]

  • Home
  • About
  • Contact
  • Privacy
  • Terms
  • DCMA
  • Sitemap
  • Submit

© 2021 Regionalposts.com

No Result
View All Result
  • News
    • Sports
    • Politics
  • Business
    • Crypto
    • Marketing
  • Lifestyle
    • Entertainment
    • Fashion
    • Food
  • Tech
    • Gaming
    • Gadgets
  • Science
  • Health
  • Travel
  • World

© 2021 Regionalposts.com