Feature: 100+ new functions

November 12, 2021

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.

Read the complete list of supported functions »