Tag: formulas

11 New Analytical Functions In Google Sheets For 2023

Coming hot on the heels of last year’s batch of new lambda functions, Google recently announced another group of new analytical functions for Sheets.

Included in this new batch are the long-awaited LET function, 8 new array manipulation functions, a new statistical function, and a new datetime function.

Let’s begin with a look at the new array functions. The LET function is at the end of the post.

  1. TOROW Function
  2. TOCOL Function
  3. CHOOSEROWS Function
  4. CHOOSECOLS Function
  5. WRAPROWS Function
  6. WRAPCOLS Function
  7. VSTACK Function
  8. HSTACK Function
  9. MARGINOFERROR Function
  10. EPOCHTODATE Function
  11. LET Function

Continue reading 11 New Analytical Functions In Google Sheets For 2023

XMATCH Function in Google Sheets

Lambda Functions 10-Day Challenge💡 Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

The XMATCH function in Google Sheets is a new lookup function in Google Sheets that finds the relative position of a search term within an array or range. It’s an evolution of the original MATCH function.

Here’s a simple XMATCH function that finds the position of the search term “Cho Oyu” in the list of the highest mountains in the world:

=XMATCH(E2,A2:A15)

In the Sheet:

Xmatch Function Google Sheets Simple Example

And here’s how it works:

It looks for the search term from cell E2 (“Cho Oyu”) in the range A2:A15, then returns the position of the search text within this range. Note that the result is relative to the range, irrespective of the row number.

Xmatch Function Google Sheets Explainer

Notice how, unlike a regular MATCH function, you don’t have to specify the “0” search type for an exact match. It chooses the exact match, which is by far the most common use case, by default (in contrast to the MATCH function where you have to add the 0 to explicitly confirm exact matching). More on the search types below.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading XMATCH Function in Google Sheets

Google Sheets Advent Calendar

This year, I created a Google Sheets Advent Calendar, which you can see in action here:

Google Sheets Advent Calendar

It was a fun project with some interesting techniques, which are explored below.

You could easily modify it for your own example, or use these techniques in different scenarios.

Plus, if you’re too cheap to buy a physical advent calendar, this lets you enjoy the fun of opening a door each day to reveal something, but for free!
Continue reading Google Sheets Advent Calendar

BYCOL Function in Google Sheets – LAMBDA Helper Function

Lambda Functions 10-Day Challenge💡 Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

The BYCOL function in Google Sheets operates on an array or range and returns a new row array, created by grouping each column to a single value.

Here’s a simple example, showing a table of exam scores:

Bycol Function In Google Sheets

The formula in A8 is:

=BYCOL(A2:D6,LAMBDA(c,AVERAGE(c)))

The easiest way to think about using BYCOL is to think “what function can I use on a single column?” and then think of BYCOL as just repeating that operation across multiple columns.

BYCOL passes the input array to a lambda function, which takes an input column, called “c”, and calculates the average value for that column. It loops over each column and returns a row array of average values, one for each column.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading BYCOL Function in Google Sheets – LAMBDA Helper Function

BYROW Function in Google Sheets – LAMBDA Helper Function

Lambda Functions 10-Day Challenge💡 Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

The BYROW function in Google Sheets operates on an array or range and returns a new column array, created by grouping each row to a single value.

The value for each row is obtained by applying a lambda function on that row.

For example, this BYROW function calculates the average score of all three rows in the input array:

=BYROW(A2:D4,LAMBDA(row, AVERAGE(row)))

Which looks like this in our Google Sheet:

byrow Function In Google Sheets

(Of course, you could use three separate AVERAGE functions to perform this calculation.)

But the important thing here is how the BYROW operates. It’s much more like programming.

We pass an array of data to the BYROW function.

It then passes each row into a lamba function to calculate a single value for that row (the average in this example). The BYROW formula returns these values in a column array, with the same number of rows as the original input array.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading BYROW Function in Google Sheets – LAMBDA Helper Function