Thanks to our new formula engine, we have been able to add support for 102 new functions and a few new operators to this release, for a grand total of 387 functions.

Many of the functions are new — either Calcapp exclusives, like ISEMAIL, or transplants from Microsoft Excel 2021, like XLOOKUP — while many others are classic functions found in most spreadsheets.

Calcapp went from supporting 20 functions to 244 functions overnight in 2016. At that time, we didn’t have support for arrays. As a result, we couldn’t add functions like SUMIF and FVSCHEDULE, as there was no way to express these functions without arrays. We’re thrilled to finally be able to bring you these classic functions.

We haven’t been content to just bring Calcapp up to speed with the older versions of Excel, we have added support for all eight dynamic array functions introduced with Excel 2021 (and Microsoft 365 before that): FILTER, RANDARRAY, SEQUENCE, SORT, SORTBY, UNIQUE, XLOOKUP and XMATCH.

Many of the new functions come with Calcapp extensions. FILTER, XLOOKUP and all conditional functions (SUMIF, SUMIFS and others) all use some kind of a condition parameter to determine which element or elements to select. Traditionally, this is done by providing either a single value the function should look for, a text string specifying a condition or a logical array determining which elements to keep.

Calcapp gives you a choice of doing it the traditional way, or using formula fragments, which makes functions like SUMIF considerably more powerful. A formula fragment has access to the full power of the formula language (including the ability to reference values everywhere in the app and the ability to invoke arbitrary functions). Check out the links above to learn more.

(If you have been exposed to a traditional programming language like
JavaScript, you may be interested to know that a *formula
fragment* is our name for an *anonymous function*, or a
*lambda*. We think that our name is easier to understand for
people used to spreadsheets.)

Last but not least, all functions and operators now come with detailed documentation, with thousands of examples tailored for Calcapp and app creation.

## Further reading

- Feature: IF without all the parentheses, SWITCH and IFBLANK
- Feature: Look up values with XLOOKUP, MATCH and INDEX
- Feature: Support for SUMIF, SUMIFS and other conditional functions
- Feature: Use | to join arrays together with other arrays and values
- Feature: Use IF to conditionally include array elements
- Feature: Dramatically improved documentation for formulas and properties, with thousands of examples

## New date and time functions

ISOWEEKNUM | Returns the ISO week number of the given date, from 1 through 54. |

NETWORKDAYS | Returns the number of working days between two dates. |

WORKDAY.INTL | Returns a date advanced or set back a number of working days. |

## New financial functions

FVSCHEDULE | Returns the future value of a lump sum, with changing future interest rates. |

IRR | Calculates the internal rate of return of a series of cash flows. |

MIRR | Returns the modified internal rate of return of a series of cash flows. |

XIRR | Calculates the internal rate of return of a series of irregular cash flows. |

XNPV | Returns the net present value of an investment with irregular cash payments. |

## New information functions

SIZE | Returns the size of the given array, that is, the number of elements that are contained in the array. |

TOCOLOR | Returns the given value converted to a color. |

TYPE | Returns the type of the given value. |

## New logical functions

IFBLANK | Returns a value unchanged if it is not blank and a different value otherwise. |

IFS | Returns one of the given values based on logical conditions. |

SWITCH | Tests a given parameter for equality against a list of parameters and returns a parameter associated with the parameter which matched the first parameter. |

## New lookup functions

FILTER | Filters the first array using the second parameter. |

INDEX | Returns the array element at the given position. |

MAP | Applies the transformation specified by the given formula fragment to all array elements and returns the resulting array. |

MATCH | Returns the position that a given value appears at in the given lookup array, or returns an #N/A error if the value cannot be found. |

REDUCE | Reduces an array of values to a single value using a user-supplied formula. |

REVERSEARRAY | Reverses the given array and returns it. |

SORT | Sorts and returns the given array. |

SORTBY | Sorts and returns a given array, where the sort order is based on sort arrays given to this function. |

UNIQUE | Returns a version of the given array with all duplicate values removed. |

XLOOKUP | Returns the array element in the second array with the same position as an element in the first array which is equal to a given value. |

XMATCH | Returns the position that a given value appears at in the given lookup array, or returns an #N/A error if the value cannot be found. |

## New math functions

MDETERM | Returns the matrix determinant of the given matrix. |

MINVERSE | Returns the inverse matrix of the given matrix. |

MMULT | Returns the matrix product of the given matrices. |

MUNIT | Returns the unit matrix for the given dimension. |

PRODUCTIF | Returns the product of the array elements that satisfy a condition (multiplying them together). |

PRODUCTIFS | Returns the product of all array elements which satisfy one or several conditions (multiplying them together). |

RANDARRAY | Returns an array with random numbers. |

SEQUENCE | Returns an array containing a sequence of numbers. |

SUMIF | Returns the sum of the array elements that satisfy a condition (adding them together). |

SUMIFS | Returns the sum of all array elements which satisfy one or several conditions (adding them together). |

SUMPRODUCT | Multiplies all the given arrays together and returns the sum of products. |

SUMX2MY2 | Returns the sum of the differences between the corresponding squared elements of the given two arrays. |

SUMX2PY2 | Returns the sum of the squares of all elements of the given two arrays. |

SUMXMY2 | Returns the sum of the squared differences between the corresponding elements of the given two arrays. |

## New operators

| Array concatenation | Joins together two arrays, or joins a value to an array, and returns the result. |

-- Double negation | Returns 0 if the given operand is FALSE, 1 if it is TRUE and the operand unchanged if is a number. |

== Simple equality | Returns whether the first value is equal to the second value. |

!= Simple inequality | Returns whether the first value is not equal to the second value. |

## New statistical functions

AVERAGEA | Returns the average (arithmetic mean) of the parameters. |

AVERAGEIF | Returns the average (arithmetic mean) of the array elements that satisfy a condition. |

AVERAGEIFS | Returns the average (arithmetic mean) of all array elements which satisfy one or several conditions. |

CORREL | Returns the Pearson correlation coefficient of two sets of data. |

COUNT | Returns the number of values which can be interpreted as numbers. |

COUNTA | Returns the number of values which are not blank. |

COUNTBLANK | Returns the number of elements of the given array which are either blank or are equal to the empty text string. |

COUNTIF | Returns the number of array elements which satisfy a condition. |

COUNTIFS | Returns the number of array elements which satisfy one or several conditions. |

COVARIANCE.P | Returns the population covariance, that is, the covariance of the product of paired deviations, for an entire population. |

COVARIANCE.S | Returns the sample covariance, that is, the covariance of the product of paired deviations, for a sample of the population. |

FORECAST | Fits a straight line to data using linear regression and returns the vertical coordinate of a point on that line. |

FREQUENCY | Returns the frequency distribution of the values of the first array when grouped into the intervals of the second array. |

GROWTH | Returns the points of an exponential curve of the form y = b * m ^ x through the given data points using linear regression. |

INTERCEPT | Fits a straight line to data using linear regression and returns its intercept on the vertical axis. |

LARGE | Returns the nth largest number in the given array, where n is given as the second parameter. |

LINEST | Finds a straight line, y = m * x + b, that best fits the given data, and returns an array containing two values corresponding to m and b in the equation above. |

MAXA | Returns the largest number of the given numbers. |

MAXIFS | Returns the largest number of all array elements which satisfy one or several conditions. |

MINA | Returns the smallest number of the given numbers. |

MINIFS | Returns the smallest number of all array elements which satisfy one or several conditions. |

MODE.MULT | Returns the values that appear most frequently. |

MODE.SNGL | Returns the value that appears most frequently. |

PEARSON | Returns the Pearson correlation coefficient of two sets of data. |

PERCENTILE.EXC | Returns the number of the given array at the kth percentile, where k is a number between 0 and 1 (exclusive), which can also be specified with the % operator (. |

PERCENTILE.INC | Returns the number of the given array at the kth percentile, where k is a number between 0 and 1 (inclusive), which can also be specified with the % operator (. |

PERCENTRANK.EXC | Returns the percentile rank of a number in an array. |

PERCENTRANK.INC | Returns the percentile rank of a number in an array. |

PROB | Returns the probability that a value is inside a given interval. |

QUARTILE.EXC | Returns the number of the given array at the given quartile value. |

QUARTILE.INC | Returns the number of the given array at the given quartile value. |

RANK.AVG | Returns the rank of the given number in the given number array. |

RANK.EQ | Returns the rank of the given number in the given number array. |

RSQ | Returns the square of the Pearson correlation coefficient of two sets of data. |

SERIESSUM | Returns the sum of the first terms of a power series. |

SLOPE | Fits a straight line to data using linear regression and returns its slope. |

SMALL | Returns the nth smallest number in the given array, where n is given as the second parameter. |

STDEVA | Returns the sample standard deviation of the parameters. |

STDEVPA | Returns the population standard deviation of the parameters. |

STEYX | Fits a straight line to data using linear regression and returns the standard error of the actual vertical coordinates compared to the vertical coordinates of the straight line. |

TRIMMEAN | Returns the mean (average) of the given values, ignoring a percentage of array elements at the beginning and end of the given array. |

VARA | Returns the sample variance of the parameters. |

VARPA | Returns the population variance of the parameters. |

Z.TEST | Returns the one-tailed cumulative probability value of a Z-test. |

## New text functions

CHARAT | Returns the character at the given position. |

CONCAT | Joins any number of values together as a single text string and returns it. |

CONTAINS | Returns whether a text string contains another text string. |

ENDSWITH | Returns whether a text string ends with another text string. |

FORMATDATE | Returns a date as text. |

FORMATTIME | Returns a time as text. |

ISEMAIL | Returns whether the given text string is probably an email address. |

NEWLINE | Returns a line break character, which causes the character following it to appear on the next line. |

PARSEDATE | Interprets a text string as a date and returns it. |

PARSETIME | Interprets a text string as a time and returns it. |

REGEXEXTRACTALL | Extracts the parts of a text string identified by a JavaScript regular expression and returns an array with the extracted text. |

REGEXSPLIT | Divides a text string into parts using a regular expression as a delimiter, returning the parts as an array. |

REVERSE | Reverses the given text string and returns it. |

SEARCH | Finds a text string nestled within another text string and returns its position. |

STARTSWITH | Returns whether a text string starts with another text string. |

TEXTJOIN | Joins a number of text strings together, separating them with a delimiter, and returns the resulting text string. |

TEXTSPLIT | Divides a text string into parts using a delimiter, returning the parts as an array. |