Wednesday, February 1, 2023

Google Sheets adds powerful new functions for advanced analysis

Update

[March 1, 2023]  Rollout has resumed. All Rapid Release and Scheduled Release domains will have access to this feature by March 14, 2023.


[February 22, 2023] We have paused rollout for this feature while we evaluate performance and quality. We will provide an update once rollout resumes.


What’s changing

Last year, we launched Named functions, along with several powerful functions in Google Sheets, including LAMBDA and XLOOKUP. 

Today, we’re excited to announce 11 additional functions that will introduce new concepts, provide you with more efficient functions, and help with more advanced analysis: 
  • EPOCHTODATE: Converts a Unix epoch timestamp in seconds, milliseconds, or microseconds to a datetime in UTC. 
  • MARGINOFERROR: Calculates the amount of random sampling error given a range of values and a confidence level. 
  • TOROW: Transforms an array or range of cells into a single row. 
  • TOCOL: Transforms an array or range of cells into a single column. 
  • CHOOSEROWS: Creates a new array from the selected rows in the existing range. 
  • CHOOSECOLS: Creates a new array from the selected columns in the existing range. 
  • WRAPROWS: Wraps the provided row or column of cells by rows after a specified number of elements to form a new array. 
  • WRAPCOLS: Wraps the provided row or column of cells by columns after a specified number of elements to form a new array. 
  • VSTACK: Appends ranges vertically and in sequence to return a larger array. 
  • HSTACK: Appends ranges horizontally and in sequence to return a larger array. 
  • LET: Assigns name with the value_expression results and returns the result of the formula_expression. The formula_expression can use the names defined in the scope of the LET function. The value_expressions are evaluated only once in the LET function even if the following value_expressions or the formula_expression use them multiple times. 

Who’s impacted 

End users 


Why you’d use it 

In addition to providing advanced statistical calculations like margin of error, these functions provide new functionality in the form of: 
  • Powerful new ways to manipulate and work with arrays using TOROW, TOCOL, WRAPROWS, WRAPCOLS, CHOOSEROWS, CHOOSECOLS, VSTACK, and HSTACK. 
  • Converting Unix EPOCH dates to readable formats without the need for manual calculations using EPOCHTODATE. 
  • More efficient and simpler to read complex formulas that use LET to assign names to calculation results.

Getting started 

  • Admins: There is no admin control for this feature. 
  • End users: In Google Sheets, all functions can be inserted using the function autocomplete after pressing the equal symbol, using the insert menu, or the function dropdown in the toolbar. 

Rollout pace 


Availability 

  • Available to all Google Workspace customers, as well as legacy G Suite Basic and Business customers 
  • Available to users with personal Google Accounts 

Resources