Google Sheets gains LAMBDA and helper functions


With new support for named functions, LAMBDA and helper functions, spreadsheet calculations — especially with arrays — may be more efficient and elegant.

Image: Andy Wolber/TechRepublic.

In August 2022, Google announced support for named functions and the addition of the LAMBDA function — along with several related functions — to Google Sheets. These features make functions not only easier to share between sheets, but also more elegant, since one well-crafted calculation may operate on an entire set of data.

These new features began to be available in Google Sheets for most people on Aug. 24, 2022. Frontline, Nonprofits and legacy G Suite Basic and Business customers will not gain these features. The basics of what you need to know about these functions, with a few examples, are covered below.

What’s new in Google Sheets?

Named functions

Named functions tend to make complex calculations more comprehensible, since well-chosen words may convey meaning faster than formulas for most people. For example, a person likely can figure out what the formula (F – 32) x 5/9 is for, but a custom name such as CONVERTTOCELSIUS can better convey that this function converts degrees Fahrenheit to Celsius.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

Even better, you may import a named function created and used in one Google Sheets file to a different Google Sheets file. Support pages indicate that this feature will be found in the Data | Named functions menu, although as I write this on the last day of August 2022, the Named function option was not yet available in my Google account.

LAMBDA and helper functions

The added LAMBDA and helper functions let you create custom functions that operate on values passed to the function. Importantly, LAMBDA can work with arrays, or for people not familiar with that term, a collection of data or values. LAMBDA and helper functions may make sophisticated spreadsheets easier to manage and maintain, since you create the function and pass parameters to it.

BYROW and BYCOL functions

Respectively, BYCOL makes a new row of data based on grouping array items by column — hence the name! — while BYROW makes a new column of data based on grouping array items by row.

In an array of weather data where a wind speed is reported in a calendar grid format, for example, BYROW might be used to return the maximum wind speed in any week (Figure B), while BYCOL might be used to return the maximum wind speed on a particular day (Figure A).

Figure A

The BYCOL function in cell A7 identifies the maximum number from the items in the array above for each column (e.g., 36 is the maximum of the numbers in column A).

Figure B

The BYROW function in cell I2 identifies the maximum number of the items in the array to the left for each row (e.g., 42 is the maximum of the numbers in row 2).

MAKEARRAY function

MAKEARRAY offers a way to create a calculated set of rows and columns. For example, MAKEARRAY might generate a set of calculated data, such as a multiplication table (Figure C), or a set of randomly generated data.

FIgure C

The MAKEARRAY function in cell A1 generated all of the other data displayed in this sheet.

MAP function

MAP lets you create a new array with the LAMBDA applied to your selected array. Continuing our weather-related examples, this might be used on an array of temperatures (e.g. cells A1:G5 in Figure D) to create a new array with only days that are below 32 degrees Fahrenheit identified as cold (e.g. cells A8:G11 in Figure D).

Figure D

The MAP function in cell A8 generated all of the items in the range from A8 to G11. This one-cell entry efficiently operates on the array above to create the text descriptions as shown.

REDUCE function

REDUCE applies a LAMBDA to items in an array, item by item, repeatedly. Often, this might be used to perform a calculation on only some values, such as adding only numbers that are even. In a weather context, this might be used to calculate minutes of sunlight, but only counting those days where the minutes of sunlight exceeds 240 minutes (Figure E).

Figure E

The REDUCE function in cell A8 with the LAMBDA function displayed adds up all the numbers in the A2:G5 range that are greater than or equal to 240.

SCAN function

SCAN, much like REDUCE, applies a LAMBDA to each value in an array, item by item. However, unlike REDUCE, SCAN creates a new array with the same number of items as the original. You might use a SCAN to apply a calculation to items in an array.

In my example, the first set of data might represent a set of daily rainfall numbers. The SCAN function creates an array of a corresponding size, but with every cell displaying the cumulative total of rainfall, as shown in Figure F. Again, what is notable here is that all the numbers in the second array are created with only a single cell entry.

Figure F

The SCAN function in cell A8 represents a cumulative total — calculated cell by cell, row by row — of the numbers in the A2:G5 range. The single SCAN function entered in cell A8 generated and filled in each and every cell from A8 to G11 with cumulative totals of the data above.

XLOOKUP and XMATCH functions

In addition to the above functions, Google announced two more functions that as of Aug. 31, 2022 had not yet become available in Google Sheets for any of my various Google Workspace accounts: XLOOKUP and XMATCH.

XLOOKUP lets you search an array and return an item that corresponds to a match. For example, in a list of states and state capitals, a XLOOKUP of a state could return the corresponding state capital. XMATCH searches a set of cells and returns the item’s position. For example, an XMATCH of an alphabetical list of U.S. states for Alaska could return the value 2, since only Alabama precedes it alphabetically.

What’s your experience with functions?

If you use named functions in Google Sheets, have you and your colleagues chosen any sort of function naming standards? Have the LAMBDA features streamlined how you work with arrays of data in Google Sheets? Which of the above functions do you use most often? Why? Mention or message me on Twitter (@awolber) to let me know what your experience with LAMBDA and the LAMBDA helper functions in Google Sheets has been.





Source link

Share

Leave a Reply

%d bloggers like this: