MathematicalWhat functions are exported to Microsoft Excel?
Formulas configured in a regular report can be exported to Microsoft Excel as formulas. If a function included into a formula is not supported in Microsoft Excel, text or numeric value is displayed in a formula cell.
Regular report containing formulas.
The following functions are exported to Microsoft Excel:
Function |
Brief description | Excel analog | Features of export to Excel |
| It returns the module (absolute value) of a number. | ABS | - | |
| It returns the number arccosine. | ACOS | - | |
| It returns hyperbolic arccosine of a number. | ACOSH | - | |
| It returns the arccotangent of the number. | ACOT | - | |
| It returns the hyperbolic arccotangent of the number. | ACOTH | - | |
| It returns result of aggregation with selected method with a possibility to not taking into account hidden values on calculation. | AGGREGATE | The Number parameter - the Function_number parameter (Excel):
|
|
| It returns the number arcsine. | ASIN | - | |
| It returns the hyperbolic arcsine of a number. | ASINH | - | |
| It returns the number arctangent. | ATAN | - | |
| It returns the arctangent for the specified X and Y coordinates. | ATAN2 | - | |
| It returns the hyperbolic arctangent of the number. | ATANH | - | |
| It returns the average of an array of cells. | AVERAGE | - | |
| It returns the average of cells that satisfy the specified condition. | AVERAGEIF | - | |
| It returns the average of cells that satisfy the specified set of conditions. | AVERAGEIFS | - | |
| It returns the result of rounding the value up to the nearest integer or to the nearest multiple of significance. | CEALING | The last function parameter is not set. | |
| It returns the number of combinations for the specified number of objects. | COMBIN | - | |
| It returns the cosine of the specified angle. | COS | - | |
| It returns the hyperbolic cosine of a number. | COSH | - | |
| It returns the cotangent of an angle. | COT | - | |
| It returns the hyperbolic cotangent of a number. | COTH | - | |
| It returns the number of elements in an array of cells. | COUNT | - | |
| It returns the number of cells that satisfy the specified condition. | COUNTIF | - | |
| It returns the number of cells that satisfy the specified set of conditions. | COUNTIFS | - | |
| It returns a value converted from radians to degrees. | DEGREES | - | |
| It returns the result of rounding the value to the nearest even integer. | EVEN | - | |
| It returns the result of raising the number "e" to the specified power. | EXP | - | |
| It returns the factorial of the specified number. | FACT | - | |
| It returns the result of rounding the number to the nearest integer smaller in modulus. | FLOOR | The last function parameter is not set. | |
| It returns the greatest common divisor for two or more integers. | GCD | - | |
| It returns the result of rounding the value to the nearest smaller integer. | INT | - | |
| It returns the least common multiple for two or more integers. | LCM | - | |
| It returns the natural logarithm of a number. | LN | - | |
| It returns the number logarithm based on the specified base. | LOG | - | |
| It returns the base-10 logarithm of the specified number. | LOG10 | - | |
| It returns the maximum number in the cell array. | MAX | - | |
| It returns the matrix determinant of a cell array. | MDETERM | - | |
| It returns the minimum number in the cell array. | MIN | - | |
| It returns an inverse matrix for the specified matrix. | MINVERSE | - | |
| It returns the number matrix equal to the product of the two matrixes. | MMULT | - | |
| It returns the remainder from dividing a number by a denominator. | MOD | - | |
| It returns a number rounded with desired precision. | MROUND | - | |
| It returns the multinomial of a set of numbers, for which you want the multinomial. | MULTINOMIAL | - | |
| It returns the result of rounding the value to the nearest odd integer. | ODD | - | |
| It returns the result of raising a real number to real power. | POWER | - | |
| It returns the product of cell array values. | PRODUCT | - | |
| It returns the integer part of the result of division with a remainder. | QUOTIENT | - | |
| It returns a value converted from degrees to radians. | RADIANS | - | |
| It returns an equally distributed random number greater than or equal to 0 and less than 1. | RAND | - | |
| It returns a random number between two specified numbers. | RANDBETWEEN | - | |
| It returns the result of converting an Arabic number into a Roman as text. | ROMAN | - | |
| It returns the result of rounding a number to the specified number of decimal places. | ROUND | - | |
| It returns the result of rounding the number to the nearest value smaller in modulus. | ROUNDDOWN | - | |
| It returns the result of rounding the number to a nearest value greater in modulus. | ROUNDUP | - | |
| It returns the sum of a power series. | SERIESSUM | - | |
| It returns the number sign. | SIGN | - | |
| It returns the sine of the specified angle. | SIN | - | |
| It returns hyperbolic sine of the specified number. | SINH | - | |
| It returns positive value of a square root. | SQRT | - | |
| It returns the square root of Number * Pi. | SQRTPI | - | |
| It returns the sum of real numbers from the specified cell range. | SUM | - | |
| It returns the sum of numbers in the specified cell range that satisfy the specified condition. | SUMIF | - | |
| It returns the sum of numbers in the specified cell range that satisfy the specified set of conditions. | SUMIFS | - | |
| It returns the sum of squares of numbers from the specified cell range. | SUMSQ | - | |
| It returns the sum of differences of squares of corresponding values in two arrays. | SUMX2MY2 | - | |
| It returns the sum of sums of squares of corresponding values in two arrays. | SUMX2PY2 | - | |
| It returns the sum of squares of the differences of corresponding values in two arrays. | SUMXMY2 | - | |
| It returns the tangent of the specified angle. | TAN | - | |
| It returns the hyperbolic tangent of the specified angle. | TANH | - | |
| It truncates a number to the specified number of decimal places. | TRUNC | - |
Function |
Brief description | Excel analog | Features of export to Excel |
| It searches for value in the first table row and returns value of the cell located in the same column in the specified row. | HLOOKUP | The searched value must be in the cell range, in which search is executed. | |
| It searches for the specified element in cell range and returns relative position of this element in the range. | MATCH | - | |
| It returns links to a cell range that is given number of rows and columns from a given link. | OFFSET | - | |
| It searches for values in the leftmost table column and returns value of the cell located in the same column in the specified row. | VLOOKUP | The searched value must be in the cell range, in which search is executed. |
Function |
Brief description | Excel analog | Features of export to Excel |
| It returns the date by the specified year, month and day. | DATE | - | |
| It returns the result of converting a string into a date. | DATEVALUE | - | |
| It returns day number in the month. | DAY | - | |
| It returns the date increased/decreased by the specified number of months. | EDATE | - | |
| It returns an hour matching the specified time. | HOUR | - | |
| It returns minutes for the specified time. | MINUTE | - | |
| It returns a month for the specified date. | MONTH | - | |
| It returns the current date and time. | NOW | - | |
| It returns seconds for the specified time. | SECOND | - | |
| It returns time by the specified hour, minute and second. | TIME | - | |
| It returns date for the current moment in time. | TODAY | - | |
| It returns the number of a weekday based on the specified date. | WEEKDAY | In Excel, a formula is added with a parameter with the 2 value that shows that weekday numbers start with 1 (Monday). | |
| It returns the year based on the specified date. | YEAR | - |
Function |
Brief description | Excel analog | Features of export to Excel |
| _T | It determines whether the value can be converted to string type. If yes, the text is returned, if no, an empty string is returned. | T | - |
| Concatenate | It returns the result of merging two or more text strings into one. | CONCATENATE | - |
| Exact | It returns whether two text strings are exactly the same. | EXACT | - |
| Find | It returns one text string within another and returns its position. | FIND | - |
| Fixed | It returns the result of rounding a number to the specified number of decimals after the decimal separator. | FIXED | - |
| Left | It returns the specified number of characters from the start of a text string. | LEFT | - |
| Len | It returns string length. | LEN | - |
| Lower | It returns the result of string characters conversion to lowercase. | LOWER | - |
| Mid | It returns a substring from a specified string based on starting position and length. | MID | - |
| Replace | It replaces part of a text string with a different text string. | REPLACE | - |
| Rept | It returns a string repeated for the specified number of times. | REPT | - |
| Right | It returns the specified number of characters from the end of a text string. | RIGHT | - |
| Trim | It deletes spaces at the beginning and at the end of the specified string. | TRIM | - |
| Upper | It returns the result of string conversion to uppercase. | UPPER | - |
| Value | It returns the result of converting a text view of a number into numeric view. | VALUE | - |
Function |
Brief description | Excel analog | Features of export to Excel |
| It returns an accrued interest for securities with periodic interest payments. | ACCRINT | - | |
| It returns the accrued interest for a security that pays interest at maturity. | ACCRINTM | - | |
| It returns the depreciation for each period. | AMORFEGRC | - | |
| It returns the depreciation for each period. | AMORLINC | - | |
| It returns the number of days from the beginning of the coupon period to the settlement date. | SOUPDAYSBS | - | |
| It returns the number of days in the coupon period that contains the settlement date. | COUPDAYS | - | |
| It returns the number of days from the settlement date to the next coupon date. | COUPDAYSNC | - | |
| It returns the number that is the next coupon date after the settlement date. | COUPNCD | - | |
| It returns the number of coupons payable between the settlement date and maturity date that is rounded to the nearest integer number of coupons. | COUPNUM | - | |
| It returns the number that is the previous coupon date before the settlement date. | COUPPCD | - | |
| It returns the cumulative interest (running total) paid on a loan between two payment periods. | CUMIPMT | - | |
| It returns the cumulative (running total) principal paid on a loan between two periods. | CUMPRINC | - | |
| It returns the depreciation of an asset for a specified period using the fixed-declining balance method. | DB | - | |
| It returns the depreciation of an asset for a specified period using the double declining balance method or some other method you specify. | DDB | - | |
| It returns the discount rate for a security. | DISC | - | |
| It converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction. | DOLLARDE | - | |
| It converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction. | DOLLARFR | - | |
| It returns the Macaulay duration for assumed $100 face value. | DURATION | - | |
| It returns the effective (actual) annual interest rate if nominal annual interest rate and the number of compounding periods per year are set. | EFFECT | - | |
| It returns the future value of an investment based on periodic, constant payments and a constant interest rate. | FV | - | |
| It returns a future value of the initial principal after a series (schedule) of compound interest rates have been applied. | FVSCHEDULE | - | |
| It returns the interest rate for a fully invested security. | INTRATE | - | |
| It returns the interest payment for a given period for an investment, based on periodic, constant payments and a constant interest rate. | IPMT | - | |
| It returns the internal rate of return for a series of cash flows presented with their numeric values. | IRR | - | |
| It returns an interest paid over the specified investment period. | ISPMT | - | |
| It returns the modified Macaulay duration for securities with assumed $100 face value. | MDURATION | - | |
| It returns the internal rate of return for a series of periodic cash flows. | MIRR | - | |
| It returns the annual nominal interest rate if effective (actual) interest rate and the number of compounding periods per year are set. | NOMINAL | - | |
| It returns the total number of periods for an investment based on periodic, constant payments and a constant interest rate. | NPER | - | |
| It returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values). | NPV | - | |
| It returns the price per $100 face value of securities for an odd (short or long) first period. | ODDFPRICE | - | |
| It returns the income on securities with an irregular (short or long) first period. | ODDFYIELD | - | |
| Calculates the payment for a loan based on constant payments and a constant interest rate. | PMT | - | |
| It returns the payment on the principal for a given investment based on periodic, constant payments and a constant interest rate. | PPMT | - | |
| It returns the price per $100 face value of the securities that pay periodic interest. | PRICE | - | |
| It returns the price per $100 face value of a discounted security. | PRICEDISC | - | |
| It returns the price per $100 face value of the securities that pay interest at maturity. | PRICEMAT | - | |
| It returns the current value of an investment. | PV | - | |
| It returns the interest rate per period of a loan or an investment. | RATE | - | |
| It returns the amount received at maturity for a fully invested security. | RECEIVED | - | |
| It returns the straight-line depreciation of an asset for one period. | SLN | - | |
| It returns the sum-of-years' digits depreciation of an asset for a specified period. | SYD | - | |
| It returns the bond-equivalent yield for a treasury bill. | TBILLEQ | - | |
| It returns the price per $100 face value for a treasury bill. | TBILLPRICE | - | |
| It returns the income for a treasury bill. | TBILLYIELD | - | |
| It returns the value of asset depreciation over any selected period, including partial periods, using the method of double balance decrease or any other specified method. | VDB | - | |
| It returns the internal rate of return for a schedule of cash flows that are optionally periodic. | XIRR | - | |
| It returns the net present value for a schedule of cash flows that are optionally periodic. | XNPV | - | |
| It returns annual yield of the securities for which a discount is made. | YIELDDISC | - | |
| It returns the yield on a security that pays periodic interest. | YIELDF | - | |
| It returns the annual yield of a security that pays interest at maturity. | YIELDMAT | - |
Function |
Brief description | Excel analog | Features of export to Excel |
| The method calculates how often values occur within a range of values, and then returns an array of numbers. | FREQUENCY | - | |
| The method returns the average of the absolute data points deviations from the mean. | AVEDEV | - | |
| The method returns the beta cumulative distribution function. | BETADIST | - | |
| The method returns an inverse function of cumulative beta probability density function. | BETAINV | - | |
| The method returns a binomial distribution value. | BINOMDIST | - | |
| The method returns the one-tailed probability of the chi-squared distribution. | CHI2DIST | - | |
| The method returns an inverse value to one-sided chi-squared distribution probability. | CHI2INV | - | |
| The method returns value of chi-squared distribution. | CHI2TEST | - | |
| The method returns confidence interval for population mean. | CONFIDENCE | - | |
| The method returns the correlation coefficient between A1 and A2. | CORREL | - | |
| The method returns covariance, that is, the average of the products for each pair of data points. | COVAR | - | |
| The method returns the least value for which integral binomial distribution is greater than or equal to a specified criterion. | CRITBINOM | - | |
| The method returns sum of squares of data point deviations from mean. | DEVSQ | - | |
| The method estimates variance based on sample. | DISPERSION | - | |
| The method calculates variance for population. | DISPERSIONP | - | |
| The method returns exponential distribution. | EXPONDIST | - | |
| The method returns F-distribution for probability. | FDIST | - | |
| The method returns inverse value of probability F-distribution. | FINV | - | |
| The method returns Fisher transformation for the Value attribute. | FISHER | - | |
| The method returns the inverse of the Fisher transformation. | FISHERINV | - | |
| The method calculates a future value based on existing values. | FORECAST | - | |
| The method returns results of F-test. | FTEST | - | |
| The method returns gamma distribution. | GAMMADIST | - | |
| The method returns inverse gamma distribution. | GAMMAINV | - | |
| The method returns the natural logarithm of the gamma function. | GAMMALN | - | |
| The method returns geometric mean of positive number array values. | GEOMEAN | - | |
| The method returns harmonic mean for a data set. | HARMEAN | - | |
| The method returns hypergeometric distribution. | HYPGEOMDIST | - | |
| The method calculates the point where a line crosses the Y axis using KnownXs and KnownYs. | INTERCEPT | - | |
| The method returns data set kurtosis. | KURT | - | |
| The method returns k-th greatest value of a data set. | LARGE | - | |
| The method calculates series statistics using the least squares method to calculate the straight line that approximates available data in the best way. | LINEST | - | |
| The method returns the inverse function of lognormal distribution. | LOGINV | - | |
| The method returns the cumulative lognormal distribution. | LORNORMDIST | - | |
| The method returns median of specified numbers. | MEDIAN | - | |
| The method returns mode, that is, the most frequently occurring, or repetitive, value in an array of data. | MODE | - | |
| The method returns negative binomial distribution. | NEGBINOMDIST | - | |
| The method returns normal distribution function for the specified mean and standard deviation. | NORMDIST | - | |
| The method returns inverse normal distribution. | NORMINV | - | |
| The method returns Pearson correlation coefficient (r). | PEARSON | - | |
| The method returns the k-th percentile of values in a range. | PRECENTILE | - | |
| The method returns value category in a data set as a percentage in this data set. | PRECENTRANK | - | |
| The method returns the number of permutations for a specified number of objects. | PERMUT | - | |
| The method returns Poisson distribution. | POISSON | - | |
| The method returns probability that a range value lies within specified limits. | PROB | - | |
| The method returns the quartile of a data set. | QUARTILE | - | |
| The method returns the rank of a number in an array of numbers. | RANK | - | |
| The method returns squared Pearson correlation coefficient (r^2). | RSQ | - | |
| The method returns distribution skew. | SKEW | - | |
| The method returns k-th least value in a data set. | SMALL | - | |
| The method returns normalized value for the distribution described by the mean and standard deviation. | STANDARDIZE | - | |
| The method estimates standard deviation based on sample. | STDEV | - | |
| The method estimates standard deviation based on the entire population. | STDEVP | - | |
| The method returns the standard error of the predicted Y values for each X value in the regression. | STEYX | - | |
| The method returns percentage points (probability) for the Student's t-distribution, where numeric value (x) is a calculated value, probabilities for which are to be calculated. | TDIST | - | |
| The method returns t-value of Student distribution as a probability function and function of freedom degrees number. | TINV | - | |
| The method returns values along a linear trend. | TREND | - | |
| The method returns mean for interior of a data set. | TRIMMEAN | - | |
| The method returns the probability associated with the Student's criterion. | TTEST | - | |
| The method returns Weibull distribution. | WEIBULL | - | |
| The method returns the two-tailed P-value of a z-test. | STEST | - |
Function |
Brief description | Excel analog | Features of export to Excel |
| It returns logical FALSE. | FALSE | - | |
| It returns TRUE if argument is a link to an empty cell, otherwise it returns FALSE. | ISEMPTY | - | |
| It returns logical TRUE. | TRUE | - |
IMPORTANT. The given list of functions with Microsoft Excel analogs is relevant for the *.xlsx format, the list of analogs for the *.xls format is limited.
See also:
Report Export | Using Formulas