Mathematical FunctionsWhat functions are exported to Microsoft Excel?
Formulas configured in a regular report can be exported to Microsoft Excel as formulas. If the function contained in the formula is not supported in Microsoft Excel, the cell with the formula will display text numeric value.
The regular report that contains 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 | - |
| Maxa | It returns the maximum value in the cell array that contains not only numeric values. | MAXA | The True value in Foresight Analytics Platform is equal to -1, in Excel it is equal to 1. |
| MDeterm | It returns the matrix determinant of a cell array. | MDETERM | - |
| Min | It returns the minimum number in the cell array. | MIN | - |
| Mina | It returns the minimum number in the cell array containing not only numeric values. | MINA | The True value in Foresight Analytics Platform is equal to -1, in Excel it is equal to 1. |
| 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 |
| Choose | Selects value in the list of values by index number. | CHOOSE | - |
| Column | It returns the number of the referenced column. | COLUMN | The searched value in Excel will be greater by one because column numbering in Foresight Analytics Platform start with zero, and column numbering in Excel starts with one. |
| Columns | It returns the number of columns in reference. | COLUMNS | - |
| Row | It returns the number of the referenced row. | ROW | The searched value in Excel will be greater by one because column numbering in Foresight Analytics Platform start with zero, and column numbering in Excel starts with one. |
| Rows | It returns the number of rows in reference. | ROWS | - |
| 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 the square of the 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 | - |
| IfError | It returns the expression if it does not contain error, otherwise it returns the specified value. | IFERROR | - |
| Ifs | It checks for correspondence with one or several conditions and returns value for the first condition, which is set to TRUE. | IFS | It supports Excel 2019 or later. |
| IsEmpty | It returns TRUE if the argument refers to an empty cell, otherwise it returns FALSE. | ISEMPTY | - |
| IsErr | It checks if the value is error and differs from #N/A. | ISERR | Do not use range as a value. |
| IsError | It checks if the value is error. | ISERROR | Do not use range as a value. |
| IsLogical | It checks if the value is logical. | ISLOGICAL | - |
| IsNa | It checks if the value is #N/A. | ISNA | - |
| IsNonText | It checks if the value is text. | ISNONTEXT | - |
| IsNumber | It checks if the value is number. | ISNUMBER | - |
| IsText | It checks if the value is text. | ISTEXT | - |
| 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