Product Updates - May 22, 2021 - Array formula syntax

🌟 What's new?
1

Today we’re happy to share that we’ve released another set of improvements to the Spreadsheet.com formula engine along with support for three additional formula functions.

Additional formula functions

Spreadsheet.com now has support for the following additional formula functions:

  • ARRAYFORMULA
    Enables the use of non-array functions with arrays. ARRAYFORMULA can be automatically added directly to a formula expression using the CONTRL+SHIFT+ENTER key combination.

    Note that array formula spillover capabilities whereby values returned from an array formula spill over into multiple rows and/or columns is not yet supported. This is a forthcoming feature planned for a near future release.

    Example: ARRAYFORMULA(SUM(IF(A1:A10>10,1,0)*A1:A10))
    Sums all the values which are greater than 10 in the range A1:A10. ARRAYFORMULA can be used in combination all formulas like IF, ADD, DIVIDE, etc.

  • ARRAY_CONSTRAIN
    Constrains an array result to a specified size.

    Example: ARRAY_CONSTRAIN(A1:C10, 2, 3)
    Returns a 2 by 3 array of cell values from the range A1:C10.

  • IFS
    Evaluates multiple conditions and returns a value that corresponds to the first true condition.

    Example: IFS(A1>90, "A", A1>80, "B", A1>70, "C")
    Returns the letter grade of A1 depending on its score. For instance, a 92 would be an A.

Formula engine enhancements

  • Array literals
    Spreadsheet.com now supports array literals in formula expressions. You can directly pass inline arrays as parameters to formula functions.

    Example 1:
    Sum({A1,10,5})
    Results in 25 if A1 is 10.

    Example 2:
    VLOOKUP(WEEKDAY("03-09-2021"),{1, "Sunday";2, "Monday";3, "Tuesday";4, "Wednesday";5, "Thursday";6, "Friday"; 7, "Saturday"}, 2, FALSE)

  • Data type promotion improvements
    With the latest enhancement to data type promotion rules, the Spreadsheet.com formula engine now evaluates the resultant value based on the data types of the arguments.

    Example1: If A1 = 25%, A2 = $2000 and B1 = A1*A2, then B1 will evaluate to $500. Previously Spreadsheet.com used to determine the resultant data type based on the first argument’s cell data type, which is now corrected.

    Example2: 1/21/2020 - 1/11/2020 will now correctly result in 10.

  • Enhanced spreadsheet import performance
    The speed of importing formula-heavy workbooks (i.e. > 1,000 cells containing formulas) has been significantly improved in many cases.

6 Likes
2

Spreadsheet.com Team:

Really appreciate the updates, including the ARRAYFORMULA functions :grinning: I had a need for this and have already put it to use!

5 Likes