-
Notifications
You must be signed in to change notification settings - Fork 275
Supported Functions
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
DAVERAGE | Calculates the average of values in a field of a list or database, that satisfy specified conditions | 4 | |
DCOUNT | Returns the number of cells containing numbers in a field of a list or database that satisfy specified conditions | 4 | |
DCOUNTA | Returns the number of non-blank cells in a field of a list or database, that satisfy specified conditions | 4 | |
DGET | Returns a single value from a field of a list or database, that satisfy specified conditions | 4 | |
DMAX | Returns the maximum value from a field of a list or database, that satisfy specified conditions | 4 | |
DMIN | Returns the minimum value from a field of a list or database, that satisfy specified conditions | 4 | |
DPRODUCT | Multiplies the values in a field of a list or database, that satisfy specified conditions | 7.3 | |
DSTDEV | Calculates the standard deviation values in a field of a list or database, that satisfy specified conditions | 7.3 | |
DSTDEVP | Calculates the standard deviation of values in a field of a list or database, that satisfy specified conditions (for the whole population) | 7.3 | |
DSUM | Calculates the sum of values in a field of a list or database, that satisfy specified conditions | 4 | |
DVAR | Calculates the variance (based on a sample of a population) of values in a field of a list or database, that satisfy specified conditions | 4 | |
DVARP | Calculates the variance (based on an entire population) of values in a field of a list or database, that satisfy specified conditions | 4 |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
DATE | Returns a date, from a user-supplied year, month and day | 4 | |
DATEDIF | Get days, months, or years between two dates | 5.5 | |
DATEVALUE | Converts a text string showing a date, to an integer that represents the date in Excel's date-time code | 4 | |
DAY | Returns the day (of the month) from a user-supplied date | 4 | |
DAYS | Calculates the number of days between 2 dates | 4 | _xlfn. |
DAYS360 | Calculates the number of days between 2 dates, based on a 360-day year (12 x 30 months) | 4 | |
EDATE | Returns a date that is the specified number of months before or after an initial supplied start date | 4 | |
EOMONTH | Returns a date that is the last day of the month which is a specified number of months before or after an initial supplied start date | 4 | |
HOUR | Returns the hour part of a user-supplied time | 4 | |
ISOWEEKNUM | Returns the ISO week number of the year for a given date | 4 | _xlfn. |
MINUTE | Returns the minute part of a user-supplied time | 4 | |
MONTH | Returns the month from a user-supplied date | 4 | |
NETWORKDAYS | Returns the number of whole networkdays (excluding weekends & holidays), between two supplied dates | 4 | |
NETWORKDAYS.INTL | Returns the number of whole networkdays (excluding weekends & holidays), between two supplied dates, using parameters to specify weekend days | 4 | |
NOW | Returns the current date & time | 4 | |
SECOND | Returns the seconds part of a user-supplied time | 4 | |
TIME | Returns a time, from a user-supplied hour, minute and second | 4 | |
TIMEVALUE | Converts a text string showing a time, to a decimal that represents the time in Excel | 4 | |
TODAY | Returns today's date | 4 | |
WEEKDAY | Returns an integer representing the day of the week for a supplied date | 4 | |
WEEKNUM | Returns an integer representing the week number (from 1 to 53) of the year from a user-supplied date | 4 | |
WORKDAY | Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date | 4 | |
WORKDAY.INTL | Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date, using supplied parameters to specify weekend days | 5.0 | _xlfn. |
YEAR | Returns the year from a user-supplied date | 4 | |
YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates | 4 |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
BESSELI | Calculates the modified Bessel function In(x) | 5.2 | |
BESSELJ | Calculates the Bessel function Jn(x) | 5.2 | |
BESSELK | Calculates the modified Bessel function Kn(x) | 5.2 | |
BESSELY | Calculates the modified Bessel function Yn(x) | 5.2 | |
BIN2DEC | Converts a binary number to a decimal | 5.1 | |
BIN2HEX | Converts a binary number to hexadecimal | 5.1 | |
BIN2OCT | Converts a binary number to octal | 5.1 | |
BITAND | Returns a Bitwise 'And' of two numbers | 5.1 | _xlfn. |
BITLSHIFT | Returns a number shifted left by a specified number of bits | 5.1 | _xlfn. |
BITOR | Returns a Bitwise 'Or' of two numbers | 5.1 | _xlfn. |
BITRSHIFT | Returns a number shifted right by a specified number of bits | 5.1 | _xlfn. |
BITXOR | Returns a Bitwise 'Exclusive Or' of two numbers | 5.1 | _xlfn. |
COMPLEX | Converts user-supplied real and imaginary coefficients into a complex number | 5.5 | |
CONVERT | Converts a number from one measurement system to another | 5.1 | |
DEC2BIN | Converts a decimal number to binary | 5.1 | |
DEC2HEX | Converts a decimal number to hexadecimal | 5.1 | |
DEC2OCT | Converts a decimal number to octal | 5.1 | |
DELTA | Tests whether two supplied numbers are equal | 5.1 | |
ERF | Returns the error function integrated between two supplied limits | 5.2 | |
ERF.PRECISE | Returns the complementary error function integrated between a supplied lower limit and infinity | 5.2 | _xlfn. |
ERFC | Returns the complementary error function integrated between a supplied lower limit and infinity | 5.2 | |
ERFC.PRECISE | Returns the complementary error function integrated between a supplied lower limit and infinity | 5.2 | _xlfn. |
GESTEP | Returns 1 if number ≥ step; returns 0 (zero) otherwise. Use this function to filter a set of values. For example, by summing several GESTEP functions you calculate the count of values that exceed a threshold | 7.0 | |
HEX2BIN | Converts a hexadecimal number to binary | 5.1 | |
HEX2DEC | Converts a hexadecimal number to a decimal | 5.1 | |
HEX2OCT | Converts a hexadecimal number to octal | 5.1 | |
IMABS | Returns the absolute value of a complex number in x + yi or x + yj text format. | 7.0 | |
IMAGINARY | Returns the imaginary coefficient of a complex number in x + yi or x + yj text format. | 7.0 | |
IMARGUMENT | Returns the argument Theta(theta), an angle expressed in radians. | 7.0 | |
IMCONJUGATE | Returns the complex conjugate of a complex number in x + yi or x + yj text format. | 7.0 | |
IMCOS | Returns the cosine of a complex number in x + yi or x + yj text format. | 7.0 | |
IMCOSH | Returns the hyperbolic cosine of a complex number in x+yi or x+yj text format. | 7.0 | _xlfn. |
IMCOT | Returns the cotangent of a complex number in x+yi or x+yj text format. | 7.0 | _xlfn. |
IMCSC | Returns the cosecant of a complex number in x+yi or x+yj text format. | 7.0 | _xlfn. |
IMCSCH | Returns the hyperbolic cosecant of a complex number in x+yi or x+yj text format. | 7.0 | _xlfn. |
IMDIV | Returns the quotient of two complex numbers in x + yi or x + yj text format. | 7.0 | |
IMEXP | Returns the exponential of a complex number in x + yi or x + yj text format. | 7.0 | |
IMLN | Returns the tangent of a complex number in x+yi or x+yj text format. | 7.0 | |
IMLOG10 | Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format. | 7.0 | |
IMLOG2 | Returns the sine of a complex number in x + yi or x + yj text format. | 7.0 | |
IMPOWER | Returns a complex number in x + yi or x + yj text format raised to a power | 7.0 | |
IMPRODUCT | Returns the product of 1 to 255 complex numbers in x + yi or x + yj text format. | 7.0 | |
IMREAL | Returns the real coefficient of a complex number in x + yi or x + yj text format. | 7.0 | |
IMSEC | Returns the secant of a complex number in x+yi or x+yj text format. | 7.0 | _xlfn. |
IMSECH | Returns the hyperbolic secant of a complex number in x+yi or x+yj text format. | 7.0 | _xlfn. |
IMSIN | Returns the sine of a complex number in x + yi or x + yj text format. | 7.0 | |
IMSINH | Returns the hyperbolic sine of a complex number in x + yi or x + yj text format. | 7.0 | _xlfn. |
IMSQRT | Returns the square root of a complex number in x + yi or x + yj text format. | 7.0 | |
IMSUB | Returns the difference of two complex numbers in x + yi or x + yj text format. | 7.0 | |
IMSUM | Returns the sum of two or more complex numbers in x + yi or x + yj text format. | 7.0 | |
IMTAN | Returns the tangent of a complex number in x+yi or x+yj text format. | 7.0 | _xlfn. |
OCT2BIN | Converts octal number to binary | 5.1 | |
OCT2DEC | Converts octal number to a decimal | 5.1 | |
OCT2HEX | Converts octal number to hexadecimal | 5.1 | |
YIELDDISC | Returns the annual yield for a discounted security. | 7.2.1 | _xlfn. |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
ACCRINT | Calculates the accrued interest for a security that pays periodic interest. | 6.0 | |
ACCRINTM | Calculates he accrued interest for a security that pays interest at maturity. | 6.0 | |
COUPDAYBS | Calculates the number of days from the beginning of the coupon period to the settlement date | 5.2 | |
COUPDAYS | Calculates the number of days in the coupon period that contains the settlement date | 5.2 | |
COUPDAYSNC | Calculates the number of days from the settlement date to the next coupon date | 5.2 | |
COUPNCD | Returns the next coupon date after the settlement date | 5.2 | |
COUPNUM | Returns the number of coupons payable between the settlement date and maturity date | 5.2 | |
COUPPCD | Returns the previous coupon date, before the settlement date | 5.2 | |
CUMIPMT | Calculates the cumulative interest paid between two specified periods | 5.2 | |
CUMPRINC | Calculates the cumulative principal paid on a loan, between two specified periods | 5.2 | |
DB | Calculates the depreciation of an asset for a specified period, using the fixed-declining balance method | 5.5 | |
DDB | Calculates the depreciation of an asset for a specified period, using the double-declining balance method, or some other user-specified method | 5.2 | |
DISC | Calculates the discount rate for a security | 5.2 | |
DOLLARDE | Converts a dollar price expressed as a fraction, into a dollar price expressed as a decimal | 5.5 | |
DOLLARFR | Converts a dollar price expressed as a decimal, into a dollar price expressed as a fraction | 5.5 | |
DURATION | Calculates the Macauley duration of a security with an assumed par value of $100 | 5.2 | |
EFFECT | Calculates the effective annual interest rate from a supplied Nominal interest rate and number of periods | 5.2 | |
FV | Calculates the future value of an investment with periodic constant payments and a constant interest rate | 5.2 | |
FVSCHEDULE | Calculates the future value of an initial principal, after applying a series of compound interest rates | 5.2 | |
INTRATE | Calculates the interest rate for a fully invested security | 5.5 | |
IPMT | Calculates the interest payment for a given period of an investment, with periodic constant payments and a constant interest rate | 5.2 | |
IRR | Calculates the internal rate of return for a series of periodic cash flows | 5.2 | |
ISPMT | Returns the interest paid during a specified period of an investment | 5.2 | |
MDURATION | Calculates the Macauley modified duration for a security with an assumed par value of $100 | 5.5 | |
MIRR | Calculates the internal rate of return for a series of periodic cash flows, considering the cost of the investment and the interest on the reinvestment of cash | 5.2 | |
NOMINAL | Calculates the annual nominal interest rate from a supplied Effective interest rate and number of periods | 5.2 | |
NPER | Returns the number of periods for an investment with periodic constant payments and a constant interest rate | 5.2 | |
NPV | Calculates the net present value of an investment, based on a supplied discount rate, and a series of periodic cash flows | 5.2 | |
ODDFPRICE | Returns the price of a security having an irregular (long or short) first period. Price is per $100 face value. | 7.0 | |
ODDFYIELD | Returns the yield of a bond or other security that has a long or short first period. | 7.0 | |
ODDLPRICE | Returns yield of a security that has an irregular (odd) last period. | 7 | |
ODDLYIELD | Returns yield of a security that has an irregular (odd) last period. | 7 | |
PDURATION | Calculates the number of periods required for an investment to reach a specified value | 5.2 | _xlfn. |
PMT | Calculates the payments required to reduce a loan, from a supplied present value to a specified future value | 4 | |
PPMT | Calculates the payments required to reduce a loan, from a supplied present value to a specified future value | 5.2 | |
PRICE | Calculates the price per $100 face value of a security that pays periodic interest | 5.2 | |
PRICEDISC | Calculates the price per 100$ Face value of a discounted bond or other security. | 7.0 | |
PRICEMAT | Calculates the price of a security that pays interest maturity (per 100 dollars) | 7.0 | |
PV | Calculates the present value of an investment (i.e. the total amount that a series of future periodic constant payments is worth now) | 5.2 | |
RATE | Calculates the interest rate required to pay off a specified amount of a loan, or reach a target amount on an investment over a given period | 5.2 | |
RECEIVED | Calculates amount received at maturity for fully invested bond or other security. | 7.0 | |
RRI | Calculates the interest rate required for an investment to grow to a specified future value | 5.2 | _xlfn. |
SLN | Returns the straight-line depreciation of an asset for one period | 5.2 | |
SYD | Returns the sum-of-years' digits depreciation of an asset for a specified period | 5.2 | |
TBILLEQ | Calculates the bond-equivalent yield for a treasury bill | 6.0 | |
TBILLPRICE | Calculates the price per $100 face value for a treasury bill | 6.0 | |
TBILLYIELD | Calculates the yield for a treasury bill | 6.0 | |
VDB | Calculates the depreciation of an asset over a specific period. Calculates the depreciation with eitherdouble declining method or straight line method. | 7.0 | |
XIRR | Calculates the internal rate of return for a schedule of cash flows occurring at a series of supplied dates | 5.2 | |
XNPV | Calculates the net present value for a schedule of cash flows occurring at a series of supplied dates | 5.2 | |
YIELD | Calculates the yield of a security that pays periodic interest | 5.2 | |
YIELDMAT | Returns the annual yield of a security that pays interest at maturity. | 5.5 |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
ERROR.TYPE | Tests a supplied value and returns an integer relating to the supplied value's error type | 4 | |
ISBLANK | Tests if a supplied cell is blank (empty), and if so, returns TRUE; Otherwise, returns FALSE | 4 | |
ISERR | Tests if an initial supplied value (or expression) returns an error (EXCEPT for the #N/A error) and if so, returns TRUE; Otherwise returns FALSE | 4 | |
ISERROR | Tests if an initial supplied value (or expression) returns an error and if so, returns TRUE; Otherwise returns FALSE | 4 | |
ISEVEN | Tests if a supplied number (or expression) is an even number, and if so, returns TRUE; Otherwise, returns FALSE. | 4 | |
ISLOGICAL | Tests if a supplied value is a logical value, and if so, returns TRUE; Otherwise, returns FALSE | 4 | |
ISNA | Tests if an initial supplied value (or expression) returns the Excel #N/A error and if so, returns TRUE; Otherwise returns FALSE | 4 | |
ISNONTEXT | Tests if an initial supplied value (or expression) returns the Excel #N/A error and if so, returns TRUE; Otherwise returns FALSE | 4 | |
ISNUMBER | Tests if a supplied value is a number, and if so, returns TRUE; Otherwise, returns FALSE. | 4 | |
ISODD | Tests if a supplied number (or expression) is an odd number, and if so, returns TRUE; Otherwise, returns FALSE. | 4 | |
ISREF | Tests if the supplied value is a reference, and if so, returns TRUE; Otherwise, returns FALSE | 7 | |
ISTEXT | Tests if a supplied value is text, and if so, returns TRUE; Otherwise, returns FALSE | 4 | |
N | Converts a non-number value to a number, a date to a serial number, the logical value TRUE to 1 and all other values to 0 | 4 | |
NA | Returns the Excel #N/A error | 4 | |
SHEET | Returns the sheet number relating to a supplied reference | 5.5 | _xlfn. |
TYPE | Returns information about the data type of a supplied value | 4 |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
AND | Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise | 4 | |
FALSE | Returns the logical value FALSE | 4 | |
IF | Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE | 4 | |
IFERROR | Tests if an initial supplied value (or expression) returns an error, and if so, returns a supplied value; Otherwise the function returns the initial value. | 4 | |
IFNA | Tests if an expression returns the #N/A error and if so, returns an alternative specified value; Otherwise the function returns the value of the supplied expression. | 4 | _xlfn. |
IFS | Returns the largest numeric value that meets one or more criteria in a range of values | 5.0 | _xlfn. |
LET | Assigns names to calculation results, allowing storing intermediate calculations, values, or defining names inside a formula | 7.2 | _xlfn. |
MAXIFS | Returns the largest numeric value that meets one or more criteria in a range of values. | 5.3 | _xlfn. |
MINIFS | Returns the smallest numeric value that meets one or more criteria in a range of values. | 5.3 | _xlfn. |
NOT | Returns a logical value that is the opposite of a user supplied logical value or expression (i.e.returns FALSE is the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE) | 4 | |
OR | Returns the logical value FALSE | 4 | |
SWITCH | Compares a number of supplied values to a supplied test expression and returns a result corresponding to the first value that matches the test expression. | 5.0 | _xlfn. |
TRUE | Returns the logical value TRUE | 4 | |
XOR | Returns a logical Exclusive Or of all arguments | 5.5 | _xlfn. |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
ADDRESS | Returns a reference, in text format, for a supplied row and column number | 4 | |
ANCHORARRAY | Returns the range of the dynamic array starting at the cell-address supplied | 7 | _xlfn. |
CHOOSE | Returns one of a list of values, depending on the value of a supplied index number | 4 | |
CHOOSECOLS | Returns the specified columns from an array. | 7 | _xlfn. |
CHOOSEROWS | Returns the specified rows from an array. | 7 | _xlfn. |
COLUMN | Returns the column number of a supplied range, or of the current cell | 4 | |
COLUMNS | Returns the number of columns in a supplied range | 4 | |
DROP | Excludes a specified number of rows or columns from the start or end of an array | 7 | _xlfn. |
EXPAND | Returns the array in a single column. | 7 | _xlfn. |
FILTER | Allows filtering of a range or array data based on criteria. | 7 | _xlfn._xlws. |
GETPIVOTDATA | Returns the value of a pivot table data field. | 7.2 | |
HLOOKUP | Looks up a supplied value in the first row of a table, and returns the corresponding value from another row | 4 | |
HSTACK | Combines arrays horizontally into a single array. | 7 | _xlfn. |
INDEX | Returns a reference to a cell (or range of cells) for requested rows and columns within a supplied range | 4 | |
INDIRECT | Returns a cell or range reference that is represented by a supplied text string | 4 | |
LOOKUP | Searches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector | 4 | |
MATCH | Finds the relative position of a value in a supplied array | 4 | |
OFFSET | Returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range | 4 | |
ROW | Returns the row number of a supplied range, or of the current cell | 4 | |
ROWS | Returns the number of rows in a supplied range | 4 | |
SINGLE | Returns a value using implicit intersection | 7 | _xlfn. |
SORT | Sorts the contents of a range or array in ascending or descending order and returns a dynamic array. | 7 | _xlfn._xlws. |
SORTBY | Sorts the contents of a range or array based on the values in a corresponding range or array. | 7 | _xlfn. |
TAKE | Returns a specified number of contiguous rows or columns from the start or end of an array. | 7 | _xlfn. |
TOCOL | Returns the array in a single column. | 7 | _xlfn. |
TOROW | Returns the array in a single row. | 7 | _xlfn. |
TRANSPOSE | Converts a vertical range/array to a horizontal or vice versa. | 7 | |
UNIQUE | Returns a list of unique values in a list or range | 7 | _xlfn. |
VLOOKUP | Looks up a supplied value in the first column of a table, and returns the corresponding value from another column | 4 | |
VSTACK | Combines arrays vertically into a single array. | 7 | _xlfn. |
XLOOKUP | Searches a range or an array, and then returns the item corresponding to the first match it finds. | 7 | _xlfn. |
XMATCH | Looks up a Searches for a specified item in an array or range of cells, and returns the item's relative position. | 7 | _xlfn. |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
ABS | Returns the absolute value (i.e. the modulus) of a supplied number | 4 | |
ACOS | Returns the Arccosine of a number | 4 | |
ACOSH | Returns the inverse hyperbolic cosine of a number | 4 | |
ACOT | Returns the inverse hyperbolic cosine of a number | 5.1 | _xlfn. |
ACOTH | Returns the hyperbolic arccotangent of a number | 5.1 | _xlfn. |
ARABIC | Returns an integer depicting its Roman equivalence. | 7.0 | _xlfn. |
ASIN | Returns the Arcsine of a number | 5.1 | |
ASINH | Returns the Inverse Hyperbolic Sine of a number | 4 | |
ATAN | Returns the Inverse Hyperbolic Sine of a number | 4 | |
ATAN2 | Returns the Arctangent of a given pair of x and y coordinates | 4 | |
ATANH | Returns the Arctangent of a given pair of x and y coordinates | 4 | |
CEILING | Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to a multiple of significance | 4 | |
CEILING.MATH | Rounds a number up to the nearest integer or to the nearest multiple of significance | 5.1 | _xlfn. |
CEILING.PRECISE | Rounds a number up, regardless of the sign of the number, to a multiple of significance | 5.1 | _xlfn. |
COMBIN | Returns the number of combinations (without repititions) for a given number of objects | 5.1 | |
COMBINA | Returns the number of combinations (with repetitions) for a given number of items | 5.1 | _xlfn. |
COS | Returns the Cosine of a given angle | 4 | |
COSH | Returns the hyperbolic cosine of a number | 4 | |
COT | Returns the cotangent of an angle | 5.1 | _xlfn. |
COTH | Returns the hyperbolic cotangent of an angle | 5.1 | _xlfn. |
CSC | Returns the cosecant of an angle | 5.1 | _xlfn. |
CSCH | Returns the hyperbolic cosecant of an angle | 5.1 | _xlfn. |
DEGREES | Converts Radians to Degrees | 4 | |
EVEN | Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to the next even number | 5.0 | |
EXP | Returns e raised to a given power | 4 | |
FACT | Returns the Factorial of a given number | 4 | |
FACTDOUBLE | Returns the Double Factorial of a given number | 5.1 | |
FLOOR | Rounds a number towards zero, (i.e. rounds a positive number down and a negative number up), to a multiple of significance | 4 | |
FLOOR.MATH | Rounds a number down, to the nearest integer or to the nearest multiple of significance | 5.1 | _xlfn. |
FLOOR.PRECISE | Rounds a number down, regardless of the sign of the number, to a multiple of significance | 5.1 | _xlfn. |
GCD | Returns the Greatest Common Divisor of two or more supplied numbers | 5.1 | |
INT | Rounds a number down to the next integer | 4 | |
ISO.CEILING | Rounds a number up, regardless of the sign of the number, to a multiple of significance. | 5.1 | |
LN | Returns the natural logarithm of a given number | 4 | |
LOG | Returns the logarithm of a given number, to a specified base | 4 | |
LOG10 | Returns the base 10 logarithm of a given number | 4 | |
MDETERM | Get the determinant of Matrix | 7.2 | |
MINVERSE | Get the inverse of Matrix | 7.2 | |
MMULT | Multiply to matrixes | 7.2 | |
MOD | Returns the remainder from a division between two supplied numbers | 4 | |
MROUND | Rounds a number up or down, to the nearest multiple of significance | 5.1 | |
MULTINOMIAL | Returns the ratio of the factorial of a sum of values to the product of factorials. | 5.5 | |
MUNIT | Get the inverse of Matrix | 7.2 | _xlfn. |
ODD | Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to the next odd number | 5.0 | |
PI | Returns the constant value of pi | 4 | |
POWER | Returns the result of a given number raised to a supplied power | 4 | |
PRODUCT | Returns the product of a supplied list of numbers | 4 | |
QUOTIENT | Returns the integer portion of a division between two supplied numbers | 4 | |
RADIANS | Converts Degrees to Radians | 5.1 | |
RAND | Returns a random number between 0 and 1 | 4 | |
RANDARRAY | Returns an array of random numbers | 7 | _xlfn. |
RANDBETWEEN | Returns a random number between two given integers | 4 | |
ROMAN | Returns a text string depicting the roman numeral for a given number | 5.1 | |
ROUND | Rounds a number up or down, to a given number of digits | 4 | |
ROUNDDOWN | Rounds a number towards zero, (i.e. rounds a positive number down and a negative number up), to a given number of digits | 4 | |
ROUNDUP | Rounds a number away from zero (i.e. rounds a positive number up and a negative number down), to a given number of digits | 4 | |
SEC | Returns the secant of an angle | 5.1 | _xlfn. |
SECH | Returns the hyperbolic secant of an angle | 5.1 | _xlfn. |
SEQUENCE | Returns an array with a sequence of numbers | 7 | _xlfn. |
SERIESSUM | Returns the sum of a power series | 5.1 | |
SIGN | Returns the sign (+1, -1 or 0) of a supplied number | 4 | |
SIN | Returns the Sine of a given angle | 4 | |
SINH | Returns the Hyperbolic Sine of a number | 4 | |
SQRT | Returns the positive square root of a given number | 4 | |
SQRTPI | Returns the square root of a supplied number multiplied by pi | 4 | |
SUBTOTAL | Performs a specified calculation (e.g. the sum, product, average, etc.) for a supplied set of values | 4 | |
SUM | Returns the sum of a supplied list of numbers | 4 | |
SUMIF | Adds the cells in a supplied range, that satisfy a given criteria | 4 | |
SUMIFS | Adds the cells in a supplied range, that satisfy multiple criteria | 4 | |
SUMPRODUCT | Returns the sum of the products of corresponding values in two or more supplied arrays | 4 | |
SUMSQ | Returns the sum of the squares of a supplied list of numbers | 4 | |
SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two supplied arrays | 5.1 | |
SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two supplied arrays | 5.1 | |
SUMXMY2 | Returns the sum of squares of differences of corresponding values in two supplied arrays | 5.1 | |
TAN | Returns the Tangent of a given angle | 4 | |
TANH | Returns the Hyperbolic Tangent of a given number | 4 | |
TRUNC | Truncates a number towards zero (i.e. rounds a positive number down and a negative number up), to the next integer. | 4 |
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
AGGREGATE | Performs a specified calculation (e.g. the sum, product, average, etc.) for a list or database, with the option to ignore hidden rows and error values | 5.5 | _xlfn. |
AVEDEV | Returns the average of the absolute deviations of data points from their mean | 5.5 | |
AVERAGE | Returns the Average of a list of supplied numbers | 4 | |
AVERAGEA | Returns the Average of a list of supplied numbers, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 | 4 | |
AVERAGEIF | Calculates the Average of the cells in a supplied range, that satisfy a given criteria | 4 | |
AVERAGEIFS | Calculates the Average of the cells in a supplied range, that satisfy multiple criteria | 4 | |
BETA.DIST | Calculates the cumulative beta probability density function | 6.0 | _xlfn. |
BETA.INV | Calculates the inverse of the beta cumulative probability density function | 6.0 | _xlfn. |
BETADIST | Calculates the cumulative beta probability density function | 6.0 | _xlfn. |
BETAINV | Calculates the inverse of the beta cumulative probability density function | 6.0 | _xlfn. |
BINOM.DIST | Returns the individual term binomial distribution probability | 7.0 | _xlfn. |
BINOM.DIST.RANGE | Returns the probability of a trial result using a binomial distribution. | 7.0 | _xlfn. |
BINOM.INV | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. | 7.0 | _xlfn. |
BINOMDIST | Returns the individual term binomial distribution probability. | 7.0 | _xlfn. |
CHIDIST | Calculates the right-tailed probability of the Chi-Square Distribution. Same implementation as CHISQ.DIST.RT | 6.0 | |
CHIINV | Calculates the inverse of the right-tailed probability of the Chi-Square Distribution. Same implementation as CHISQ.INV.RT | 6.0 | |
CHISQ.DIST | Returns the chi-squared distribution. | 7.0 | _xlfn. |
CHISQ.DIST.RT | Calculates the right-tailed probability of the Chi-Square Distribution | 6.0 | _xlfn. |
CHISQ.INV | Calculates the inverse of the left-tailed probability of the Chi-Square Distribution. | 6.0 | _xlfn. |
CHISQ.INV.RT | Calculates the inverse of the right-tailed probability of the Chi-Square Distribution. | 6.0 | _xlfn. |
CHISQ.TEST | Returns test for independence with help of chi-square statistic and appropriate degrees of freedom. | 7.0 | _xlfn. |
CHITEST | Returns test for independence with help of chi-square statistic and appropriate degrees of freedom. | 7.2.1 | _xlfn. |
CONFIDENCE | Returns the confidence interval for a population mean, using a normal distribution | 5.5 | _xlfn. |
CONFIDENCE.NORM | Returns the confidence interval for a population mean, using a normal distribution | 5.5 | _xlfn. |
CONFIDENCE.T | Returns the confidence interval for a population mean, using a Student's t distribution | 5.5 | _xlfn. |
CORREL | Returns the correlation coefficient of two cell ranges | 6.0 | |
COUNT | Returns the number of numerical values in a supplied set of cells or values | 4 | |
COUNTA | Returns the number of non-blanks in a supplied set of cells or values | 4 | |
COUNTBLANK | Returns the number of blank cells in a supplied range | 4 | |
COUNTIF | Returns the number of cells (of a supplied range), that satisfy a given criteria | 4 | |
COUNTIFS | Returns the number of cells (of a supplied range), that satisfy a set of given criteria | 4 | |
COVAR | Returns covariance, the average of the products of deviations for each data point pair in two data sets. | 5.5 | |
COVARIANCE.P | Returns covariance, the average of the products of deviations for each data point pair in two data sets. | 5.5 | _xlfn. |
COVARIANCE.S | Returns covariance, the average of the products of deviations for each data point pair in two data sets. | 5.5 | _xlfn. |
DEVSQ | Returns the sum of the squares of the deviations of a set of data points from their sample mean | 5.5 | |
EXPON.DIST | Returns the value of the exponential distribution for a give value of x. | 6.0 | _xlfn. |
EXPONDIST | Returns the value of the exponential distribution for a give value of x. Same implementation as EXPON.DIST | 6.0 | _xlfn. |
F.DIST | Calculates the F probability distribution. Takes a boolean argument that determines if PDF or CDF is used. | 7.0 | _xlfn. |
F.INV | Returns the inverse of the F probability distribution. If p = F.DIST(x,...), then F.INV(p,...) = x. The F distribution can be used in an F-test that compares the degree of variability in two data sets. For example, you can analyze income distributions in the United States and Canada to determine whether the two countries have a similar degree of income diversity. | 7.0 | _xlfn. |
F.INV.RT | Returns the inverse of the (right-tailed) F probability distribution. If p = F.DIST.RT(x,...), then F.INV.RT(p,...) = x. The F distribution can be used in an F-test that compares the degree of variability in two data sets. For example, you can analyze income distributions in the United States and Canada to determine whether the two countries have a similar degree of income diversity. | 7.0 | _xlfn. |
F.TEST | Calculates the result of the F-test | 7.0 | _xlfn. |
FDIST | Calculates the F probability distribution. Takes a boolean argument that determines if PDF or CDF is used. | 7.0 | _xlfn. |
FISHER | Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed. | 6.0 | |
FISHERINV | Returns the inverse of the Fisher transformation | 6.0 | |
FORECAST | Calculate, or predict, a future value by using existing values. The future value is a y-value for a given x-value. | 6.0 | _xlfn. |
FORECAST.LINEAR | Calculate, or predict, a future value by using existing values. The future value is a y-value for a given x-value. | 6.0 | _xlfn. |
FREQUENCY | Calculates how often values occur within a range of values, and then returns a vertical array of numbers | 7.0 | |
FTEST | Calculates the result of the F-test | 7.0 | |
GAMMA | Return the gamma function value for a supplied number | 5.5 | _xlfn. |
GAMMA.DIST | Returns the gamma distribution. | 7.0 | _xlfn. |
GAMMA.INV | Returns the individual term binomial distribution probability. | 7.2.1 | _xlfn. |
GAMMADIST | Returns the gamma distribution. | 7.0 | |
GAMMAINV | Returns the individual term binomial distribution probability. | 7.2.1 | |
GAMMALN | Calculates the natural logarithm of the gamma function for a supplied value | 5.5 | _xlfn. |
GAMMALN.PRECISE | Calculates the natural logarithm of the gamma function for a supplied value | 5.5 | _xlfn. |
GAUSS | Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean. | 6.0 | _xlfn. |
GEOMEAN | Returns the geometric mean of an array or range of positive data. | 6.0 | |
GROWTH | Returns the y-values along an exponential curve that best fits the inputted data. If new_x's is given, it returns the y-valuesalong those x-values. Growth can also find best fitting curve for a model with multiple predictor variables. | 7.3 | |
HARMEAN | Returns the harmonic mean of a data set. | 6.0 | |
INTERCEPT | Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. | 6.0 | |
KURT | Calculates the kurtosis of a data set | 6.0 | |
LARGE | Returns the Kth LARGEST value from a list of supplied numbers, for a given value K | 4 | |
LCM | Returns the Least Common Multiple of two or more supplied numbers | 5.1 | |
LINEST | The LINEST function calculates a regressional line that fits your data. It also calculates additional statistics.It can handle several x-variables and perform multiple regression analysis. | 7.2 | |
LOGEST | The LOGEST function calculates an exponential curve that best fits the input data. It can also provide multiple curves if there are multiple x-variables. | 7.2 | |
LOGINV | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev. If p = LOGNORMDIST(x,...) then LOGINV(p,...) = x. |
Use the lognormal distribution to analyze logarithmically transformed data.|7.2.1|_xlfn. |LOGNORM.DIST|Returns the lognormal distribution of x|7.0|_xlfn. |LOGNORM.INV|Returns the inverse of the lognormal cumulative distribution function|7.0|_xlfn. |MAX|Returns the largest value from a list of supplied numbers|4| |MAXA|Returns the largest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1|4| |MEDIAN|Returns the largest value from a list of supplied numbers|4| |MIN|Returns the smallest value from a list of supplied numbers|4| |MINA|Returns the smallest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1|4| |MODE|Returns the Mode (the most frequently occurring value) of a list of supplied numbers |5.2| |MODE.MULT|Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data.|7|_xlfn. |MODE.SNGL|Returns the Mode (the most frequently occurring value) of a list of supplied numbers |5.2|_xlfn. |NEGBINOM.DIST|Returns the negative binomial distribution|7.0|_xlfn. |NEGBINOMDIST|Returns the negative binomial distribution|7.0|_xlfn. |NORM.DIST|Calculates the Normal Probability Density Function or the Cumulative Normal Distribution. Function for a supplied set of parameters.|5.8|_xlfn. |NORM.INV|Calculates the inverse of the Cumulative Normal Distribution Function for a supplied value of x, and a supplied distribution mean & standard deviation. Note that this is the same implementation as NORMINV.|5.8|_xlfn. |NORM.S.DIST|Calculates the inverse of the Cumulative Normal Distribution Function for a supplied value of x, and a supplied distribution mean & standard deviation. Note that this is the same implementation as NORMINV.|6.0|_xlfn. |NORM.S.INV|Calculates the inverse of the Cumulative Normal Distribution Function for a supplied value of x, and a supplied distribution mean & standard deviation.|5.8|_xlfn. |NORMDIST|Calculates the Normal Probability Density Function or the Cumulative Normal Distribution. Function for a supplied set of parameters.|5.8|_xlfn. |NORMINV|Calculates the inverse of the Cumulative Normal Distribution Function for a supplied value of x, and a supplied distribution mean & standard deviation.|5.8|_xlfn. |NORMSDIST|Calculates the Standard Normal Cumulative Distribution Function for a supplied value.|6.0| |NORMSINV|Calculates the inverse of the Cumulative Normal Distribution Function for a supplied value of x, and a supplied distribution mean & standard deviation.|5.8| |PEARSON|Returns the Pearson product moment correlation coefficient.|6.0| |PERCENTILE|Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (inclusive)|5.2| |PERCENTILE.EXC|Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (exclusive)|5.5|_xlfn. |PERCENTILE.INC|Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (inclusive)|5.2|_xlfn. |PERCENTRANK|The Excel Percentrank function calculates the relative position, between 0 and 1 (inclusive), of a specified value within a supplied array.|5.2| |PERCENTRANK.EXC|Returns the rank of a value in a data set, as a percentage (0 - 1 exclusive)|5.5|_xlfn. |PERCENTRANK.INC|The Excel Percentrank.Inc function calculates the relative position, between 0 and 1 (inclusive), of a specified value within a supplied array.|5.2|_xlfn. |PERMUT|Returns the number of permutations for a given number of objects|5.5| |PERMUTATIONA|Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects|5.5|_xlfn. |PHI|Calculates the value of the density function for a standard normal distribution for a supplied number.|6.0|_xlfn. |POISSON|Returns the Poisson distribution. This function works the same as POISSON.DIST|7.0| |POISSON.DIST|Returns the Poisson distribution. This function works the same as POISSON|7.0|_xlfn. |PROB|Returns the probability that values in a range are between two limits|7.0|_xlfn. |QUARTILE|Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (inclusive)|5.5| |QUARTILE.EXC|Returns the specified quartile of a set of supplied numbers, based on percentile value 0 - 1 (exclusive) |5.5|_xlfn. |QUARTILE.INC|Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (inclusive)|5.5|_xlfn. |RANK|Returns the statistical rank of a given value, within a supplied array of values|4| |RANK.AVG|Returns the statistical rank of a given value, within a supplied array of values (if more than one value has same rank, the average rank is returned) |4|_xlfn. |RANK.EQ|Returns the Mode (the most frequently occurring value) of a list of supplied numbers (if more than one value has same rank, the top rank of that set is returned)|4|_xlfn. |RSQ|Returns the geometric mean of an array or range of positive data.|6.0| |SKEW|Returns the skewness of a distribution|6.0| |SKEW.P|Calculates the skewness of a distribution based on a population|6.0|_xlfn. |SLOPE|Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.|7.0| |SMALL|Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K|4| |STANDARDIZE|Returns a normalized value from a distribution characterized by mean and standard_dev.|6.0| |STDEV|Returns the standard deviation of a supplied set of values (which represent a sample of a population) |4| |STDEV.P|Returns the standard deviation of a supplied set of values (which represent an entire population)|4|_xlfn. |STDEV.S|Returns the standard deviation of a supplied set of values (which represent a sample of a population) |4|_xlfn. |STDEVA|Returns the standard deviation of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1|5.5| |STDEVP|Returns the standard deviation of a supplied set of values (which represent an entire population)|4|_xlfn. |STDEVPA|Returns the standard deviation of a supplied set of values (which represent an entire population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1|5.5| |STEYX|Returns the standard error for each predicted y-value for each x-value in the regression.|7.0| |T.DIST|Returns the left-tailed Students t-distribution. The Students t-distribution is used for hypothesis testing with small samples.|7.0|_xlfn. |T.DIST.2T|Returns the two-tailed Students t-distribution. The Students t-distribution is used for hypothesis testing with small samples.|7.0|_xlfn. |T.DIST.RT|Returns the right-tailed Students t-distribution. The Students t-distribution is used for hypothesis testing with small samples.|7.0|_xlfn. |T.INV|Returns left tailed inverse of Students T-distribution|7.0|_xlfn. |T.INV.2T|Returns two tailed inverse of Students T-distribution|7.0|_xlfn. |T.TEST|Returns the probability for the Student's t-test. Can handle three types of t-tests: Paired, homoscedastic and heteroscedastic.|7.0|_xlfn. |TDIST|Returns the left-tailed Students t-distribution. The Students t-distribution is used for hypothesis testing with small samples.|7.2.1|_xlfn. |TREND|Returns the y-values along a linear trend that fits the inputted data. If new_x's is given, it returns the y-valuesalong those x-values. Trend can also find the trend values for a model with multiple predictor variables.|7.2| |TRIMMEAN|Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis.|7.0| |TTEST|Returns the probability for the Student's t-test. Can handle three types of t-tests: Paired, homoscedastic and heteroscedastic.|7.0|_xlfn. |VAR|Returns the variance of a supplied set of values (which represent a sample of a population) |4| |VAR.P|Returns the variance of a supplied set of values (which represent an entire population) |5.2|_xlfn. |VAR.S|Returns the variance of a supplied set of values (which represent a sample of a population) |5.2|_xlfn. |VARA|Returns the variance of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1|5.5| |VARP|Returns the variance of a supplied set of values (which represent an entire population) |4| |VARPA|Returns the variance of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1|5.5| |WEIBULL|Returns the Weibull distribution. This function works the same as WEIBULL.|7.0| |WEIBULL.DIST|Returns the Weibull distribution. This function works the same as WEIBULL.|7.0|_xlfn. |Z.TEST|Returns the one-tailed P-value of a z-test.|7.0|_xlfn. |ZTEST|Returns the one-tailed P-value of a z-test.|7.0|
Function | Description | From EPPlus version | Prefix |
---|---|---|---|
CHAR | Returns the character that corresponds to a supplied numeric value | 4 | |
CLEAN | Removes all non-printable characters from a supplied text string | 5.0 | |
CONCAT | Joins together two or more text strings | 5.0 | _xlfn. |
CONCATENATE | Joins together two or more text strings | 4 | |
DOLLAR | Converts a supplied number into text, using a currency format | 5.5 | |
EXACT | Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive) | 4 | |
FIND | Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive) | 4 | |
FIXED | Rounds a supplied number to a specified number of decimal places, and then converts this into text | 4 | |
HYPERLINK | Creates a hyperlink to a document in a supplied location. | 4 | |
LEFT | Returns a specified number of characters from the start of a supplied text string | 4 | |
LEN | Returns the length of a supplied text string | 4 | |
LOWER | Converts all characters in a supplied text string to lower case | 4 | |
MID | Returns a specified number of characters from the middle of a supplied text string | 4 | |
NUMBERVALUE | Converts text to a number, in a locale-independent way | 5.0 | _xlfn. |
PROPER | Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case) | 4 | |
REPLACE | Replaces all or part of a text string with another string (from a user supplied position) | 4 | |
REPT | Returns a string consisting of a supplied text string, repeated a specified number of times | 4 | |
RIGHT | Returns a specified number of characters from the end of a supplied text string | 4 | |
SEARCH | Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive) | 4 | |
SUBSTITUTE | Substitutes all occurrences of a search text string, within an original text string, with the supplied replacement text | 4 | |
T | Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string. | 4 | |
TEXT | Converts a supplied value into text, using a user-specified format | 4 | |
TEXTAFTER | Get the text After delimiter | 7.2 | _xlfn. |
TEXTBEFORE | Get the text before delimiter | 7.2 | _xlfn. |
TEXTJOIN | Joins together two or more text strings, separated by a delimiter | 5.2 | _xlfn. |
TEXTSPLIT | Splits a string into substrings | 7.2 | _xlfn. |
TRIM | Removes duplicate spaces, and spaces at the start and end of a text string | 4 | |
UNICHAR | Returns the Unicode character that is referenced by the given numeric value | 5.0 | _xlfn. |
UNICODE | Returns the number (code point) corresponding to the first character of a supplied text string | 5.0 | _xlfn. |
UPPER | Converts all characters in a supplied text string to upper case | 4 | |
VALUE | Converts a text string into a numeric value | 4 |
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles