What 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.
A regular report containing formulas.
The following functions are exported to Microsoft Excel:
Function | Brief description | Excel analog | Features of export to Excel |
Abs | It returns the module (absolute value) of a number. | ABS | - |
ACos | It returns the number arccosine. | ACOS | - |
ACosH | It returns hyperbolic arccosine of a number. | ACOSH | - |
ACot | It returns the arccotangent of the number. | ACOT | - |
ACotH | It returns the hyperbolic arccotangent of the number. | ACOTH | - |
Aggregate | 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):
|
ASin | It returns the number arcsine. | ASIN | - |
ASinH | It returns the hyperbolic arcsine of a number. | ASINH | - |
ATan | It returns the number arctangent. | ATAN | - |
ATan2 | It returns the arctangent for the specified X and Y coordinates. | ATAN2 | - |
ATanH | It returns the hyperbolic arctangent of the number. | ATANH | - |
Average | It returns the average of an array of cells. | AVERAGE | - |
AverageIf | It returns the average of cells that satisfy the specified condition. | AVERAGEIF | - |
AverageIfS | It returns the average of cells that satisfy the specified set of conditions. | AVERAGEIFS | - |
Ceiling | 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. |
Combin | It returns the number of combinations for the specified number of objects. | COMBIN | - |
Cos | It returns the cosine of the specified angle. | COS | - |
CosH | It returns the hyperbolic cosine of a number. | COSH | - |
Cot | It returns the cotangent of an angle. | COT | - |
CotH | It returns the hyperbolic cotangent of a number. | COTH | - |
Count | It returns the number of elements in an array of cells. | COUNT | - |
CountIf | It returns the number of cells that satisfy the specified condition. | COUNTIF | - |
CountIfS | It returns the number of cells that satisfy the specified set of conditions. | COUNTIFS | - |
Degrees | It returns a value converted from radians to degrees. | DEGREES | - |
Even | It returns the result of rounding the value to the nearest even integer. | EVEN | - |
Exp | It returns the result of raising the number "e" to the specified power. | EXP | - |
Fact | It returns the factorial of the specified number. | FACT | - |
Floor | It returns the result of rounding the number to the nearest integer smaller in modulus. | FLOOR | The last function parameter is not set. |
Gcd | It returns the greatest common divisor for two or more integers. | GCD | - |
Int | It returns the result of rounding the value to the nearest smaller integer. | INT | - |
Lcm | It returns the least common multiple for two or more integers. | LCM | - |
Ln | It returns the natural logarithm of a number. | LN | - |
Log | It returns the number logarithm based on the specified base. | LOG | - |
Log10 | It returns the base-10 logarithm of the specified number. | LOG10 | - |
Max | It returns the maximum number in the cell array. | MAX | - |
MDeterm | It returns the matrix determinant of a cell array. | MDETERM | - |
Min | It returns the minimum number in the cell array. | MIN | - |
MInverse | It returns an inverse matrix for the specified matrix. | MINVERSE | - |
MMult | It returns the number matrix equal to the product of the two matrixes. | MMULT | - |
Mod_ | It returns the remainder from dividing a number by a denominator. | MOD | - |
MRound | It returns a number rounded with desired precision. | MROUND | - |
Multinomial | It returns the multinomial of a set of numbers, for which you want the multinomial. | MULTINOMIAL | - |
Odd | It returns the result of rounding the value to the nearest odd integer. | ODD | - |
Power | It returns the result of raising a real number to real power. | POWER | - |
Product | It returns the product of cell array values. | PRODUCT | - |
Quotient | It returns the integer part of the result of division with a remainder. | QUOTIENT | - |
Radians | It returns a value converted from degrees to radians. | RADIANS | - |
Rand | It returns an equally distributed random number greater than or equal to 0 and less than 1. | RAND | - |
RandBetween | It returns a random number between two specified numbers. | RANDBETWEEN | - |
Roman | It returns the result of converting an Arabic number into a Roman as text. | ROMAN | - |
Round | It returns the result of rounding a number to the specified number of decimal places. | ROUND | - |
RoundDown | It returns the result of rounding the number to the nearest value smaller in modulus. | ROUNDDOWN | - |
RoundUp | It returns the result of rounding the number to a nearest value greater in modulus. | ROUNDUP | - |
SeriesSum | It returns the sum of a power series. | SERIESSUM | - |
Sign | It returns the number sign. | SIGN | - |
Sin | It returns the sine of the specified angle. | SIN | - |
SinH | It returns hyperbolic sine of the specified number. | SINH | - |
Sqrt | It returns positive value of a square root. | SQRT | - |
SqrtPi | It returns the square root of Number * Pi. | SQRTPI | - |
Sum | It returns the sum of real numbers from the specified cell range. | SUM | - |
Sumif | It returns the sum of numbers in the specified cell range that satisfy the specified condition. | SUMIF | - |
SumIfS | It returns the sum of numbers in the specified cell range that satisfy the specified set of conditions. | SUMIFS | - |
SumSq | It returns the sum of squares of numbers from the specified cell range. | SUMSQ | - |
SumX2MY2 | It returns the sum of differences of squares of corresponding values in two arrays. | SUMX2MY2 | - |
SumX2PY2 | It returns the sum of sums of squares of corresponding values in two arrays. | SUMX2PY2 | - |
SumXMY2 | It returns the sum of squares of the differences of corresponding values in two arrays. | SUMXMY2 | - |
Tan | It returns the tangent of the specified angle. | TAN | - |
TanH | It returns the hyperbolic tangent of the specified angle. | TANH | - |
Trunc | It truncates a number to the specified number of decimal places. | TRUNC | - |
Function | Brief description | Excel analog | Features of export to Excel |
HLookUp | 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. |
Match | It searches for the specified element in cell range and returns relative position of this element in the range. | MATCH | - |
Offset | It returns links to a cell range that is given number of rows and columns from a given link. | OFFSET | - |
VLookUp | 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 |
Date | It returns the date by the specified year, month and day. | DATE | - |
DateValue | It returns the result of converting a string into a date. | DATEVALUE | - |
Day | It returns day number in the month. | DAY | - |
Edate | It returns the date increased/decreased by the specified number of months. | EDATE | - |
Hour | It returns an hour matching the specified time. | HOUR | - |
Minute | It returns minutes for the specified time. | MINUTE | - |
Month | It returns a month for the specified date. | MONTH | - |
Now | It returns the current date and time. | NOW | - |
Second | It returns seconds for the specified time. | SECOND | - |
Time | It returns time by the specified hour, minute and second. | TIME | - |
Today | It returns date for the current moment in time. | TODAY | - |
Weekday | 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). |
Year | 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 |
Accrint | It returns an accrued interest for securities with periodic interest payments. | ACCRINT | - |
AccrintM | It returns the accrued interest for a security that pays interest at maturity. | ACCRINTM | - |
AmorDegrC | It returns the depreciation for each period. | AMORFEGRC | - |
AmorLinC | It returns the depreciation for each period. | AMORLINC | - |
CoupDayBs | It returns the number of days from the beginning of the coupon period to the settlement date. | SOUPDAYSBS | - |
CoupDays | It returns the number of days in the coupon period that contains the settlement date. | COUPDAYS | - |
CoupDaysNc | It returns the number of days from the settlement date to the next coupon date. | COUPDAYSNC | - |
CoupNcd | It returns the number that is the next coupon date after the settlement date. | COUPNCD | - |
CoupNum | 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 | - |
CoupPcd | It returns the number that is the previous coupon date before the settlement date. | COUPPCD | - |
CumIpmt | It returns the cumulative interest (running total) paid on a loan between two payment periods. | CUMIPMT | - |
Cumprinc | It returns the cumulative (running total) principal paid on a loan between two periods. | CUMPRINC | - |
Db | It returns the depreciation of an asset for a specified period using the fixed-declining balance method. | DB | - |
Ddb | It returns the depreciation of an asset for a specified period using the double declining balance method or some other method you specify. | DDB | - |
Disc | It returns the discount rate for a security. | DISC | - |
DollarDe | It converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction. | DOLLARDE | - |
DollarFr | It converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction. | DOLLARFR | - |
Duration | It returns the Macaulay duration for assumed $100 face value. | DURATION | - |
Effect | It returns the effective (actual) annual interest rate if nominal annual interest rate and the number of compounding periods per year are set. | EFFECT | - |
Fv | It returns the future value of an investment based on periodic, constant payments and a constant interest rate. | FV | - |
FvSchedule | It returns a future value of the initial principal after a series (schedule) of compound interest rates have been applied. | FVSCHEDULE | - |
Intrate | It returns the interest rate for a fully invested security. | INTRATE | - |
Ipmt | It returns the interest payment for a given period for an investment, based on periodic, constant payments and a constant interest rate. | IPMT | - |
Irr | It returns the internal rate of return for a series of cash flows presented with their numeric values. | IRR | - |
Ispmt | It returns an interest paid over the specified investment period. | ISPMT | - |
MDuration | It returns the modified Macaulay duration for securities with assumed $100 face value. | MDURATION | - |
MIrr | It returns the internal rate of return for a series of periodic cash flows. | MIRR | - |
Nominal | It returns the annual nominal interest rate if effective (actual) interest rate and the number of compounding periods per year are set. | NOMINAL | - |
NPer | It returns the total number of periods for an investment based on periodic, constant payments and a constant interest rate. | NPER | - |
Npv | 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 | - |
OddfPrice | It returns the price per $100 face value of securities for an odd (short or long) first period. | ODDFPRICE | - |
OddfYield | It returns the income on securities with an irregular (short or long) first period. | ODDFYIELD | - |
Pmt | Calculates the payment for a loan based on constant payments and a constant interest rate. | PMT | - |
Ppmt | It returns the payment on the principal for a given investment based on periodic, constant payments and a constant interest rate. | PPMT | - |
Price | It returns the price per $100 face value of the securities that pay periodic interest. | PRICE | - |
PriceDisc | It returns the price per $100 face value of a discounted security. | PRICEDISC | - |
PriceMat | It returns the price per $100 face value of the securities that pay interest at maturity. | PRICEMAT | - |
Pv | It returns the current value of an investment. | PV | - |
Rate | It returns the interest rate per period of a loan or an investment. | RATE | - |
Received | It returns the amount received at maturity for a fully invested security. | RECEIVED | - |
Sln | It returns the straight-line depreciation of an asset for one period. | SLN | - |
Syd | It returns the sum-of-years' digits depreciation of an asset for a specified period. | SYD | - |
TBillEq | It returns the bond-equivalent yield for a treasury bill. | TBILLEQ | - |
TBillPrice | It returns the price per $100 face value for a treasury bill. | TBILLPRICE | - |
TBillYield | It returns the income for a treasury bill. | TBILLYIELD | - |
Vdb | 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 | - |
XIrr | It returns the internal rate of return for a schedule of cash flows that are optionally periodic. | XIRR | - |
Xnpv | It returns the net present value for a schedule of cash flows that are optionally periodic. | XNPV | - |
YieldDisc | It returns annual yield of the securities, for which a discount is made. | YIELDDISC | - |
YieldF | It returns the yield on a security that pays periodic interest. | YIELDF | - |
YieldMat | It returns the annual yield of a security that pays interest at maturity. | YIELDMAT | - |
Function | Brief description | Excel analog | Features of export to Excel |
_Frequency | The method calculates how often values occur within a range of values, and then returns an array of numbers. | FREQUENCY | - |
AveDev | The method returns the average of the absolute data points deviations from the mean. | AVEDEV | - |
BetaDist | The method returns the beta cumulative distribution function. | BETADIST | - |
BetaInv | The method returns an inverse function of cumulative beta probability density function. | BETAINV | - |
BinomDist | The method returns a binomial distribution value. | BINOMDIST | - |
ChiDist | The method returns the one-tailed probability of the chi-squared distribution. | CHI2DIST | - |
ChiInv | The method returns an inverse value to one-sided chi-squared distribution probability. | CHI2INV | - |
ChiTest | The method returns value of chi-squared distribution. | CHI2TEST | - |
Confidence | The method returns confidence interval for population mean. | CONFIDENCE | - |
Correl | The method returns the correlation coefficient between A1 and A2. | CORREL | - |
Covar | The method returns covariance, that is, the average of the products for each pair of data points. | COVAR | - |
CritBinom | The method returns the least value for which integral binomial distribution is greater than or equal to a specified criterion. | CRITBINOM | - |
DevSq | The method returns sum of squares of data point deviations from mean. | DEVSQ | - |
Dispersion | The method estimates variance of the sample. | DISPERSION | - |
DispersionP | The method calculates variance for population. | DISPERSIONP | - |
ExponDist | The method returns exponential distribution. | EXPONDIST | - |
FDist | The method returns F-distribution for probability. | FDIST | - |
FInv | The method returns the inverse of the F-probability distribution. | FINV | - |
Fisher | The method returns Fisher transformation for the Value attribute. | FISHER | - |
FisherInv | The method returns the inverse of the Fisher transformation. | FISHERINV | - |
Forecast | The method calculates a future value based on existing values. | FORECAST | - |
FTest | The method returns results of F-test. | FTEST | - |
GammaDist | The method returns the gamma distribution. | GAMMADIST | - |
GammaInv | The method returns the inverse of gamma distribution. | GAMMAINV | - |
GammaLn | The method returns the natural logarithm of the gamma function. | GAMMALN | - |
GeoMean | The method returns geometric mean of positive number array values. | GEOMEAN | - |
HarMean | The method returns harmonic mean for a data set. | HARMEAN | - |
HypGeomDist | The method returns the hypergeometric distribution. | HYPGEOMDIST | - |
Intercept | The method calculates the point where a line crosses the Y axis using KnownXs and KnownYs. | INTERCEPT | - |
Kurt | The method returns data set kurtosis. | KURT | - |
Large | The method returns k-th greatest value of a data set. | LARGE | - |
Linest | The method calculates series statistics using the least squares method to calculate the straight line that approximates available data in the best way. | LINEST | - |
LogInv | The method returns the inverse function of lognormal distribution. | LOGINV | - |
LogNormDist | The method returns the cumulative lognormal distribution. | LORNORMDIST | - |
Median | The method returns median of specified numbers. | MEDIAN | - |
Mode | The method returns mode, that is, the most frequently occurring, or repetitive, value in a data array. | MODE | - |
NegBinomDist | The method returns the negative binomial distribution. | NEGBINOMDIST | - |
NormDist | The method returns normal distribution function for the specified mean and standard deviation. | NORMDIST | - |
NormInv | The method returns the inverse of the normal distribution. | NORMINV | - |
Pearson | The method returns Pearson correlation coefficient (r). | PEARSON | - |
Percentile | The method returns the k-th percentile of values in a range. | PRECENTILE | - |
PercentRank | The method returns value category in a data set as a percentage in this data set. | PRECENTRANK | - |
Permut | The method returns the number of permutations for a specified number of objects. | PERMUT | - |
Poisson | The method returns Poisson distribution. | POISSON | - |
Prob | The method returns probability that a range value lies within specified limits. | PROB | - |
Quartile | The method returns the quartile of a data set. | QUARTILE | - |
Rank | The method returns the rank of a number in an array of numbers. | RANK | - |
Rsq | The method returns squared Pearson correlation coefficient (r^2). | RSQ | - |
Skew | The method returns distribution skew. | SKEW | - |
Small | The method returns k-th least value in a data set. | SMALL | - |
Standardize | The method returns normalized value for the distribution described by the mean and standard deviation. | STANDARDIZE | - |
StDev | The method estimates standard deviation based on a sample. | STDEV | - |
StDevP | The method calculates standard deviation based on the entire population. | STDEVP | - |
SteYX | The method returns the standard error of the predicted Y values for each X value in the regression. | STEYX | - |
TDist | 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 | - |
TInv | The method returns t-value of Student distribution as a probability function and function of freedom degrees number. | TINV | - |
Trend | The method returns values along a linear trend. | TREND | - |
TrimMean | The method returns mean for interior of a data set. | TRIMMEAN | - |
TTest | The method returns the probability associated with the Student's criterion. | TTEST | - |
Weibull | The method returns the Weibull distribution. | WEIBULL | - |
ZTest | The method returns the two-tailed P-value of a z-test. | STEST | - |
Function | Brief description | Excel analog | Features of export to Excel |
False | It returns logical FALSE. | FALSE | - |
IsEmpty | It returns TRUE if argument is a link to an empty cell, otherwise it returns FALSE. | ISEMPTY | - |
True | 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