Excel function collection

DAVERAGE returns the average value of the selected database entries
DCOUNT calculates the number of cells in the database that contain numbers.
Non-empty cells in the DCOUNTA computing Database
DGET extracts a single record from the database that meets the specified conditions
DMAX returns the maximum value of the selected database entry
DMIN returns the minimum value of the selected database entry
DPRODUCT multiply the values of specific fields in the database that meet the conditions
DSTDEV sample Estimation Standard deviation based on selected database entries
DSTDEVP overall calculation standard deviation based on selected database entries
DSUM adds the numbers in the field columns of qualified records in the database
DVAR sample variance estimation based on selected database entries
DVARP returns the population variance of the selected database entries.
Getttdata returns the data stored in the PivotTable.
Date and Time Functions
DATE returns the serial number of a specific DATE.
DATEVALUE converts a date in text format to a serial number
DAY converts the serial number to the date of the month
DAYS360 calculates the number of days in the two-day period based on the 360 days of a year.
Returns the serial number of a date that represents the number of months before or after the start date.
EOMONTH returns the serial number of the last day of a specified number of months.
HOUR converts the serial number to HOUR
MINUTE converts the serial number to minutes
MONTH converts the serial number to MONTH.
NETWORKDAYS returns the number of all working days between two dates.
NOW returns the serial number of the current date and time.
SECOND converts the serial number to seconds
TIME returns the serial number of a specific TIME.
TIMEVALUE converts the time in text format to the serial number
TODAY returns the serial number of the current day.
WEEKDAY converts the serial number to a day of the week.
WEEKNUM converts the serial number to the number representing the week of the year.
WORKDAY returns the serial number of a specified number of working days before or after the specified date.
YEAR converts the serial number to YEAR
YEARFRAC returns the year score representing the number of days between start_date and end_date.
External Functions
These functions are loaded using the add-on program.
EUROCONVERT converts the number to the euro format, and the number from the euro format to the currency form of the EU Member States, or use the euro as the intermediate currency to convert the number from the currency of one EU Member State to the currency of another EU member State (triangular conversion relationship ).
SQL. REQUEST connects to an external data source and runs the query from the worksheet. Then, the query result is returned as an array without macro programming.
Engineering Functions
BESSELI returns the modified Bessel function In (x)
BESSELJ returns the Bessel function Jn (x)
BESSELK returns the modified Bessel function Kn (x)
BESSELY returns the Bessel function Yn (x)
BIN2DEC converts binary to decimal.
BIN2HEX converts binary to hexadecimal
BIN2OCT converts binary to octal.
COMPLEX converts real numbers and virtual coefficients into plural numbers
CONVERT Converts a number from one measurement system to another.
DEC2BIN converts decimal number to binary number
DEC2HEX converts the decimal number to the hexadecimal number.
DEC2OCT converts decimal number to octal number
DELTA checks whether two values are equal
ERF returns an error function
ERFC returns complementary error functions
GESTEP checks whether the number exceeds the threshold
HEX2BIN converts hexadecimal to binary
HEX2DEC converts a hexadecimal number to a decimal number.
HEX2OCT converts hexadecimal number to octal number
IMABS returns the absolute value (modulus) of the plural number)
IMAGINARY returns the virtual coefficient of the plural number
IMARGUMENT returns the theta parameter, which is the angle in radians.
IMCONJUGATE returns the complex number of the compound number.
IMCOS returns the cosine of the plural.
IMDIV returns two vertices.
IMEXP returns the exponent of the plural number
IMLN returns the natural logarithm of the plural.
IMLOG10 returns the base-10 logarithm of the plural number.
IMLOG2 returns the base 2 logarithm of the plural number.
IMPOWER returns the plural integer power.
IMPRODUCT returns the product of two plural numbers.
IMREAL returns the real number of complex numbers.
IMSIN returns the sine of the plural number.
IMSQRT returns the square root of the plural number
IMSUB returns the difference between two plural values.
IMSUM returns the sum of multiple plural values.

OCT2BIN converts octal to binary

OCT2DEC converts the octal number to the decimal number
OCT2HEX converts the octal number to the hexadecimal number.
Financial Functions
ACCRINT returns the accrued interest on the periodically paid marketable securities
ACCRINTM returns the accrued interest of one-time interest-based securities upon expiration
AMORDEGRC returns the depreciation value for each settlement period using the depreciation factor
AMORLINC returns the depreciation value for each settlement period
COUPDAYBS returns the number of days from the current interest payment period to the transaction date
COUPDAYS returns the number of days in which the transaction date is included.
COUPDAYSNC returns the number of days from the transaction date to the next interest payment day.
COUPNCD returns the next interest payment day after the transaction date
COUPNUM returns the number of interest payments between the transaction date and the expiration date
COUPPCD returns the previous interest payment day before the transaction date
CUMIPMT returns the cumulative interest paid in two periods
CUMPRINC returns the amount of the principal that a loan has accumulated over two periods
B uses the progressive subtraction of fixed balances to return the depreciation value of an asset within the given period
DDB returns the depreciation value of an asset within the specified period by using the double balance progressive subtraction or other specified methods.
DISC returns the discount rate of marketable securities
DOLLARDE converts the price represented by scores to the price represented by decimals.
DOLLARFR converts the price represented by decimal places to the price represented by scores.
DURATION returns the annual period of the fixed interest securities
EFFECT returns the actual annual rate
FV returns the future value of an investment.
FVSCHEDULE returns the future value of the principal after applying a series of compound interest rates
INTRATE returns the interest rate of one-time interest-based securities.
IPMT returns the interest paid by an investment within the given period
IRR returns the internal rate of return for a series of cash flows
ISPMT calculates the interest to be paid during a specific investment period
MDURATION returns the Macauley correction term for a marketable securities with a nominal value of $100.
Internal Rate of Return of MIRR and negative cash flow at different interest rates
NOMINAL returns the NOMINAL annual interest rate
NPER return the amount during the investment period
NPV returns the net value of the investment calculated based on a series of regular cash flows and discount rates
ODDFPRICE returns the marketable securities with an unfixed nominal value of $100 for the first interest payment day.
ODDFYIELD returns the rate of return for marketable securities that are not fixed on the first interest payment day
ODDLPRICE returns the marketable securities price with an unfixed face value of $100 at the end of the interest payment day.
ODDLYIELD returns the rate of return of securities with Fixed End-time interest payment days
PMT returns the regular payment of the annuities
PPMT returns the principal paid by an investment within the given period
PRICE Returns the PRICE of the marketable securities with a nominal value of $100 for the regular interest payment.
PRICEDISC returns the price of the marketable securities with a coupon value of $100.
PRICEMAT returns the price of the marketable securities with a face value of $100 due to interest payment.
PV returns the present value of investment.
RATE returns the interest RATE of each period of the annuities.
RECEIVED returns the amount of securities that have been paid in one lump sum upon expiration.
SLN returns the linear depreciation of an asset over a period of time.
SYD returns the depreciation value for the specified period calculated by the sum of years of an asset
TBILLEQ returns the equivalent return rate of the Treasury bill
TBILLPRICE returns the price of the $100 coupon.
TBILLYIELD returns the return rate of the Treasury bill
VDB uses the balance progressive Subtraction Method to return the depreciation value of an asset during a given period or partial period.
XIRR returns the internal rate of return for a group of irregular cash flows
XNPV returns the net present value of a group of irregular cash flows.
YIELD returns the YIELD of fixed interest securities
YIELDDISC returns the annual yield of marketable securities (such as treasury bonds) issued at a discount.
YIELDMAT returns the annual rate of return for the securities with interest due
Information Functions
CELL returns the format, position, or content of a CELL.
ERROR. TYPE Returns the number of ERROR types
INFO returns information about the current operating environment
ISBLANK returns TRUE if the value is null.
If the ISERR value is any error value except # N/A, TRUE is returned.
ISERROR if the value is any error value, TRUE is returned.
ISEVEN returns TRUE if the number is an even number.
If the ISLOGICAL value is a logical value, TRUE is returned.
ISNA returns TRUE if the value is # N/.
ISNONTEXT returns TRUE if the value is not text
ISNUMBER. If the value is a number, TRUE is returned.
ISODD returns TRUE if the number is odd.
ISREF returns TRUE if the value is a reference value.
If the value of ISTEXT is text, TRUE is returned.
N returns the value converted to a number.
NA returns an error value # N/
TYPE: a numeric logical operator that represents the Data TYPE of the value.

AND returns TRUE if all its parameters are TRUE.
FALSE: the logical value is FALSE.
IF indicates the logic test to be executed
NOT logical inversion of its parameters
OR returns TRUE if all parameters are TRUE.
TRUE returns the logical value TRUE.
Search and reference functions
ADDRESS returns the reference value to a single cell in the worksheet in text format.
AREAS returns the number of regions included in the reference value.
CHOOSE Select a value from a column Value
COLUMN returns the number of referenced Columns
COLUMNS returns the number of COLUMNS included in the reference.
HLOOKUP searches for the first row of the array and returns the value of a specific cell.
HYPERLINK creates a shortcut or jump to open files stored on the network server, Intranet, or Internet
INDEX use INDEX to select value from reference or Array
INDIRECT returns the reference specified by the text value
LOOKUP searches for values in a vector or array.
MATCH searches for values in referenced values or Arrays
Returns the reference OFFSET from the given reference value.
ROW returns the number of referenced rows.
ROWS returns the number of ROWS in the reference.
RTD returns real-time data from programs supporting COM automation
TRANSPOSE returns the TRANSPOSE of the array
VLOOKUP searches for the first column of the array, moves it to the row, and returns the value of the cell.
Mathematical and trigonometric Functions
ABS returns the absolute value of a number.
ACOS returns the arc cosine of a number.
Return the inverse hyperbolic cosine of a number.
ASIN returns the inverse sine of a number.
ASINH returns the inverse hyperbolic sine of a number.
ATAN returns the arc tangent of a number.
ATAN2 returns the arc tangent of X and Y coordinates.
ATANH returns the inverse hyperbolic tangent of a number.
CEILING rounds a number to the nearest integer or a multiple of the nearest Significance.
COMBIN returns the combination of a given number of objects.
COS returns the cosine of a number.
COSH returns the hyperbolic cosine of a number.
DEGREES converts radians to DEGREES
EVEN rounds up the number to the nearest EVEN number.
EXP returns the given number power of e.
FACT returns the factorial of a number.
FACTDOUBLE returns the double factorial of a number.
The FLOOR rounds a number in the direction of decreasing the absolute value.
GCD returns the maximum public approx.
INT places the number down to the nearest integer.
LCM returns the smallest public multiple
LN returns the natural logarithm of a number.
LOG returns the logarithm of a number based on the specified base number.
LOG10 returns the base-10 logarithm of a number.
MDETERM returns the matrix determining value of the array.
MINVERSE returns the inverse matrix of the array
MMULT returns the matrix product of two arrays.
MOD returns the remainder of the Division.
MROUND returns the number rounded to a specified multiple.
MULTINOMIAL returns the polynomials of a set of numbers.
ODD rounds up the number to the nearest ODD number.
PI returns the value of pi.
POWER returns the result of a given number POWER.
PRODUCT multiply its parameters
QUOTIENT returns the integer part of the Division.
RADIANS converts degrees to RADIANS
RAND returns a random number between 0 and 1.
RANDBETWEEN returns a random number between specified numbers.
ROMAN converts Arabic numerals into ROMAN numerals in the text format
ROUND rounds a number by a specified number of digits
ROUNDDOWN rounds a number in the direction of decreasing the absolute value.
ROUNDUP round a number in the direction of increasing the absolute value
SERIESSUM returns the sum of power series based on the following formula.
SIGN
SIN returns the sine of the given angle
SINH returns the hyperbolic sine of a number.
SQRT returns the positive square root.
SQRTPI returns the square root of the product of a number and pi.
SUBTOTAL return to the list or Category summary in the database
SUM adds its parameters
SUMIF sums several cells according to the given conditions.
SUMPRODUCT returns the product and of the corresponding array elements.
Sum of squares of parameters returned by SUMSQ
SUMX2MY2 returns the sum of the squared differences of the values in the two arrays.
SUMX2PY2 returns the sum of the squares of the corresponding values in the two arrays.
SUMXMY2 returns the sum of the squared differences of the values in the two arrays.
TAN returns the tangent of a number.
TANH returns the hyperbolic tangent of a number.
TRUNC truncates the end of a number
Statistical functions
AVEDEV returns the absolute mean deviation between the data points and their average values
AVERAGE returns the AVERAGE value of its parameters.
AVERAGEA returns the average value of its parameters, including numbers, text, and logical values.
BETADIST returns the Beta cumulative distribution function.
BETAINV returns the inverse function of the cumulative distribution function of the specified Beta distribution.
BINOMDIST returns the probability value of the mona1 binary distribution.
CHIDIST returns the single-tail probability of the gamma 2 Distribution
CHIINV returns the inverse function of the single-tail probability of the gamma 2 Distribution
CHITEST returns the independence test value
CONFIDENCE returns the CONFIDENCE interval of the overall average.

CORREL returns the correlation coefficient between two datasets.
COUNT calculates the number of digits in the parameter list.
COUNTA calculates the number of values in the parameter list.
COUNTBLANK calculates the number of blank cells in the interval.
COUNTIF calculates the number of non-empty cells in the interval that meets the given conditions.
COVAR returns the covariance, which is the mean value of the pair deviation product.
The return value of CRITBINOM is the minimum value that makes the cumulative binary distribution less than or equal to the critical value.
DEVSQ returns the sum of squares of deviations
EXPONDIST returns Exponential Distribution
FDIST returns F Probability Distribution
FINV returns the inverse function value of F probability distribution.
FISHER returns the Fisher transform.
FISHERINV returns the inverse function value of the Fisher transform.
FORECAST returns the value along the linear trend
FREQUENCY returns the FREQUENCY distribution in the form of a vertical array
FTEST returns the result of the F test.
GAMMADIST returns gamma distribution
GAMMAINV returns the inverse function of the Gamma cumulative distribution function.
GAMMALN returns the natural logarithm of the Gamma function)
GEOMEAN returns the geometric mean value.
GROWTH returns the value along the exponential trend
HARMEAN returns the harmonic mean.
Hypgomdist returns the Hypergeometric Distribution.
INTERCEPT returns the INTERCEPT of the linear regression line.
KURT returns the peak value of the dataset.
LARGE returns the maximum k value in the dataset.
LINEST return linear trend Parameters
LOGEST returns the exponential trend Parameter
LOGINV returns the inverse function of the logarithm distribution function.
LOGNORMDIST returns the logarithm Cumulative Distribution Function
MAX returns the maximum value in the parameter list.
MAXA returns the maximum value in the parameter list, including numbers, text, and logical values.
MEDIAN returns the value of the given value set.
MIN returns the minimum value in the parameter list.
MINA returns the minimum value in the parameter list, including numbers, text, and logical values.
MODE returns the value that appears the most frequently in the dataset.
NEGBINOMDIST returns the negative binary distribution.
NORMDIST returns normal cumulative distribution
NORMINV returns the inverse function of normal cumulative distribution.
NORMSDIST returns standard normal cumulative distribution
NORMSINV returns the inverse function of the standard normal cumulative distribution.
PEARSON returns the Pearson product moment correlation coefficient.
PERCENTILE returns the value of the K percentage point in the region.
PERCENTRANK returns the percentage ranking of values in the dataset
PERMUT returns the number of objects to be sorted
POISSON return POISSON distribution
The probability that the value in the returned region falls within the specified range.
QUARTILE returns the QUARTILE of a dataset.
RANK returns the number ranking of a column of numbers.
RSQ returns the square of the Pearson product moment correlation coefficient.
SKEW returns the skewness of the distribution.
SLOPE returns the SLOPE of the linear regression line
SMALL returns the minimum K value in the dataset.
STANDARDIZE returns a normalized value.
STDEV estimates standard deviations Based on given samples
STDEVA estimates standard deviations Based on given samples (including numbers, text, and logical values)
STDEVP calculates the standard deviation based on the given sample population
STDEVPA calculates standard deviations based on the population (including numbers, text, and logical values)
STEYX returns the standard error generated when the y predicted values of each x are calculated using linear regression.
TDIST returns Student t distribution
TINV returns the inverse function of Student t distribution
TREND returns the value along the linear TREND.
TRIMMEAN returns the internal average value of the dataset.
TTEST returns the probability associated with the student's t test.
VAR estimation Sample-based variance
VARA estimates variance Based on samples (including numbers, text, and logical values)
VARP calculates population-based variance
VARPA calculates the variance based on the sample population (including numbers, text, and logical values)
Weibo returns the Weber Distribution.
ZTEST returns the single-tail probability value of the z test.
Text and data Functions
ASC changes the full-width (dual-byte) English letter or katakana in the string to a half-width (single-byte) character.
BAHTTEXT converts a number to text in Thai baht format
CHAR returns the character corresponding to the code number
CLEAN Delete unprintable characters in text
CODE returns the numeric CODE of the first character in the text string.
CONCATENATE combines several text items into one text item
DOLLAR converts a number to text in $ (USD) Currency format
EXACT checks whether the two text values are the same
FIND finds another text value in one text value (case sensitive)
FIXED sets the number format to a FIXED number of decimal places
JIS changes the half-width (single-byte) English letters or katakana characters in the string to full-width (dual-byte) characters
LEFT returns the leftmost character in the text value.
LEN returns the number of characters in a text string.
LOWER converts text to lowercase
In a text string, MID Returns a specified number of characters starting from the specified position.
PHONETIC extracts pinyin (furigana) characters from text strings
PROPER capital the first letter of each word in the text value
REPLACE to REPLACE characters in text
REPT repeatedly displays text based on the given number of times
RIGHT returns the rightmost character in the text value.
SEARCH searches for another text value in one text value (case-insensitive)
SUBSTITUTE replaces the old text with the new text in the text string
T converts its parameters to text
TEXT sets the numeric format and converts it to TEXT
TRIM removes spaces from text
UPPER converts text to uppercase
VALUE converts a text parameter to a number.