GS-Calc 23.1 has been released

GS-Calc 23.1 is a maintenance update that adds or modifies several
functions to improve the compatibility with Google Sheets and Excel 365.

  • Added: SWITCH(expression, case1, value1, [case2, value2, …], [default])

    Tests the value of the “expression” against the “case1, case2, …”
    values and if a matching case value is found, returns the
    corresponding “value”. If no matching “case” value is found, the
    #n/a! error value is returned.

    =SWITCH(A5, “dog”, “mammal”, “falcon”, “bird”)
    =SWITCH(A1:A10, 1, “one”, 2, “two”, 3, “three”, “etc.”)

  • Added: IFS(condition1, value1, [condition2, value2, …])

    Evaluates multiple conditions “condition1, condition2, …” and
    returns a value that corresponds to the first true condition. The
    evaluation is performed from left to right. If conditions are
    arrays/ranges, all must have the same size.

    =IFS(A1 > 0.75, "above 75 % ", A1 > 0.5, "above 50 % ", A1 > 0.25,
    "above 25 % ")

    Note: Google Sheets seems to treat text as always bigger than
    numbers and
    =IFS(“0.55” > 0.75,…
    produces "above 75 % ".
    In GS-Calc basic comparison operators always generate the #VALUE!
    error if the types don’t match and for all other math
    operators/functions the text-to-number conversion is automatic.

    =IFS({1, 2, 3, 4, 5} > 2, " > 2", {1, 2, 3, 4, 5} = 1, “equal 1”)

    returns {“equal 1”, #N/A!, " > 2", " > 2", " > 2" }

    Note: In GS-Calc array/spilling formulas don’t require any
    additional actions, whereas in Google Sheets you must enclose
    the function in the ARRAYFORMULA() function to obtain the above
    result. Otherwise Google Sheets uses just the 1st value from each
    array and returns a single value.

  • Added: BITAND(), BITOR(), BITXOR(), BITNOT()
    Previously AND(), OR(), XOR(), NOT() were all bit functions
    which is inconsistent with the above other spreadsheets and
    could result in mistakes when, for example, you try to use and(2,4)
    for logical evaluation.
    With this update AND(), OR(), XOR(), NOT() now evaluate strictly
    the logical 1/0, true/false values.

    Note: BITNOT() is available in Excel 365, but not in Google Sheets.
    In Excel it operates with 16 bit values, which is now the default
    value in GS-Calc. However, you can also specify the number of bits
    as the 2nd argument.

  • Added: IFNA()
    Similarly to IFERROR(), but using only the #N/A! error code.
    It’s not available in Google Sheets.