Excel Formulas

Unqork supports a large set of Excel-compatible formulas that work the same way as their Excel equivalents. Creators familiar with Excel can use them here without additional configuration.

For formulas unique to Unqork, see Unqork Formulas.

Note: Some formulas have both a modern name (like STDEV.S) and a legacy alias (like STDEVS). Both are supported and behave identically.

Logical

Formula	Description
`AND(value1, value2, ...)`	Returns `true` if all arguments are true.
`EQ(value1, value2)`	Returns `true` if the two values are equal.
`FALSE()`	Returns the logical value `false`.
`GTE(value1, value2)`	Returns `true` if value1 is greater than or equal to value2.
`IF(condition, valueIfTrue, valueIfFalse)`	Returns one value if a condition is true, another if false.
`LT(value1, value2)`	Returns `true` if value1 is less than value2.
`LTE(value1, value2)`	Returns `true` if value1 is less than or equal to value2.
`NE(value1, value2)`	Returns `true` if the two values are not equal.
`NOT(logical)`	Reverses the logical value of its argument.
`OR(value1, value2, ...)`	Returns `true` if any argument is true.
`SWITCH(expression, value1, result1, ...)`	Evaluates an expression against a list of values and returns the result for the first match.
`TRUE()`	Returns the logical value `true`.
`XOR(value1, value2, ...)`	Returns `true` if an odd number of arguments are true.

Math

Formula	Description
`ABS(number)`	Returns the absolute value of a number.
`ADD(number1, number2)`	Adds two numbers.
`CEILING(number, significance)`	Rounds a number up to the nearest multiple of significance.
`COMBIN(n, k)`	Returns the number of combinations for a given number of items.
`DEGREES(angle)`	Converts radians to degrees.
`DIVIDE(number1, number2)`	Divides two numbers.
`EVEN(number)`	Rounds a number up to the nearest even integer.
`EXP(number)`	Returns e raised to the power of a number.
`FACT(number)`	Returns the factorial of a number.
`FLOOR(number, significance)`	Rounds a number down to the nearest multiple of significance.
`GCD(number1, number2, ...)`	Returns the greatest common divisor.
`INT(number)`	Rounds a number down to the nearest integer.
`LCM(number1, number2, ...)`	Returns the least common multiple.
`LN(number)`	Returns the natural logarithm of a number.
`LOG(number, base)`	Returns the logarithm of a number to a specified base.
`LOG10(number)`	Returns the base-10 logarithm of a number.
`MOD(number, divisor)`	Returns the remainder after division.
`MROUND(number, multiple)`	Rounds a number to the nearest specified multiple.
`MULTIPLY(number1, number2)`	Multiplies two numbers.
`ODD(number)`	Rounds a number up to the nearest odd integer.
`PI()`	Returns the value of π.
`POWER(number, power)`	Returns a number raised to a power.
`PRODUCT(number1, number2, ...)`	Multiplies all the numbers together.
`QUOTIENT(numerator, denominator)`	Returns the integer portion of a division.
`RADIANS(angle)`	Converts degrees to radians.
`RAND()`	Returns a random number between 0 and 1.
`RANDBETWEEN(bottom, top)`	Returns a random integer between two values.
`ROUND(number, digits)`	Rounds a number to a specified number of digits.
`ROUNDDOWN(number, digits)`	Rounds a number down toward zero.
`ROUNDUP(number, digits)`	Rounds a number up away from zero.
`SIGN(number)`	Returns the sign of a number: 1, -1, or 0.
`SQRT(number)`	Returns the square root of a number.
`SUM(number1, number2, ...)`	Adds all numbers together.
`SUMIF(range, criteria, sumRange)`	Adds numbers that meet a condition.
`SUMIFS(sumRange, range1, criteria1, ...)`	Adds numbers that meet multiple conditions.
`SUMPRODUCT(array1, array2, ...)`	Returns the sum of products of corresponding array elements.
`TRUNC(number, digits)`	Truncates a number to an integer by removing the decimal portion.

Text

Formula	Description
`CHAR(number)`	Returns the character specified by a code number.
`CLEAN(text)`	Removes all non-printable characters from text.
`CODE(text)`	Returns the numeric code for the first character in a text string.
`CONCATENATE(text1, text2, ...)`	Joins several text strings into one.
`DOLLAR(number, decimals)`	Converts a number to text using currency format.
`EXACT(text1, text2)`	Checks if two text strings are identical (case-sensitive).
`FIND(findText, withinText, startNum)`	Finds a text string in another (case-sensitive).
`FIXED(number, decimals)`	Formats a number as text with a fixed number of decimals.
`LEFT(text, numChars)`	Returns the leftmost characters from a text string.
`LEN(text)`	Returns the number of characters in a text string.
`LOWER(text)`	Converts text to lowercase.
`MID(text, startNum, numChars)`	Returns a specific number of characters from a text string.
`PROPER(text)`	Capitalizes the first letter of each word.
`REPLACE(text, startNum, numChars, newText)`	Replaces part of a text string with different text.
`REPT(text, numberTimes)`	Repeats text a given number of times.
`RIGHT(text, numChars)`	Returns the rightmost characters from a text string.
`SEARCH(findText, withinText, startNum)`	Finds a text string in another (case-insensitive).
`SUBSTITUTE(text, oldText, newText, instanceNum)`	Replaces existing text with new text.
`T(value)`	Returns the value if it is text, otherwise returns an empty string.
`TEXT(value, format)`	Converts a value to text in a specific number format.
`TEXTJOIN(delimiter, ignoreEmpty, text1, ...)`	Joins text strings using a delimiter, optionally ignoring empty values.
`TRIM(text)`	Removes extra spaces from text.
`UNICHAR(number)`	Returns the Unicode character for a given number.
`UNICODE(text)`	Returns the Unicode code point for the first character of a text string.
`UPPER(text)`	Converts text to uppercase.
`VALUE(text)`	Converts a text string that represents a number to a number.

Date & Time

Formula	Description
`DATE(year, month, day)`	Returns the date value for a specified year, month, and day.
`DATEVALUE(dateText)`	Converts a date stored as text to a date value.
`DAY(date)`	Returns the day of the month for a given date.
`DAYS(endDate, startDate)`	Returns the number of days between two dates.
`DAYS360(startDate, endDate)`	Returns the number of days between two dates based on a 360-day year.
`EDATE(startDate, months)`	Returns the date a specified number of months before or after a start date.
`EOMONTH(startDate, months)`	Returns the last day of the month a specified number of months away.
`HOUR(time)`	Returns the hour from a time value.
`ISOWEEKNUM(date)`	Returns the ISO week number for a given date.
`MINUTE(time)`	Returns the minute from a time value.
`MONTH(date)`	Returns the month from a date.
`NETWORKDAYS(startDate, endDate)`	Returns the number of working days between two dates.
`NOW()`	Returns the current date and time.
`SECOND(time)`	Returns the seconds from a time value.
`TIME(hour, minute, second)`	Returns the time value for a specific hour, minute, and second.
`TIMEVALUE(timeText)`	Converts a time stored as text to a time value.
`TODAY()`	Returns today's date.
`WEEKDAY(date, returnType)`	Returns the day of the week for a date.
`WEEKNUM(date, returnType)`	Returns the week number for a date.
`WORKDAY(startDate, days)`	Returns the date a number of working days from a start date.
`YEAR(date)`	Returns the year from a date.
`YEARFRAC(startDate, endDate, basis)`	Returns the fraction of a year between two dates.

Information

Formula	Description
`ISBLANK(value)`	Returns `true` if the value is empty.
`ISEVEN(number)`	Returns `true` if the number is even.
`ISLOGICAL(value)`	Returns `true` if the value is a logical value.
`ISNONTEXT(value)`	Returns `true` if the value is not text.
`ISNUMBER(value)`	Returns `true` if the value is a number.
`ISODD(number)`	Returns `true` if the number is odd.
`ISTEXT(value)`	Returns `true` if the value is text.

Statistical

Formula	Description
`AVERAGE(number1, number2, ...)`	Returns the average of the arguments.
`AVERAGEIF(range, criteria, averageRange)`	Returns the average of values that meet a condition.
`AVERAGEIFS(averageRange, range1, criteria1, ...)`	Returns the average of values that meet multiple conditions.
`COUNT(value1, value2, ...)`	Counts how many numbers are in the arguments.
`COUNTA(value1, value2, ...)`	Counts how many values (including text) are in the arguments.
`COUNTBLANK(range)`	Counts the number of empty cells.
`COUNTIF(range, criteria)`	Counts the number of cells that meet a condition.
`COUNTIFS(range1, criteria1, ...)`	Counts the number of cells that meet multiple conditions.
`LARGE(array, k)`	Returns the k-th largest value.
`MAX(number1, number2, ...)`	Returns the maximum value.
`MEDIAN(number1, number2, ...)`	Returns the median value.
`MIN(number1, number2, ...)`	Returns the minimum value.
`RANK.EQ(number, ref, order)`	Returns the rank of a number in a list.
`SMALL(array, k)`	Returns the k-th smallest value.
`STDEV.S(number1, number2, ...)`	Returns the standard deviation based on a sample.
`STDEV.P(number1, number2, ...)`	Returns the standard deviation based on the entire population.
`SUBTOTAL(functionNum, ref1, ...)`	Returns a subtotal in a list.
`VAR.S(number1, number2, ...)`	Returns the variance based on a sample.
`VAR.P(number1, number2, ...)`	Returns the variance based on the entire population.

Financial

Formula	Description
`FV(rate, nper, pmt, pv, type)`	Returns the future value of an investment.
`IRR(values, guess)`	Returns the internal rate of return for a series of cash flows.
`MIRR(values, financeRate, reinvestRate)`	Returns the modified internal rate of return.
`NPV(rate, value1, value2, ...)`	Returns the net present value of an investment.
`PMT(rate, nper, pv, fv, type)`	Returns the periodic payment for a loan.
`PV(rate, nper, pmt, fv, type)`	Returns the present value of an investment.
`RATE(nper, pmt, pv, fv, type, guess)`	Returns the interest rate per period.
`SLN(cost, salvage, life)`	Returns the straight-line depreciation of an asset.
`XIRR(values, dates, guess)`	Returns the internal rate of return for irregular cash flows.
`XNPV(rate, values, dates)`	Returns the net present value for irregular cash flows.

Lookup & Reference

Formula	Description
`CHOOSE(indexNum, value1, value2, ...)`	Returns a value from a list based on an index number.
`COLUMN(reference)`	Returns the column number of a reference.
`COLUMNS(array)`	Returns the number of columns in a reference.
`MATCH(lookupValue, lookupArray, matchType)`	Returns the position of a value in a range.
`ROW(reference)`	Returns the row number of a reference.
`ROWS(array)`	Returns the number of rows in a reference.
`TRANSPOSE(array)`	Returns a transposed array.

Engineering

Engineering formulas are available but rarely used in standard Unqork applications. They include number base conversions (BIN2DEC, DEC2HEX, and so on), bitwise operations (BITAND, BITOR, BITXOR, and so on), and complex number functions (IMABS, IMSUM, and so on). For details on these formulas, see standard Excel documentation.

Changelog

Date	Change
—	Initial publication.

Documentation Index