[ad_1]
With new support for named functions, LAMBDA and helper functions, spreadsheet calculations — especially with arrays — may be more efficient and elegant.
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
Figure B
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
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
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
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
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.
[ad_2]
Source link
