ToolGood.Algorithm

中文文档

ToolGood.Algorithm is a powerful, lightweight, Excel formula compatible algorithm library aimed at improving developers’ productivity in different business scenes.

Applicable scenarios: Code and algorithm are separated to avoid forced project upgrade

1）Uncertain algorithm at the beginning of the project;

2）Algorithms that are frequently changed during project maintenance;

3）Algorithms in financial data and statistical data (Note: Some formulas use the double type, and it is recommended to use fen as the unit);

Quick start

AlgorithmEngine engine = new AlgorithmEngine(); double a=0.0; if (engine.Parse("1+2")) { var o = engine.Evaluate(); a=o.NumberValue; } var b = engine.TryEvaluate("1=1 && 1<2 || 7-8>1", 0);// Support(支持) && || var c = engine.TryEvaluate("2+3", 0); var q = engine.TryEvaluate("-7 < -2 ?1 : 2", 0); var e = engine.TryEvaluate("count(array(1, 2, 3, 4))", 0);//{} represents array, return: 4 {}代表数组, 返回:4 var s = engine.TryEvaluate("'aa'&'bb'", ""); //String connection, return: AABB 字符串连接, 返回:aabb var r = engine.TryEvaluate("(1=1)*9+2", 0); //Return: 11 返回:11 var d = engine.TryEvaluate("'2016-1-1'+1", DateTime.MinValue); //Return date: 2016-1-2 返回日期:2016-1-2 var j = engine.TryEvaluate("json('{\"Name\":\"William Shakespeare\", \"Age\":51, \"Birthday\":\"04/26/1564 00:00:00\"}').Age", null);//Return 51 返回51 var k = engine.TryEvaluate("json('{\"Name\":\"William Shakespeare \", \"Age\":51, \"Birthday\":\"04/26/1564 00:00:00\"}')['Name'].Trim()", null);//Return to "William Shakespeare" 返回"William Shakespeare" (不带空格) var l = engine.TryEvaluate("json('{\"Name1\":\"William Shakespeare \", \"Age\":51, \"Birthday\":\"04/26/1564 00:00:00\"}')['Name'& 1].Trim().substring(2, 3)", null); ;//Return "ill" 返回"ill" var n = engine.TryEvaluate("{Name:\"William Shakespeare\", Age:51, Birthday:\"04/26/1564 00:00:00\"}.Age", null);//Return 51 返回51 var m = engine.TryEvaluate("[1,2,3,4,5,6].has(13)", true);//Return false 返回false

Constantspi, e, true, falseare supported.

The value is converted to bool, non-zero is true and zero is false. String to bool, 0 FALSE NO 无 没有 不是 否 is false, 1 TRUE YES 是 有 is true. Case insensitive.

Bool to value, false is0, true is1. Bool to string, false toFALSE, true toTRUE.

The default index isexcel index. If you want to use c# index, please setUseExcelIndextofalse.

Chinese symbols are automatically converted into English symbols: brackets, commas, quotation marks, double quotation marks，addition,subtraction, multiplication, division , equal sign.

Note: Use & for string concatenation.

Note: find is an Excel formula , find (the string to be searched, the string to be searched [, start position])

Note: Starting from version 6.0, the [X,X] array code replaces {X,X}.

Note: Starting from version 6.2, converting datetime to numbers, numbers to datetime, and performing arithmetic operations between datetime and numbers are prohibited. Use functions instead for related operations.

Custom parameters

//Define cylinder information 定义圆柱信息 public class Cylinder : AlgorithmEngine { private int _radius; private int _height; public Cylinder(int radius, int height) { _radius = radius; _height = height; } protected override Operand GetParameter(string parameter) { if (parameter == "半径") { return Operand.Create(_radius); } if (parameter == "直径") { return Operand.Create(_radius * 2); } if (parameter == "高") { return Operand.Create(_height); } return base.GetParameter(parameter); } } //Call method 调用方法 Cylinder c = new Cylinder(3, 10); c.TryEvaluate("半径*半径*pi()", 0.0); //Round bottom area 圆底面积 c.TryEvaluate("直径*pi()", 0.0); //The length of the circle 圆的长 c.TryEvaluate("半径*半径*pi()*高", 0.0); //Volume of circle 圆的体积 c.EvaluateFormula("'圆'-半径-高", '-'); // Return: 圆-3-10 c.GetSimplifiedFormula("半径*if(半径>2, 1+4, 3)"); // Return: 3 * 5

Parameter Definition: parameter name

Note: Versions below 5.9 do not support [parameter name], 【parameter name】, #parameter name#, or @parameterName.

Note: AlgorithmEngineEx allows the use of AddParameter and AddParameterFromJson to add parameters.

Note: You can override the GetParameter and ExecuteDiyFunction methods to customize parameters and define custom methods.

Note: use AlgorithmEngineHelper.GetDiyNames get parameter name and custom function name.

Custom parameters

var helper = new ToolGood.Algorithm.AlgorithmEngineHelper(); helper.IsKeywords("false"); // return true helper.IsKeywords("true"); // return true helper.IsKeywords("mysql"); // return false DiyNameInfo p5 = helper.GetDiyNames("ddd(d1, 22)"); Assert.AreEqual("ddd", p5.Functions[0]); Assert.AreEqual("d1", p5.Parameters[0]);

Support Unit

Standard units can be set: DistanceUnit (default:m), AreaUnit(default:m2), VolumeUnit(default:m3), MassUnit(default:kg).

Note: When calculating the formula, first convert the quantity with units into standard units, and then perform numerical calculations.

AlgorithmEngine engine = new AlgorithmEngine(); bool a = engine.TryEvaluate("1=1m", false); // return true bool b = engine.TryEvaluate("1=1m2", false); // return true bool c = engine.TryEvaluate("1=1m3", false); // return true bool d = engine.TryEvaluate("1=1kg", false); // return true // Unit Conversion 单位转化 var num = AlgorithmEngineHelper.UnitConversion(1M,"米","千米"); // Example of not throwing mistakes 不抛错例子 bool error = engine.TryEvaluate("1m=1m2", false); // return true

Excel Formula

Functions: logical functions, mathematics and trigonometric functions, text functions, statistical functions, date and time functions

Note: Function names are not case sensitive. Parameters with square brackets can be omitted. The return value of the example is approximate.

Note 2: The function name with ★ indicates that the first parameter can be prefixed, such as (-1).ISTEXT()

Note 3: The function name with ▲ means that it is affected by Excel Index,

Logical function

function name	description	Example
IF	if(condition, trueValue[, falseValue]) Execute the judgment and return different results according to the true or false of the logical calculation.	if(1=1, 1, 2) >>1
IFS	ifs(condition1, value1, condition2, value2, ...) Check whether one or more conditions are met and return the value corresponding to the first TRUE condition.	IFS(1=1, 'a', 1=2, 'b') >>a
SWITCH	switch(expression, value1, result1, value2, result2, ...) Evaluate an expression against a list of values and return the corresponding result.	SWITCH(1, 1, 'one', 2, 'two') >>one
ifError	ifError(condition, trueValue[, falseValue]) If the formula calculates incorrectly, the value you specify is returned; otherwise, the formula result is returned.	ifError(1/0, 1, 2) >>1
isError ★	isError(value) To determine whether there is an error, return TRUE or FALSE isError(value, replace) If there is an error, return the replacement value, otherwise return the original value	isError(1) >>false
isNull ★	isNull(value) Determine whether it is null or return TRUE or FALSE isNull(value, replace) If null, return the replacement value, otherwise return the original value	isNull(null) >>true
isNullOrError ★	isNullOrError(value) To determine whether it is null or error, return TRUE or FALSE isNullOrError(value, replace) If it is null or wrong, return the replacement value, otherwise return the original value	isNullOrError(null) >>true
isNumber ★	isNumber(value) Determine whether it is a numeric value, and return TRUE or FALSE	isNumber(1) >>true
isText ★	isText(value) Determine whether it is a text and return TRUE or FALSE	isText('1') >>true
IsNonText ★	IsNonText(value) Determine whether it is not a text and return TRUE or FALSE	IsNonText('1') >>false
IsLogical ★	IsLogical(value) Determine whether it is a logical value and return TRUE or FALSE	IsLogical('1') >>false
IsEven ★	IsEven(value) If the value is even, return TRUE or FALSE	IsEven('1') >>false
IsOdd ★	IsOdd(value) If the value is odd, return TRUE or FALSE	IsOdd('1') >>true
AND	and(logic1, ...) If all parameters are true, return true. If there is an error, report it first	and(1, 2=2) >>true
OR	or(logic1, ...) If any parameter is TRUE, return TRUE. If there is an error, report it first	or(1, 2=3) >>true
XOR	xor(logic1, ...) Return the exclusive OR of all parameters, odd number of TRUE returns TRUE	xor(true(), false()) >>true
NOT	not(logic) Negate the logical value of a parameter	NOT(true()) >>false
TRUE	true() Return TRUE	true() >>true
FALSE	false() Return FALSE	false() >>false

Mathematics and Trigonometric Functions

classification	function name	description	Example
basic mathematics	E	e() Return e value	E() >>
	PI	pi() Return PI value	pi() >>3.141592654
	abs	abs(number) Returns the absolute value of a numerical value	abs(-1) >>1
	QUOTIENT	quotient(number, dividend) Returns the integer portion of the quotient, which can be used to round off the fractional portion of the quotient.	QUOTIENT(7, 3) >>2
	mod	mod(number, dividend) Returns the remainder of the division of two numbers	MOD(7, 3) >>1
	SIGN	sign(number) Returns the sign of a numerical value. Returns 1 when the value is positive, 0 when it is zero, and -1 when it is negative.	SIGN(-9) >>-1
	SQRT	sqrt(number) Returns the positive square root	SQRT(9) >>3
	TRUNC	trunc(number) Truncate the value	TRUNC(9.222) >>9
	int ★	int(number) Rounds the value down to the nearest integer.	int(9.222) >>9
	gcd	gcd(number, ...) Returns the maximum common divisor	GCD(3, 5, 7) >>1
	LCM	lcm(number, ...) Returns the least common multiple of an integer parameter	LCM(3, 5, 7) >>105
	combin	combin(tatal, number) Calculate the number of combinations to extract several objects from a given number of object sets	combin(10, 2) >>45
	PERMUT	permut(tatal, number) Returns the ranking of several objects selected from a given number of object collections	PERMUT(10, 2) >>990
	FIXED	fixed(number[, decimalDigit[, hasComma]]) Format numeric values to text with fixed decimal places	FIXED(4567.89, 1) >>4, 567.9
Triangulation function	degrees	degrees(radian) Convert radians to degrees	degrees(pi()) >>180
	RADIANS	radians(degree) Convert degrees to radians	RADIANS(180) >>3.141592654
	cos	cos(radian) Returns the cosine of a numerical value	cos(1) >>0.540302305868
	cosh	cosh(radian) Returns the hyperbolic cosine of a value	cosh(1) >>1.54308063481
	SIN	sin(radian) Returns the sine of a given angle	sin(1) >>0.84147098480
	SINH	sinh(radian) Returns the hyperbolic sine of a numeric value	sinh(1) >>1.1752011936
	TAN	tan(radian) Returns the tangent of a numerical value	tan(1) >>1.55740772465
	TANH	tanh(radian) Returns the hyperbolic tangent of a value	tanh(1) >>0.761594155955
	acos	acos(number) Returns the inverse cosine of a numeric value	acos(0.5) >>1.04719755119
	acosh	acosh(number) Returns the inverse hyperbolic cosine of a value	acosh(1.5) >>0.962423650119
	asin	asin(number) Returns the arcsine of a value	asin(0.5) >>0.523598775598
	asinh	asinh(number) Returns the inverse hyperbolic sine of a value.	asinh(1.5) >>1.1947632172
	atan	atan(number) Returns the inverse tangent of a value	atan(1) >>0.785398163397
	atanh	atanh(number) Returns the inverse hyperbolic tangent of the parameter	atanh(1) >>0.549306144334
	atan2	atan2(number, number) Return anti-tangent from X and Y coordinates	atan2(1, 2) >>1.10714871779
COT	cot(radian) Returns the cotangent of a numeric value	cot(1) >>0.64209261593
COTH	coth(radian) Returns the hyperbolic cotangent of a value	coth(1) >>1.31303528549
CSC	csc(radian) Returns the cosecant of a numeric value	csc(1) >>1.18839510578
CSCH	csch(radian) Returns the hyperbolic cosecant of a value	csch(1) >>0.85091812823
SEC	sec(radian) Returns the secant of a numeric value	sec(1) >>1.85081571768
SECH	sech(radian) Returns the hyperbolic secant of a value	sech(1) >>0.64805427366
ACOT	acot(number) Returns the inverse cotangent of a numeric value	acot(1) >>0.78539816339
ACOTH	acoth(number) Returns the inverse hyperbolic cotangent of a value	acoth(2) >>0.54930614433
Round off	ROUND	round(number, decimalDigit) Returns the value of a value rounded by the specified number of digits.	ROUND(4.333, 2) >>4.33
	roundDown	roundDown(number, decimalDigit) Close to zero, rounding the value down (the direction in which the absolute value decreases).	roundDown(4.333, 2) >>4.33
	roundUp	roundUp(number, decimalDigit) Away from zero, round the value upward (the direction in which the absolute value increases).	roundUp(4.333, 2) >>4.34
	CEILING	ceiling(number, roundingBase) Rounding up (in the direction in which the absolute value increases) is a multiple of the nearest rounding base.	CEILING(4.333, 0.1) >>4.4
	floor	floor(number, roundingBase) Round down to a multiple of the nearest Significance.	FLOOR(4.333, 0.1) >>4.3
	even	even(number) Returns the nearest even number rounded in the direction of increasing the absolute value.	EVEN(3) >>4
	ODD	odd(number) Rounds the value up to the nearest odd integer	ODD(3.1) >>5
	MROUND	mround(number, roundingBase) Returns a value rounded to the desired multiple	MROUND(13, 5) >>15
Random number	RAND	rand() Returns a random number between 0 and 1	RAND() >>0.2
	randBetween	randBetween(min, max) Returns a random integer greater than or equal to the specified minimum value and less than the specified maximum value.	randBetween(2, 44) >>9
Power / logarithm / factorial	fact	fact(number) Returns the factorial of a number, where the factorial of a number is equal to 1'2'3 *. * the number.	FACT(3) >>6
	factdouble	factDouble(number) Return the double factorial of the value	factDouble(10) >>3840
	POWER	power(number, power) The power result of the return number	POWER(10, 2) >>100
	exp	exp(power) Returns the power of the specified number of e	exp(2) >>7.389056099
	ln	ln(number) Returns the natural logarithm of the value	LN(4) >>1.386294361
	log	log(number[, baseNumber]) Returns the common logarithm of the value, such as omitting the base. The default is 10.	LOG(100, 10) >>2
	LOG10	log10(number) Returns the 10 logarithm of the value	LOG10(100) >>2
	MULTINOMIAL	multinomial(number, ...) Returns the ratio of the factorial of the sum of parameters to the factorial product of each parameter	MULTINOMIAL(1, 2, 3) >>60
	PRODUCT	product(number, ...) Multiplies all values given as parameters and returns the product value.	PRODUCT(1, 2, 3, 4) >>24
	SqrtPi	SqrtPi(number) Returns the square root of the product of a number and PI	SqrtPi(3) >>3.069980124
	ERF	erf(number) Returns the error function value	ERF(1) >>0.842701
ERFC	erfc(number) Returns the complementary error function value	ERFC(1) >>0.157299
BESSELI	besselI(x, n) Returns the modified Bessel function In(x)	BESSELI(1.5, 1) >>0.981666
BESSELJ	besselJ(x, n) Returns the Bessel function Jn(x)	BESSELJ(1.5, 1) >>0.557937
BESSELK	besselK(x, n) Returns the modified Bessel function Kn(x)	BESSELK(1.5, 1) >>0.277388
BESSELY	besselY(x, n) Returns the Bessel function Yn(x)	BESSELY(1.5, 1) >>-0.412309
DELTA	delta(number1[, number2]) Tests whether two numbers are equal, returns 1 if equal, otherwise 0	DELTA(5, 5) >>1
GESTEP	gestep(number[, step]) Tests whether a number is greater than or equal to a threshold, returns 1 if yes, otherwise 0	GESTEP(5, 4) >>1
SUMSQ	sumQq(number, ...) Returns the sum of squares of parameters	SUMSQ(1, 2) >>5
SUMPRODUCT	sumproduct(array1, array2, ...) Returns the sum of products of corresponding array elements	SUMPRODUCT(array(1, 2, 3), array(4, 5, 6)) >>32
SUMX2MY2	sumx2my2(arrayX, arrayY) Returns the sum of the difference of squares of corresponding values in two arrays	SUMX2MY2(array(1, 2, 3), array(4, 5, 6)) >>-63
SUMX2PY2	sumx2py2(arrayX, arrayY) Returns the sum of the sum of squares of corresponding values in two arrays	SUMX2PY2(array(1, 2, 3), array(4, 5, 6)) >>91
SUMXMY2	sumxmy2(arrayX, arrayY) Returns the sum of squares of differences of corresponding values in two arrays	SUMXMY2(array(1, 2, 3), array(4, 5, 6)) >>27
SERIESSUM	seriessum(X, N, M, coefficients) Returns the sum of a power series	SERIESSUM(2, 0, 1, array(1, 1, 1, 1)) >>15
ARABIC ★	arabic(romanText) Converts a Roman numeral to Arabic numeral	ARABIC('MMXXIII') >>2023
ROMAN ★	roman(number[, form]) Converts an Arabic numeral to Roman numeral	ROMAN(2023) >>MMXXIII
Transformation	DEC2BIN ★	DEC2BIN(number[, digit]) Decimal to binary	DEC2BIN(100) >>
	DEC2OCT ★	DEC2OCT(number[, digit]) Decimal to octal	DEC2OCT(100) >>
	DEC2HEX ★	DEC2HEX(number[, digit]) Convert from decimal to hexadecimal	DEC2HEX(100) >>
	HEX2BIN ★	HEX2BIN(number[, digit]) Hexadecimal to binary	HEX2BIN(100) >>
	HEX2OCT ★	HEX2OCT(number[, digit]) Convert hexadecimal to octal	HEX2OCT(100) >>
	HEX2DEC ★	HEX2DEC(number) Hexadecimal to decimal	HEX2DEC(100) >>
	OCT2BIN ★	OCT2BIN(number[, digit]) Octal to binary	OCT2BIN(100) >>
	OCT2DEC ★	OCT2DEC(number) Octal to decimal	OCT2DEC(100) >>
	OCT2HEX ★	OCT2HEX(number[, digit]) Octal to hexadecimal	OCT2HEX(100) >>
	BIN2OCT ★	BIN2OCT(number[, digit]) Binary to octal	BIN2OCT(100) >>
	BIN2DEC ★	BIN2DEC(number) Binary to decimal	BIN2DEC(100) >>
	BIN2HEX ★	BIN2HEX(number[, digit]) Binary to hexadecimal	BIN2HEX(100) >>

Text function

function name	description	Example
ASC ★	asc(text) Change the full-width letters in a string to half-width characters	asc('ａｂｃＡＢＣ１２３') >>abcABC123
JIS ★ WIDECHAR ★	jis(text) Change half-width English characters in a string to full-width characters	jis('abcABC123') >>ａｂｃＡＢＣ１２３
CHAR ★	CHAR(number) Returns the character specified by the code value	char(49) >>1
CLEAN ★	clean(text) Delete all unprintable characters in the text	clean('\r112\t') >>112
CODE ★	code(text) Returns the numeric code of the first character in the text string	CODE("1") >>49
CONCATENATE ★	concatenate(text1, ...) Merge several text items into a single text item	CONCATENATE('tt', '11') >>tt11
EXACT ★	exact(text1, text2) Check whether the two text values are exactly the same	EXACT("11", "22") >>false
FIND ★ ▲	find(text, findText[, startIndex]) Find another text value within one text value (case sensitive)	FIND("11", "12221122") >>5
LEFT ★	left(text[, count]) Returns the leftmost character of the text value	LEFT('123222', 3) >>123
LEN ★	len(text) Returns the number of characters in a text string	LEN('123222') >>6
MID ★ ▲	mid(text, startIndex, count) Returns a specific number of characters from a specified position in a text string	MID('ABCDEF', 2, 3) >>BCD
PROPER ★	proper(text) Set the first letter of each word in the text value to uppercase	PROPER('abc abc') >>Abc Abc
REPLACE ★ ▲	replace(srcText, startIndex, count, newText) replace(srcText, repalceText, newText) Replace characters in text	REPLACE("abccd", 2, 3, "2") >>a2d REPLACE("abccd", "bc", "2") >>a2cd
REPT ★	rept(text, times) Repeat the text a given number of times	REPT("q", 3) >>qqq
RIGHT ★	right(text[, count]) Returns the rightmost character of the text value	RIGHT("123q", 3) >>23q
RMB ★	RMB(number) Convert numeric values to chinese uppercase numeric text	RMB(12.3) >>壹拾贰元叁角
SEARCH ★ ▲	search(findText, text[, startIndex]) Find another text value in one text value (case-insensitive)	SEARCH("aa", "abbAaddd") >>4
SUBSTITUTE ★	substitute(text, srcText, newText[, index]) Replace old text with new text in a text string	SUBSTITUTE("ababcc", "ab", "12") >>1212cc
T ★	t(number) Convert parameters to text	T('123') >>123
TEXT ★	text(number, format) Format numeric values and convert them to text	TEXT(123, "0.00") >>123.00
TRIM ★	trim(text) Delete spaces in text	TRIM(" 123 123 ") >>123 123
LOWER ★ TOLOWER ★	lower(text) tolower(text) Convert text to lowercase	LOWER('ABC') >>abc
UPPER ★ TOUPPER ★	upper(text) toupper(text) Convert text to uppercase	UPPER("abc") >>ABC
VALUE ★	value(text) Convert text parameters to numeric values	VALUE("123") >>123
UNICHAR ★	unichar(number) Returns the character specified by the Unicode value	unichar(65) >>A
UNICODE ★	unicode(text) Returns the Unicode numeric code of the first character in the text string	UNICODE("A") >>65

Date and time functions

function name	description	Example
NOW	now() Returns the current date and time	NOW() >>2017-01-07 11:00:00
TODAY	today() Return to today's date	TODAY() >>2017-01-07
DateValue ★	DateValue(text) Convert a text format to a date	DateValue("2017-01-02") >>2017-01-02
TimeValue ★	TimeValue(text) Convert text formatted time to date	TimeValue("12:12:12") >>12:12:12
DATE ★	date(year, month, day[, hour[, minute[, second]]]) Returns a specific date	DATE(2016, 1, 1) >>2016-01-01
TIME ★	time(hour, minute, second) Returns a specific time	TIME(12, 13, 14) >>12:13:14
YEAR ★	year(date) Returns year	YEAR(NOW()) >>2017
MONTH ★	month(date) Returns month	MONTH(NOW()) >>1
DAY ★	day(date) Returns day	DAY(NOW()) >>7
HOUR ★	hour(date) Returns hour	HOUR(NOW()) >>11
MINUTE ★	minute(date) Returns minute	MINUTE(NOW()) >>12
SECOND ★	second(date) Returns second	SECOND(NOW()) >>34
WEEKDAY ★	WEEKDAY(date) Returns weekday	WEEKDAY(date(2017, 1, 7)) >>7
dateDIF	dateDif(startDate, endDate, type:Y/M/D/YD/MD/YM) Returns the number of days between two dates	dateDIF("1975-1-30", "2017-1-7", "Y") >>41
DAYS360	days360(startDate, endDate[, type:0/1]) Calculate the number of days in a two-day period on the basis of 360 days a year	DAYS360('1975-1-30', '2017-1-7') >>15097
EDATE	eDate(startDate, month) Returns the serial number used to represent the number of months before or after the start date	EDATE("2012-1-31", 32) >>2014-09-30
EOMONTH	eoMonth(startDate, month) Returns the serial number of the last day of the month before or after the specified number of months	EOMONTH("2012-2-1", 32) >>2014-10-31
netWorkdays	netWorkdays(startDate, endDate[, holidays]) Returns the total number of working days between two dates	netWorkdays("2012-1-1", "2013-1-1") >>262
workDay	workday(startDate, days[, holidays]) Returns the serial number of the date before or after the specified number of working days	workDay("2012-1-2", 145) >>2012-07-23
WEEKNUM	weekNum(date[, type：1/2]) Returns week number	WEEKNUM("2016-1-3") >>2
DAYS	days(endDate, startDate) Returns the number of days between two dates	DAYS("2017-1-7", "2017-1-1") >>6
YEARFRAC	yearfrac(startDate, endDate[, basis:0-4]) Returns the fraction of the year represented by the number of whole days between two dates	YEARFRAC("2012-1-1", "2012-7-1") >>0.5

Extension function

function name	description	Example
AddYears ★	AddYears(date, number) Add Years
AddMonths ★	AddMonths(date, number) Add Months
AddDays ★	AddDays(date, number) Add Days
AddHours ★	AddHours(date, number) Add Hours
AddMinutes ★	AddMinutes(date, number) Add Minutes
AddSeconds ★	AddSeconds(date, number) Add Seconds
DateValue ★	DateValue(value, number) Conversion time DateValue(text/number, 0) Parse, automatically match to a date similar to the current date DateValue(text, 1) Conversion date, text format DateValue(number, 2) Conversion date, Excel value DateValue(number, 3) Convert to date, timestamp (milliseconds) DateValue(number, 4) Convert to date, timestamp (seconds)
Timestamp ★	Timestamp(date[, type:0/1]) Switch to timestamp. Default is millisecond. Timestamp(date, 0) Convert to timestamp (milliseconds) Timestamp(date, 1) Convert to timestamp (seconds)

Note: The UseLocalTime attribute affects the conversion of DateValue/Timestamp. Set true to directly convert to local time.

Financial function

function name	description	Example
PMT	pmt(rate, nper, pv[, fv[, type]]) Calculate the periodic payment for a loan with fixed interest rate	PMT(0.08/12, 10, 10000) >>-1037.03
PPMT	ppmt(rate, per, nper, pv[, fv[, type]]) Calculate the principal payment for a loan in a given period	PPMT(0.08/12, 1, 10, 10000) >>-970.37
IPMT	ipmt(rate, per, nper, pv[, fv[, type]]) Calculate the interest payment for a loan in a given period	IPMT(0.08/12, 1, 10, 10000) >>-66.67
PV	pv(rate, nper, pmt[, fv[, type]]) Calculate the present value of an investment	PV(0.08/12, 10, -1000) >>9637.09
FV	fv(rate, nper, pmt[, pv[, type]]) Calculate the future value of an investment	FV(0.08/12, 10, -1000) >>10413.84
NPER	nper(rate, pmt, pv[, fv[, type]]) Calculate the number of periods for an investment	NPER(0.08/12, -1000, 10000) >>10.7
RATE	rate(nper, pmt, pv[, fv[, type[, guess]]]) Calculate the interest rate per period for an investment	RATE(4*12, -200, 8000) >>0.0077
NPV	npv(rate, value1, ...) Calculate the net present value of a series of periodic cash flows	NPV(0.1, -10000, 3000, 4200, 6800) >>1188.44
XNPV	xnpv(rate, values, dates) Calculate the net present value of a series of non-periodic cash flows	XNPV(0.09, array(-10000, 2750, 4250, 3250, 2750), array('2008-1-1', '2008-3-1', '2008-10-30', '2009-2-15', '2009-4-1')) >>2086.65
IRR	irr(values[, guess]) Calculate the internal rate of return for a series of cash flows	IRR(array(-70000, 12000, 15000, 18000, 21000, 26000)) >>0.0866
MIRR	mirr(values, finance_rate, reinvest_rate) Calculate the modified internal rate of return	MIRR(array(-70000, 12000, 15000, 18000, 21000, 26000), 0.1, 0.12) >>0.0797
XIRR	xirr(values, dates[, guess]) Calculate the internal rate of return for a series of non-periodic cash flows	XIRR(array(-10000, 2750, 4250, 3250, 2750), array('2008-1-1', '2008-3-1', '2008-10-30', '2009-2-15', '2009-4-1')) >>0.3734
SLN	sln(cost, salvage, life) Calculate the straight-line depreciation of an asset	SLN(30000, 7500, 10) >>2250
DB	db(cost, salvage, life, period[, month]) Calculate the declining balance depreciation of an asset	DB(1000000, 100000, 6, 1) >>319000
DDB	ddb(cost, salvage, life, period[, factor]) Calculate the double declining balance depreciation of an asset	DDB(2400, 300, 10, 2) >>384
SYD	syd(cost, salvage, life, period) Calculate the sum-of-years' digits depreciation of an asset	SYD(30000, 7500, 10, 1) >>4090.91

Statistical function

function name	description	Example
MAX	max(number, ...) Returns the maximum value in the parameter list	max(1, 2, 3, 4, 2, 2, 1, 4) >>4
MEDIAN	median(number, ...) Returns the median of a given value	MEDIAN(1, 2, 3, 4, 2, 2, 1, 4) >>2
MIN	min(number, ...) Returns the minimum value in the parameter list	MIN(1, 2, 3, 4, 2, 2, 1, 4) >>1
QUARTILE	quartile(number, quartile：0-4) Returns the quartile of the dataset	QUARTILE({1, 2, 3, 4, 2, 2, 1, 4}, 0) >>1
MODE	mode(number, ...) Returns the number that occurs most frequently in the array	MODE(1, 2, 3, 4, 2, 2, 1, 4) >>2
LARGE ▲	large(array, K) Returns the k largest value of the data set	LARGE({1, 2, 3, 4, 2, 2, 1, 4}, 3) >>3
SMALL ▲	small(number, K) Returns the k-th minimum of the data set	SMALL({1, 2, 3, 4, 2, 2, 1, 4}, 3) >>2
PERCENTILE PERCENTILE.INC	percentile(number, K) Returns the k percentile in the area	PERCENTILE({1, 2, 3, 4, 2, 2, 1, 4}, 0.4) >>2
PERCENTRANK PERCENTRANK.INC	percentRank(number, K) Returns the percentage ranking of the values in the data set	PERCENTRANK({1, 2, 3, 4, 2, 2, 1, 4}, 3) >>0.714
AVERAGE	average(number, ...) Returns the average value of the parameter	AVERAGE(1, 2, 3, 4, 2, 2, 1, 4) >>2.375
averageIf	averageIf({number1, ...}, condition[, {number1, ...}]) Returns the average value of the parameter	averageIf({1, 2, 3, 4, 2, 2, 1, 4}, '>1') >>2.833333333
GEOMEAN	geoMean(number, ...) Returns the geometric mean of a positive array or region	GEOMEAN(1, 2, 3, 4) >>2.213363839
HARMEAN	harMean(number, ...) Returns the harmonic average of the data set	HARMEAN(1, 2, 3, 4) >>1.92
COUNT	count(number, ...) Calculate the number of values in the parameter list	COUNT(1, 2, 3, 4, 2, 2, 1, 4) >>8
countIf	countIf({number1, ...}, condition[, {number1, ...}]) Calculate the number of values in the parameter list	countIf({1, 2, 3, 4, 2, 2, 1, 4}, '>1') >>6
SUM	sum(number, ...) Returns the sum of all values.	SUM(1, 2, 3, 4) >>10
sumIf	sumIf({number, ...}, condition[, {number1, ...}]) Returns the sum of all values.	sumIf({1, 2, 3, 4, 2, 2, 1, 4}, '>1') >>17
AVEDEV	aveDev(number, ...) Returns the average of the absolute deviation of a data point from its average	AVEDEV(1, 2, 3, 4, 2, 2, 1, 4) >>0.96875
STDEV STDEV.S	stDev(number, ...) Estimation of standard deviation based on samples	STDEV(1, 2, 3, 4, 2, 2, 1, 4) >>1.1877349391654208
STDEVP STDEV.P	stDevp(number, ...) Calculate the standard deviation based on the whole sample population	STDEVP(1, 2, 3, 4, 2, 2, 1, 4) >>1.1110243021644486
DEVSQ	devSq(number, ...) Returns the sum of squares of deviations	DEVSQ(1, 2, 3, 4, 2, 2, 1, 4) >>9.875
VAR VAR.S	var(number, ...) Estimation of variance based on samples	VAR(1, 2, 3, 4, 2, 2, 1, 4) >>1.4107142857142858
VARP VAR.P	varp(number, ...) Calculate the variance based on the whole sample population	VARP(1, 2, 3, 4, 2, 2, 1, 4) >>1.234375
normDist NORM.DIST	normDist(number, arithmeticMean, StDev, returnType：0/1) Return to normal cumulative distribution	normDist(3, 8, 4, 1) >>0.105649774
normInv NORM.INV	normInv(distributionProbability, arithmeticMean, StDev) Returns the anti-normal cumulative distribution	normInv(0.8, 8, 3) >>10.5248637
NormSDist	NORM.S.DIST(number) Returns the standard normal cumulative distribution function, with an average of 0 and a standard deviation of 1.	NORMSDist(1) >>0.841344746
normSInv NORM.S.INV	normSInv(number) Return anti-standard normal cumulative distribution	normSInv(0.3) >>-0.524400513
BetaDist BETA.DIST	BetaDist(number, α, β) Returns the Beta cumulative distribution function	BetaDist(0.5, 11, 22) >>0.97494877
BetaInv BETA.INV	BetaInv(number, α, β) Returns the inverse function of the cumulative distribution function of the specified Beta distribution	BetaInv(0.5, 23, 45) >>0.336640759
binomDist BINOM.DIST	binomDist(successCount, testCount, successProbability, returnType：0/1) Returns the probability of unary binomial distribution	binomDist(12, 45, 0.5, 0) >>0.000817409
exponDist EXPON.DIST	exponDist(number, value, returnType：0/1) Return exponential distribution	exponDist(3, 1, 0) >>0.049787068
FDist F.DIST	fDist(numberX, molecularFreedom, denominatorFreedom) Return F probability distribution	FDist(0.4, 2, 3) >>0.701465776
FInv F.INV	fInv(distributionProbability, molecularFreedom, denominatorFreedom) Returns the inverse function of F probability distribution	FInv(0.7, 2, 3) >>0.402651432
FISHER	fisher(number) Returns the Fisher transformation of point x. The transformation produces a normal distribution rather than a skewed function.	FISHER(0.68) >>0.8291140383
fisherInv	fisherInv(number) Returns the inverse value of the Fisher transform.	fisherInv(0.6) >>0.537049567
gammaDist GAMMA.DIST	gammaDist(number, α, β, returnType：0/1) Return gamma distribution	gammaDist(0.5, 3, 4, 0) >>0.001723627
gammaInv GAMMA.INV	gammaInv(distributionProbability, α, β) Returns the inverse function of the gamma cumulative distribution function	gammaInv(0.2, 3, 4) >>6.140176811
GAMMALN GAMMALN.PRECISE	gammaLn(number) Returns the natural logarithm of γ	GAMMALN(4) >>1.791759469
hypgeomDist HYPGEOM.DIST	hypgeomDist(successCount, testCount, successCountAll, testCountAll) Returns the hypergeometric distribution	hypgeomDist(23, 45, 45, 100) >>0.08715016
logInv LOGNORM.INV	logInv(distributionProbability, average, StDev) Returns the inverse function of the logarithmic cumulative distribution function of x	logInv(0.1, 45, 33) >>15.01122624
LognormDist LOGNORM.DIST	lognormDist(number, average, StDev) Returns the inverse normal distribution	lognormDist(15, 23, 45) >>0.326019201
negbinomDist NEGBINOM.DIST	negbinomDist(failureCount, successCount, successProbability) Returns negative binomial distribution	negbinomDist(23, 45, 0.7) >>0.053463314
POISSON POISSON.DIST	poisson(number, average, returnType：0/1) Returns the Poisson distribution	POISSON(23, 23, 0) >>0.082884384
TDist T.DIST	tDist(number, freedom, returnType：1/2) Returns the t distribution of students	TDist(1.2, 24, 1) >>0.120925677
TInv T.INV	TInv(distributionProbability, freedom) Returns the inverse distribution of students't-distribution	TInv(0.12, 23) >>1.614756561
WEIBULL	weibull(number, α, β, returnType：0/1) Returns the Weibull distribution	WEIBULL(1, 2, 3, 1) >>0.105160683
COVAR COVARIANCE.P	COVAR(array1, array2) Returns population covariance	COVAR(array(3,7,6,11),array(5,15,13,9)) >>3.375
COVARIANCE.S	COVARIANCE.S(array1, array2) Returns sample covariance	COVARIANCE.S(array(3,7,6,11),array(5,15,13,9)) >>4.5
RANK	rank(number, array[, order:0/1]) Returns the rank of a number in a list	RANK(3, array(1, 2, 3, 4, 5)) >>3
FORECAST	forecast(X, Yarray, Xarray) Returns a predicted value based on linear trend	FORECAST(30, array(6, 7, 9, 15, 21), array(20, 28, 31, 38, 40)) >>10.61
INTERCEPT	intercept(Yarray, Xarray) Returns the intercept of the linear regression line	INTERCEPT(array(2, 3, 9, 1, 8), array(6, 5, 11, 7, 5)) >>0.05
SLOPE	slope(Yarray, Xarray) Returns the slope of the linear regression line	SLOPE(array(2, 3, 9, 1, 8), array(6, 5, 11, 7, 5)) >>0.67
CORREL	correl(array1, array2) Returns the correlation coefficient between two arrays	CORREL(array(3, 2, 4, 5, 6), array(9, 7, 12, 15, 17)) >>0.997
PEARSON	pearson(array1, array2) Returns the Pearson product moment correlation coefficient	PEARSON(array(3, 2, 4, 5, 6), array(9, 7, 12, 15, 17)) >>0.997

Find references

function name	description	Example
LookFloor	LookFloor(value, array) Find the lower bound value. The array will be sorted; if higher than the maximum, take the maximum value; if lower than the minimum, take the minimum value.
LookCeiling	LookCeiling(value, array) Find the upper bound value. The array will be sorted; if higher than the maximum, take the maximum value; if lower than the minimum, take the minimum value.

Add function similar to C# method

function name	description	Example
UrlEncode ★	UrlEncode(text) Encode the URL string.
UrlDecode ★	UrlEncode(text) Converts an URL-encoded string to a decoded string.
HtmlEncode ★	HtmlEncode(text) Converts a string to a HTML-encoded string.
HtmlDecode ★	HtmlDecode(text) Transdecode the HTML-encoded string.
Base64ToText ★	Base64ToText(text) Converts Base64 to a string.
Base64UrlToText ★	Base64UrlToText(text) Converts a Base64 of type Url to a string.
TextToBase64 ★	TextToBase64(text) Converts a string to an Base64 string.
TextToBase64Url ★	TextToBase64Url(text) Converts a string to an Base64 string of type Url.
Regex ★ ▲	Regex(text, matchText) returns a matching string.
RegexRepalce ★	RegexRepalce(text, matchText, replaceString) Matches the replacement string.
IsRegex ★ IsMatch ★	IsRegex(text, matchText) IsMatch(text, matchText) To determine if there is a match.
Guid	Guid() Generate a Guid string.
Md5 ★	Md5(text) Returns the Hash string of Md5.
Sha1 ★	Sha1(text) Returns the Hash string of Sha1.
Sha256 ★	Sha256(text) Returns the Hash string of Sha256.
Sha512 ★	Sha512(text) Returns the Hash string of Sha512.
HmacMd5 ★	HmacMd5(text, secret) Returns the Hash string of HmacMd5.
HmacSha1 ★	HmacSha1(text, secret) Returns the Hash string of HmacSha1.
HmacSha256 ★	HmacSha256(text, secret) Returns the Hash string of HmacSha256.
HmacSha512 ★	HmacSha512(text, secret) Returns the Hash string of HmacSha512.
TrimStart ★ LTrim ★	TrimStart(text) LTrim(text) LTrim(text[, characterSet]) Empty the left side of the string.
TrimEnd ★ RTrim ★	TrimEnd(text) RTrim(text) RTrim(text, characterSet) Empty the right side of the string.
IndexOf ★ ▲	IndexOf(text, find[, start[, index]]) Find the position of the string.
LastIndexOf ★ ▲	LastIndexOf(text, find[, start[, index]]) Find the position of the string.
Split ★	Split(text, separator) Generate array Split(text, separator, index) Returns the string pointed to by the split index.
Join ★	Join(text1, text2....) Merge strings.
Substring ★ ▲	Substring(text, start) Substring(text, start, count) Cut the string.
StartsWith ★	StartsWith(text, startText[, ignoreCase:1/0]) Determines whether the beginning of this string instance matches the specified string.
EndsWith ★	EndsWith(text, startText[, ignoreCase:1/0]) Determines whether the end of this string instance matches the specified string when comparing using the specified comparison option.
IsNullOrEmpty ★	IsNullOrEmpty(text) Indicates whether the specified string is null or an empty string.
IsNullOrWhiteSpace ★	IsNullOrWhiteSpace(text) Indicates whether the specified string is null, empty, or consisting only of white space characters.
RemoveStart ★	RemoveStart(text, leftText[, ignoreCase]) Match the left, and if you succeed, remove the left string.
RemoveEnd ★	RemoveEnd(text, rightText[, ignoreCase]) Match the right, and if you succeed, remove the string on the right.
Json ★	json(text) Dynamic json query.
Error	Error(text) Proactively throwing error.
HAS ★ HASKEY ★ CONTAINS ★ CONTAINSKEY ★	HAS(json/array,text) Does the JSON format include a Key Does the array contain values
HASVALUE ★ CONTAINSVALUE ★	HASVALUE(json/array, text) Does the JSON format include a Value Does the array contain values
PARAM PARAMETER GETPARAMETER	GETPARAMETER(text) Dynamically obtaining parameters