3  Functions

The following built-in functions are available:

Math Functions

Function	Description
ABS(Number)	Absolute value of Number.
ACOS(Number)	Returns the arccosine, or inverse cosine, of Number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to PI.
ARABIC(Text)	Converts a Roman numeral (Text) to a number. Note that Text is case-insensitive. An invalid or malformed numeral returns NaN. ARABIC("MCMXCIX") = 1999
ASIN(Number)	Returns the arcsine, or inverse sine function, of Number, where -1 <= Number <= 1. The arcsine is the angle whose sine is Number. The returned angle is given in radians where -pi/2 <= angle <= pi/2.
ATAN(x)	Returns the principal value of the arc tangent of x, expressed in radians.
ATAN2(y, x)	Returns the principal value of the arc tangent of y,x, expressed in radians.
CEIL(Number)	Smallest integer not less than Number. CEIL(-3.2) = -3 CEIL(3.2) = 4
CLAMP(Number, Start, End)	Constrains Number within the range of Start and End.
COMBIN(Number, NumberChosen)	Returns the number of combinations for a given number (NumberChosen) of items from Number of items. Note that for combinations, order of items is not important.
COS(Number)	Cosine of the angle Number in radians.
COSH(Number)	Hyperbolic cosine of Number.
COT(Number)	Cotangent of Number.
EXP(Number)	Euler to the power of Number.
EVEN(Number)	Returns Number rounded up to the nearest even integer. Values are always rounded away from zero (e.g., EVEN(-3) = -4).
FAC(Number)	Returns the factorial of Number. The factorial of Number is equal to 1*2*3*…* Number
FACT(Number)	Alias for FAC().
FLOOR(Number)	Returns the largest integer not greater than Number. FLOOR(-3.2) = -4 FLOOR(3.2) = 3
ISERR(Expression)	Returns true if Expression evaluates to NaN.
ISERROR(Expression)	Alias for ISERR().
ISEVEN(Number)	Returns true if Number is even, false if odd.
ISNA(Expression)	Alias for ISERR().
ISNAN(Expression)	Alias for ISERR().
ISODD(Number)	Returns true if Number is odd, false if even.
LN(Number)	Natural logarithm of Number (base Euler).
LOG10(Number)	Common logarithm of Number (base 10).
MIN(Number1, Number2, …)	Returns the smallest value from a specified range of values.
MAX(Number1, Number2, …)	Returns the largest value from a specified range of values.
MAXINT()	Returns the largest integer that the parser can process.
MOD(Number, Divisor)	Returns the remainder after Number is divided by Divisor. The result has the same sign as divisor.
NA	Returns an invalid value (i.e., Not-a-number).
NAN	Alias for NA().
NCR(Number, NumberChosen)	Alias for COMBIN().
NPR(Number, NumberChosen)	Alias for PERMUT().
NUMBERVALUE(Text, DecimalSeparator, GroupSeparator)	Converts Text to a number, independent of the current locale. DecimalSeparator* and GroupSeparator are optional and default to . and ,. If only DecimalSeparator is given, then GroupSeparator defaults to whichever of . and , it is not. Only the first character of a separator is used, and the two cannot be the same. Trailing percent signs are cumulative, so NUMBERVALUE("9%%") = 0.0009. An empty Text returns 0. A group separator before the decimal separator is ignored. After it, NaN is returned. NaN is also returned if the decimal separator appears more than once, or if Text does not fully convert. Spaces at the start and end of Text are ignored, but a space inside it is not (refer to the warning below). NUMBERVALUE("2.500,27", ",", ".") = 2500.27 NUMBERVALUE("1,234.56") = 1234.56 NUMBERVALUE("3.5%") = 0.035 NUMBERVALUE("3 000", ".", " ") = 3000
ODD(Number)	Returns Number rounded up to the nearest odd integer. Values are always rounded away from zero (e.g., ODD(-4) = -5).
PERMUT(Number, NumberChosen)	Returns the number of permutations for a given number (NumberChosen) of items that can be selected Number of items. A permutation is any set of items where order is important. (This differs from combinations, where order is not important).
POW(Base, Exponent)	Raises Base to any power. For fractional exponents, Base must be greater than 0.
POWER(Base, Exponent)	Alias for POW().
RAND()	Generates a random floating point number within the range of 0 and 1.
ROUND(Number, NumDigits)	Number rounded to NumDigits decimal places. If NumDigits is negative, then Number is rounded to the left of the decimal point. (NumDigits is optional and defaults to zero.) ROUND(-11.6, 0) = 12 ROUND(-11.6) = 12 ROUND(1.5, 0) = 2 ROUND(1.55, 1) = 1.6 ROUND(3.1415, 3) = 3.142 ROUND(-50.55, -2) = -100
SIGN(Number)	Returns the sign of Number. Returns 1 if Number is positive, zero (0) if Number is 0, and -1 if Number is negative.
SIN(Number)	Sine of the angle Number in radians.
SINH(Number)	Hyperbolic sine of Number.
SQRT(Number)	Square root of Number.
TAN(Number)	Tangent of Number.
TGAMMA(Number)	Returns the gamma function of Number.
TRUNC(Number)	Discards the fractional part of Number. TRUNC(-3.2) = -3 TRUNC(3.2) = 3

ARABIC() is stricter than some spreadsheet programs, which accept malformed Roman numerals. For example, other programs may return 99 for ARABIC("IC"), even though I cannot subtract from C. TinyExpr++ returns NaN for that, since it is not a properly formed Roman numeral. A numeral is only accepted if:

• It uses only the letters I, V, X, L, C, D, and M (case-insensitive).
• I, X, and C repeat no more than four times in a row (e.g., IIII, XXXX, CCCC). This is an additive form for 4, 40, and 400 that remains common alongside the subtractive one. M repeats no more than three times, since a fourth would exceed the largest representable numeral, 3999 (MMMCMXCIX). V, L, and D never repeat.
• Subtraction is only allowed for I before V/X, X before L/C, and C before D/M.
• A repeated numeral cannot immediately precede a subtractive pair (e.g., IIX).

ISNA() differs from some spreadsheet programs for certain expressions, such as divisions by zero. TinyExpr++ will return true for this situation, while Excel will return false. This is because Excel distinguishes between #DIV/0! and #N/A errors, while TinyExpr++ treats all invalid numbers (e.g., NaN, INF, -INF) as NaN.

NUMBERVALUE() only ignores the spaces around Text, while Excel ignores them anywhere in it. Polish, Swedish, and French all group with a space, so discarding every space would make "3 000" unreadable as three thousand. (Officially they group with a non-breaking space, U+00A0, or U+202F for French, but a regular space is what gets typed by hand.) Pass the space as GroupSeparator instead:

NUMBERVALUE("3 000")             // NaN
NUMBERVALUE("3 000", ".", " ")   // 3000

(Trailing spaces are still removed, so NUMBERVALUE("3.5 %") is 0.035.)

Separators are a single char, so a UTF-8 non-breaking space (0xC2 0xA0) cannot be used as one. Convert such spaces to a regular one before compiling the formula.

Engineering Functions

Function	Description
BIN2DEC(Text)	Converts a binary string (Text) to a number. Note that Text may be up to 10 digits and is read as two’s complement. BIN2DEC("1111111111") = -1
BITAND(Number1, Number2)	Returns a bitwise ‘AND’ of two (integral) numbers. (Both numbers must be positive and cannot exceed (2^48)-1.)
BITLROTATE8(Number, RotateAmount)	Returns Number left rotated to the most significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 8-bit integer.
BITLROTATE16(Number, RotateAmount)	Returns Number left rotated to the most significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 16-bit integer.
BITLROTATE32(Number, RotateAmount)	Returns Number left rotated to the most significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 32-bit integer.
BITLROTATE64(Number, RotateAmount)	Returns Number left rotated to the most significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 64-bit integer.
BITLROTATE(Number, RotateAmount)	Returns Number left rotated to the most significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as either a 32- or 64-bit integer (depending on what is supported by the compiler).
BITLSHIFT(Number, ShiftAmount)	Returns Number left shifted by the specified number (ShiftAmount) of bits. Numbers cannot exceed (2^48)-1 and ShiftAmount cannot exceed 53 (or 63, if 64-bit is supported).
BITNOT8(Number)	Returns a bitwise ‘NOT’ of a 8-bit integer.
BITNOT16(Number)	Returns a bitwise ‘NOT’ of a 16-bit integer.
BITNOT32(Number)	Returns a bitwise ‘NOT’ of a 32-bit integer.
BITNOT64(Number)	Returns a bitwise ‘NOT’ of a 64-bit integer (if 64-bit integers are supported).
BITNOT(Number)	Returns a bitwise ‘NOT’ of a 32- or 64-bit integer (depending on whether 64-bit is supported).
BITOR(Number1, Number2)	Returns a bitwise ‘OR’ of two (integral) numbers. (Both numbers must be positive and cannot exceed (2^48)-1.)
BITRROTATE8(Number, RotateAmount)	Returns Number right rotated to the least significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 8-bit integer.
BITRROTATE16(Number, RotateAmount)	Returns Number right rotated to the least significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 16-bit integer.
BITRROTATE32(Number, RotateAmount)	Returns Number right rotated to the least significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 32-bit integer.
BITRROTATE64(Number, RotateAmount)	Returns Number right rotated to the least significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as an unsigned 64-bit integer.
BITRROTATE(Number, RotateAmount)	Returns Number right rotated to the least significant bit by the specified number (RotateAmount) of bits. Numbers are rotated as either a 32- or 64-bit integer (depending on what is supported by the compiler).
BITRSHIFT(Number, ShiftAmount)	Returns Number right shifted by the specified number (ShiftAmount) of bits. Numbers cannot exceed (2^48)-1 and ShiftAmount cannot exceed 53 (or 63, if 64-bit is supported).
BITXOR(Number1, Number2)	Returns a bitwise ‘XOR’ of two (integral) numbers. (Both numbers must be positive and cannot exceed (2^48)-1.)
HEX2DEC(Text)	Converts a hexadecimal string (Text) to a number. Note that Text may be up to 10 digits and is read as two’s complement. HEX2DEC("FFFFFFFFFF") = -1
OCT2DEC(Text)	Converts an octal string (Text) to a number. Note that Text may be up to 10 digits and is read as two’s complement. OCT2DEC("7777777777") = -1
SUPPORTS32BIT()	Returns true if 32-bit integers are supported. This will affect the supported range of values for bitwise operations.
SUPPORTS64BIT()	Returns true if 64-bit integers are supported. This will affect the supported range of values for bitwise operations.

Defining TE_FLOAT will disable all bitwise functions and operators, along with BIN2DEC(), HEX2DEC(), and OCT2DEC().

Statistical Functions

Function	Description
AVERAGE(Value1, Value2,…)	Returns the mean of a specified range of values.
SUM(Value1, Value2,…)	Returns the sum of a specified range of values.

Logic Functions

Function	Description
AND(Value1, Value2, …)	Returns true if all conditions are true.
IF(Condition, ValueIfTrue, ValueIfFalse)	If Condition is true (non-zero), then ValueIfTrue is returned; otherwise, ValueIfFalse is returned. Note that multiple IF commands can be nested to create a “case” statement.
IFS(Condition1, Value1, Condition2, Value2, …)	Checks up to twelve conditions, returning the value corresponding to the first met condition. This is shorthand for multiple nested IF commands, providing better readability. Will accept 1–12 condition/value pairs. NaN will be returned if all conditions are false.
NOT(Value)	Returns the logical negation of Value.
OR(Value1, Value2, …)	Returns true if any condition is true.

AND and OR ignore NaN arguments during evaluation, so results may differ from Excel when subexpressions resolve to NaN.

The first argument to any logic function must be valid (i.e., not NaN). If the first argument evaluates to NaN, then NaN will be returned. Any subsequent arguments that evaluate to NaN will be ignored.

Financial Functions

Function	Description
CUMIPMT(Rate, Periods, PresentValue, StartPeriod, EndPeriod, Type)	Returns the cumulative interest paid on a loan between StartPeriod and EndPeriod, inclusive. Type* specifies when payments are due: 0 = end of period, 1 = beginning of period. NaN will be returned if Rate <= 0, Periods <= 0, PresentValue <= 0, if StartPeriod or EndPeriod are out of range, or if Type is not 0 or 1.
CUMPRINC(Rate, Periods, PresentValue, StartPeriod, EndPeriod, Type)	Returns the cumulative principal paid on a loan between StartPeriod and EndPeriod, inclusive. Note that Type specifies when payments are due: 0 = end of period, 1 = beginning of period. NaN will be returned if Rate <= 0, Periods <= 0, PresentValue <= 0, if StartPeriod or EndPeriod are out of range, or if Type is not 0 or 1.
DB(Cost, Salvage, Lifetime, Period, Month)	Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
EFFECT(NominalRate, Periods)	Returns the effective annual interest rate, provided the nominal annual interest rate and the number of compounding periods per year. NaN will be returned if Periods is < 1 or if NominalRate <= 0.
FV(Rate, Periods, Payment, [PresentValue], [Type])	Returns the future value of an investment based on a constant interest rate, a fixed number of periods, and periodic payments. Note that PresentValue and Type are optional and default to 0. Also, Type specifies when payments are due: 0 = end of period, 1 = beginning of period. NaN will be returned if Periods <= 0 or if any required argument is not finite.
IPMT(Rate, Period, Periods, PresentValue, [FutureValue], [Type])	Returns the interest portion of a payment for a specified period of an investment. FutureValue* and Type are optional and default to 0. Type* specifies when payments are due: 0 = end of period, 1 = beginning of period.
NOMINAL(EffectiveRate, Periods)	Returns the nominal annual interest rate, provided the effective rate and the number of compounding periods per year. NaN will be returned if Periods is < 1 or if EffectiveRate <= 0.
NPER(Rate, Payment, PresentValue, [FutureValue], [Type])	Returns the number of periods for an investment or loan based on a constant interest rate, periodic payments, and present and future values. Note that FutureValue and Type are optional and default to 0. Also, Type specifies when payments are due: 0 = end of period, 1 = beginning of period. NaN will be returned if any required argument is not finite or if the calculation is not defined.
PMT(Rate, Periods, PresentValue, [FutureValue], [Type])	Returns the periodic payment for an investment or loan based on a constant interest rate, a fixed number of periods, and a present value. Note that FutureValue and Type are optional and default to 0. Also, Type specifies when payments are due: 0 = end of period, 1 = beginning of period. NaN will be returned if Periods <= 0 or if any required argument is not finite.
PPMT(Rate, Period, Periods, PresentValue, [FutureValue], [Type])	Returns the principal portion of a payment for a specified period of an investment. Note that FutureValue and Type are optional and default to 0. Also, Type specifies when payments are due: 0 = end of period, 1 = beginning of period. NaN will be returned if arguments are invalid or out of range.
PV(Rate, Periods, Payment, [FutureValue], [Type])	Returns the present value of an investment based on a constant interest rate, a fixed number of periods, and periodic payments. Note that FutureValue and Type are optional and default to 0. Also, Type specifies when payments are due: 0 = end of period, 1 = beginning of period. NaN will be returned if Periods <= 0 or if any required argument is not finite.

Compatibility Note

BITNOT will call either BITNOT32 or BITNOT64, depending on whether 64-bit integers are supported. This differs from Excel, which only works with 16-bit integers. To match the behavior of Excel, explicitly call BITNOT16.

Alexander, Michael, and Dick Kusleika. Microsoft Excel 365 Bible. Wiley, 2022.

Winkle, Lewis Van. C Math Evaluation Library: TinyExpr. 2016, https://codeplea.com/tinyexpr.