A to Z of Excel Functions: The BYROW Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the BYROW function.

The BYROW function

I thought it was time to pen an article on the BYROW function...

BYROW works very similarly to BYCOL (it is analogous to the relationship between HLOOKUP and VLOOKUP).  This function applies a LAMBDA to each row and returns an array of the results.  Its syntax is as follows:

It has the following arguments:

•  array: this is required, and represents an array to be separated by row
• lambda: an optional argument, this is a LAMBDA that takes a column as a single parameter and calculates just one result.

I have made up some average monthly temperatures for Melbourne (Australia):

I have called this Excel Table Temps, but it is a permanent name…

BYROW effectively produces a column vector, summing up each row of the table Temps.

If I want the year-on-year comparisons for each month where the average temperature is above 15 degrees Celsius, I can avoid using a “helper column” and instead use the formula

Here, the LAMBDA function returns values where the average temperature for the year is above 15 degrees Celsius.

This will return the following array:

Once you get the hang of LAMBDA helper functions such as BYROW, you will begin to wonder how you ever managed without them.

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.