Mathematical FunctionsWhat 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 | - |
| 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 | 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 | Returns the number of columns in reference. | COLUMNS | - |
| Row | 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 | 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 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 | - |
| 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