# Formulas

SfSpreadsheet calculation engine offers automated calculation over a formula, expression, or cross sheet references. SfSpreadsheet calculation engine is preloaded with 409 formulas covering a broad range of business functions.

## Adding Formula into cell

To add formulas into a cell programmatically, use `SetCellValue`

method of `SpreadsheetGrid`

should be invoked and then invalidate that cell to update the view.

```
var range = spreadsheet.ActiveSheet.Range["A2"];
spreadsheet.ActiveGrid.SetCellValue(range, "=SUM(B1:B2)");
spreadsheet.ActiveGrid.InvalidateCell(2,1);
```

## Named Ranges

Named Ranges are the defined names that represents a cell, range of cells, formula, or constant value or table. Each name have a scope of either to a specific worksheet or to the entire workbook.

### Define named ranges at runtime

SfSpreadsheet allows the user to define/add the named ranges at runtime by using `AddNamedRange`

method.

`spreadsheet.AddNamedRange("SampleName", "A3:B3", "Sheet1");`

### Edit or remove named ranges at runtime

SfSpreadsheet allows the user to edit the named ranges at runtime by `EditNamedRange`

method and remove the named ranges at runtime by `DeleteNamedRange`

method

```
//To Edit the named ranges,
IName name = spreadsheet.Workbook.Names["Sample"];
spreadsheet.EditNamedRange("Test", "A3:B3", name);
//To remove the named ranges,
IName name = spreadsheet.Workbook.Names["Sample"];
spreadsheet.DeleteNamedRange(name);
```

## Supported functions

Following is a list of functions that are supported by SfSpreadsheet

### Database Functions

Name |
Description |
---|---|

DCOUNT |
Returns the number of cells containing numbers in a field of a list or database that satisfy specified conditions |

DCOUNTA |
Returns the number of non-blank cells in a field of a list or database, that satisfy specified conditions |

DAVERAGE |
Calculates the average of values in a field of a list or database, that satisfy specified conditions |

DGET |
Returns a single value from a field of a list or database, that satisfy specified conditions |

DMAX |
Returns the maximum value from a field of a list or database, that satisfy specified conditions |

DMIN |
Returns the minimum value from a field of a list or database, that satisfy specified conditions |

DSTDEVP |
Calculates the standard deviation (based on an entire population) of values in a field of a list or database, that satisfy specified conditions |

DSTEV |
Calculates the standard deviation (based on a sample of a population) of values in a field of a list or database, that satisfy specified conditions |

DVARP |
Calculates the variance (based on an entire population) of values in a field of a list or database, that satisfy specified conditions |

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 |

### Date and Time Functions

Name |
Description |
---|---|

DATE |
Returns a date, from a user-supplied year, month and day |

DATEVALUE |
Converts a text string showing a date, to an integer that represents the date in Excel's date-time code |

DAY |
Returns the day (of the month) from a user-supplied date |

DAYS360 |
Calculates the number of days between 2 dates, based on a 360-day year (12 x 30 months) |

HOUR |
Returns the hour part of a user-supplied time |

MINUTE |
Returns the minute part of a user-supplied time |

SECOND |
Returns the seconds part of a user-supplied time |

MONTH |
Returns the month from a user-supplied date |

NOW |
Returns the current date & time |

TIME |
Returns a time, from a user-supplied hour, minute and second |

TIMEVALUE |
Converts a text string showing a time, to a decimal that represents the time in Excel |

TODAY |
Returns today's date |

WEEKDAY |
Returns an integer representing the day of the week for a supplied date |

YEAR |
Returns the year from a user-supplied date |

DAYS |
Calculates the number of days between 2 dates |

EDATE |
Returns a date that is the specified number of months before or after an initial supplied start date |

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 |

ISOWEEKNUM |
Returns the ISO week number of the year for a given date |

NETWORKDAYS.INTL |
Returns the number of whole network days (excluding weekends & holidays), between two supplied dates, using parameters to specify weekend days |

WEEKNUM |
Returns an integer representing the week number (from 1 to 53) of the year from a user-supplied date |

WORKDAY |
Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date |

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 |

YEARFRAC |
Calculates the fraction of the year represented by the number of whole days between two dates |

### Engineering Functions

Name |
Description |
---|---|

DEC2BIN |
Converts a decimal number to binary |

DCE2OCT |
Converts a binary number to octal |

DEC2HEX |
Converts a decimal number to hexadecimal |

BIN2DEC |
Converts a binary number to hexadecimal |

BIN2OCT |
Converts a binary number to octal |

BIN2HEX |
Converts a binary number to hexadecimal |

HEX2BIN |
Converts a hexadecimal number to binary |

HEX2DEC |
Converts a hexadecimal number to a decimal |

HEX2OCT |
Converts a hexadecimal number to octal |

OCT2BIN |
Converts octal number to binary |

OCT2DEC |
Converts octal number to a decimal |

OCT2HEX |
Converts octal number to hexadecimal |

IMABS |
Returns the absolute value (the modulus) of a complex number |

IMAGINARY |
Returns the imaginary coefficient of a complex number |

IMREAL |
Returns the real coefficient of a complex number |

COMPLEX |
Converts user-supplied real and imaginary coefficients into a complex number |

IMSUM |
Calculates the sum of two complex numbers |

IMSUB |
Subtracts two complex numbers |

IMPRODUCT |
Returns the product of up to 255 supplied complex numbers |

IMDIV |
Returns the quotient of two supplied complex numbers |

IMCONJUGATE |
Returns the complex conjugate of a complex number |

IMSQRT |
Returns the square root of a complex number |

IMARGUMENT |
Returns the argument Θ (an angle expressed in radians) of a complex number |

IMSIN |
Returns the sine of a complex number |

IMCSC |
Returns the cosecant of a complex number |

IMCOS |
Returns the cosine of a complex number |

IMSEC |
Returns the secant of a complex number |

IMTAN |
Returns the tangent of a complex number |

IMCOT |
Returns the cotangent of a complex number |

IMSINH |
Returns the hyperbolic sine of a complex number |

IMCSCH |
Returns the hyperbolic cosecant of a complex number |

IMCOSH |
Returns the hyperbolic cosine of a complex number |

IMSECH |
Returns the hyperbolic secant of a complex number |

IMLOG10 |
Returns the base-10 logarithm of a complex number |

IMLOG2 |
Returns the base-2 logarithm of a complex number |

IMLN |
Returns the natural logarithm of a complex number |

IMEXP |
Returns the exponential of a complex number |

IMPOWER |
Calculates a complex number raised to a supplied power |

GESTEP |
Tests whether a number is greater than a supplied threshold value |

DELTA |
Tests whether two supplied numbers are equal |

BITAND |
Returns a Bitwise 'And' of two numbers |

BITOR |
Returns a Bitwise 'Or' of two numbers |

BITXOR |
Returns a Bitwise 'Exclusive Or' of two numbers |

BITLSHIFT |
Returns a number shifted left by a specified number of bits |

BITRSHIFT |
Returns a number shifted right by a specified number of bits |

ERF |
Returns the error function integrated between two supplied limits |

ERF.PRECISE |
Returns the error function integrated between 0 and a supplied limit |

ERFC.PRECISE |
Returns the complementary error function integrated between a supplied lower limit and infinity |

BESSELI |
Calculates the modified Bessel function In(x) |

BESSELJ |
Calculates the Bessel function Jn(x) |

BESSELY |
Calculates the modified Bessel function Yn(x) |

BESSELK |
Calculates the modified Bessel function Kn(x) |

CONVERT |
Converts a number from one measurement system to another |

### Financial Functions

Name |
Description |
---|---|

DB |
Calculates the depreciation of an asset for a specified period, using the fixed-declining balance method |

DDB |
Calculates the depreciation of an asset for a specified period, using the double-declining balance method, or some other user-specified method |

FV |
Calculates the future value of an investment with periodic constant payments and a constant interest rate |

IPMT |
Calculates the interest payment for a given period of an investment, with periodic constant payments and a constant interest rate |

IRR |
Calculates the internal rate of return for a series of cash flows |

XIRR |
Calculates the internal rate of return for a schedule of cash flows |

ISPMT |
Returns the interest paid during a specified period of an investment |

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 |

NPER |
Returns the number of periods for an investment with periodic constant payments and a constant interest rate |

NPV |
Calculates the net present value of an investment, based on a supplied discount rate, and a series of future payments and income |

PMT |
Calculates the payments required to reduce a loan, from a supplied present value to a specified future value |

PPMT |
Calculates the payment on the principal for a given investment, with periodic constant payments and a constant interest rate |

PV |
Calculates the present value of an investment (i.e. the total amount that a series of future payments is worth now) |

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 |

SLN |
Returns the straight-line depreciation of an asset for one period |

SYD |
Returns the sum-of-years' digits depreciation of an asset for a specified period |

VDB |
Returns the depreciation of an asset for a specified period, (including partial periods), using the double-declining balance method or another user-specified method |

DOLLARDE |
Converts a dollar price expressed as a fraction, into a dollar price expressed as a decimal |

DOLLARFR |
Converts a dollar price expressed as a decimal, into a dollar price expressed as a fraction |

DURATION |
Calculates the Macaulay duration of a security with an assumed par value of $100 |

RRI |
Calculates an equivalent interest rate for the growth of an investment |

FVSCHEDULE |
Calculates the future value of an initial principal, after applying a series of compound interest rates |

DISC |
Calculates the discount rate for a security |

INTRATE |
Calculates the interest rate for a fully invested security |

CUMIPMT |
Calculates the cumulative interest paid between two specified periods |

CUMPRINC |
Calculates the cumulative principal paid on a loan, between two specified periods |

RECEIVED |
Calculates the amount received at maturity for a fully invested Security |

### Information Functions

Name |
Description |
---|---|

ISERROR |
Checks whether the value is an error and returns true or false |

ISNUMBER |
Checks whether the value is number and returns true or false |

ISLOGICAL |
Checks whether a value is logical value(TRUE/FALSE) and returns true or false |

ISNA |
Checks whether a value is #N/A and returns true or false |

ISERR |
Checks whether the value is an error except #N/A and returns true or false |

ISBLANK |
Checks whether the reference is to an empty cell and returns true or false |

ISTEXT |
Checks whether the value is text and returns true or false |

ISNONTEXT |
Checks whether the value is not text(blank cells are not text) and returns true or false |

ISEVEN |
Returns true if number is even |

CONCATENATE |
Joins together two or more text strings |

DOLLAR |
Converts a number to text using currency format |

LEN |
Returns the length of a supplied text string |

FIXED |
Rounds a supplied number to a specified number of decimal places, and then converts this into text |

ISODD |
Returns true if number is odd |

ERROR.TYPE |
Tests a supplied value and returns an integer relating to the supplied value's error type |

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 |

NA |
Returns the Excel #N/A error |

CELL |
Returns information about the contents, formatting or location of a given cell |

INFO |
Returns information about the the current operating environment |

TYPE |
Returns information about the data type of a supplied value |

ISFORMULA |
Tests if a supplied cell contains a formula and if so, returns TRUE; Otherwise, returns FALSE |

### Logical Functions

Name |
Description |
---|---|

AND |
Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, orFALSE otherwise |

OR |
Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, orFALSE otherwise |

IF |
Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE |

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. |

FALSE |
Simply returns the logical value FALSE |

TRUE |
Simply returns the logical value TRUE |

NOT |
Returns a logical value that is the opposite of a user supplied logical value or expression |

### Lookup & Reference Functions

Name |
Description |
---|---|

OFFSET |
Returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range |

HLOOKUP |
Looks up a supplied value in the first row of a table, and returns the corresponding value from another row |

VLOOKUP |
Looks up a supplied value in the first column of a table, and returns the corresponding value from another column |

MATCH |
Finds the relative position of a value in a supplied array |

COLUMN |
Returns the column number of a supplied range, or of the current cell |

ROW |
Returns the row number of a supplied range, or of the current cell |

INDIRECT |
Returns a cell or range reference that is represented by a supplied text string |

AREAS |
Returns the number of areas in a supplied range |

COLUMNS |
Returns the number of columns in a supplied range |

FORMULATEXT |
Returns a formula as a string |

HYPERLINK |
Creates a hyperlink to a document in a supplied location |

ROW |
Returns the row number of a supplied range, or of the current cell |

ROWS |
Returns the number of rows in a supplied range |

SHEET |
Returns the sheet number of the referenced sheet |

TRANSPOSE |
Performs a transpose transformation on a range of cells (i.e. transforms a horizontal range of cells into a vertical range and vice versa) |

SHEETS |
Returns the number of sheets in reference |

### Math & Trigonometry functions

Name |
Description |
---|---|

ABS |
Returns the absolute value of a number |

ACOS |
Returns the arccosine of a number |

ACOSH |
Returns the inverse hyperbolic cosine of a number |

ASIN |
Returns the arcsine of a number |

ASINH |
Returns the inverse hyperbolic sine of a number |

ATAN |
Returns the arctangent of a number |

ATAN2 |
Returns the arctangent from x- and y-coordinates |

ATANH |
Returns the inverse hyperbolic tangent of a number |

SUM |
Adds its arguments |

PI |
Returns the value of pi |

POWER |
Returns the result of a number raised to a power |

POW |
Returns the result of a number raised to a power |

SUBTOTAL |
Returns a subtotal in a list or database |

COS |
Returns the cosine of a number |

SIN |
Returns the sine of the given angle |

COSH |
Returns the hyperbolic cosine of a number |

SINH |
Returns the hyperbolic sine of a number |

TANH |
Returns the hyperbolic tangent of a number |

TAN |
Returns the tangent of a number |

ACOT |
Returns the arc cotangent of a number, in radians in the range 0 to Pi |

ACOTH |
Returns the inverse hyperbolic cotangent of a number |

SIGN |
Returns the sign of a number |

SQRT |
Returns a positive square root |

ROUND |
Rounds a number to a specified number of digits |

LOG |
Returns the logarithm of a number to a specified base |

LOG10 |
Returns the base-10 logarithm of a number |

EXP |
Returns e raised to the power of a given number |

CEILING |
Rounds a number to the nearest integer or to the nearest multiple of significance |

CEILING.MATH |
Returns the RoundUp of the given number to the given significance |

COLUMNS |
Returns the number of columns of the passed in cell reference |

FLOOR |
Rounds a number down, toward zero |

PRODUCT |
Multiplies its arguments |

MOD |
Returns the remainder from division |

TRUNC |
Truncates a number to an integer |

INT |
Rounds a number down to nearest integer |

ISEVEN |
Returns true if the number is even |

SUMPRODUCT |
Returns the sum of the products of corresponding array components |

EXP |
Returns e raised to the power of a given number |

INT |
Rounds a number down to the nearest integer |

RAND |
Returns an evenly distributed random number >= 0 and < 1 |

COMBIN |
Returns the number of combinations for a given number of objects |

DEGREES |
Converts radians to degrees |

EVEN |
Rounds a number up to the nearest even integer |

FACT |
Returns the factorial of a number |

LN |
Returns the natural logarithm of a number |

ODD |
Rounds a number up to the nearest odd integer |

RADIANS |
Converts degrees to radians |

ROUNDDOWN |
Rounds a number down, toward zero |

ROUNDUP |
Rounds a number up, away from zero |

MROUND |
Returns a number rounded to the desired multiple |

MULTINOMIAL |
Returns the multinomial of a set of numbers |

QUOTIENT |
Returns the integer portion of a division |

FACTDOUBLE |
Returns the double factorial of a number |

GCD |
Returns the greatest common divisor |

LCM |
Returns the least common multiple |

SQRTPI |
Returns the square root of (number * pi) |

ROMAN |
Converts an Arabic numeral to Roman, as text |

SUMSQ |
Returns the sum of the squares of the arguments |

SUMX2MY2 |
Returns the sum of the difference of squares of corresponding values in two arrays |

SUMX2PY2 |
Returns the sum of the sum of squares of corresponding values in two arrays |

SUMXMY2 |
Returns the sum of squares of differences of corresponding values in two arrays |

SUMIFS |
Adds the cells specified by a given set of conditions or criteria |

SEC |
Returns the secant of an angle |

SECH |
Returns the hyperbolic secant of an angle |

COT |
Returns the cotangent of an angle |

COTH |
Returns the hyperbolic cotangent of a number |

CSC |
Returns the cosecant of an angle |

CSCH |
Returns the hyperbolic cosecant of an angle |

TRUNCATE |
Truncates a number to an integer |

COMBINA |
Returns the number of combinations for a given number of objects |

BASE |
Converts number into text representation |

DECIMAL |
Converts text representation of a number in a given base into decimal number |

ARABIC |
Converts a roman numeral to Arabic |

CEILING.MATH |
Rounds a number to the nearest integer or to the nearest multiple of significance |

MDETERM |
Returns the matrix determinant of an array |

MMULT |
Returns the matrix product of two arrays |

MINVERSE |
Returns the matrix inverse of an array |

MUNIT |
Returns the unit matrix for the specified dimension |

### Statistical functions

Name |
Description |
---|---|

AVG |
Returns the average of its arguments |

AVERAGE |
Returns the average of its arguments |

MAX |
Returns the maximum value in a list of arguments |

MIN |
Returns the minimum value in a list of arguments |

MAXA |
Returns the maximum value in a list of arguments, including numbers, text, and logical values |

MINA |
Returns the smallest value in a list of arguments, including numbers, text, and logical values |

MEDIAN |
Returns the median of the given numbers |

CONFIDENCE.T |
Returns the confidence interval for a population mean |

SKEW.P |
Returns the skewness of a distribution |

COVARIANCE.P |
Returns population covariance, the average of the products deviation for each data point pair in two data sets. |

COVARIANCE.S |
Returns the sample covariance, the average of the products deviation for each data point pair in two data sets. |

PERCENTILE.EXC |
Returns the Kth percentile of the values in a range, where K is in the range 0….1 exclusive |

PERCENTILE.INC |
Returns the Kth percentile of the values in a range, where K is in the range 0….1 inclusive |

PERCENTRANK.EXC |
Returns the rank of value in dataset as a percentage of the data set as percentage (0….1, exclusive) of the dataset |

PERCENTRANC.INC |
Returns the rank of value in dataset as a percentage of the data set as percentage (0….1, inclusive) of the dataset |

STDEV.P |
Calculates standard deviation based on the entire population |

STDEV.S |
Estimates standard deviation based on a sample |

PERMUTATIONA |
Returns the number of permutations for a given number of objects |

NORM.DIST |
Returns the normal cumulative distribution |

NORM.INV |
Returns the inverse of the normal cumulative distribution |

NORM.S.DIST |
Returns the standard normal cumulative distribution |

NORM.S.INV |
Returns the inverse of the standard normal cumulative distribution |

WEIBULL.DIST |
Returns the Weibull distribution |

EXPON.DIST |
Returns the exponential distribution |

GAMMA.DIST |
Returns the gamma distribution |

GAMMA.INV |
Returns the inverse of the gamma cumulative distribution |

GAMMALN.PRECISE |
Returns the natural logarithm of the gamma function, Γ(x) |

T.INV |
Returns the left-tailed inverse of the Student’s t-distribution |

F.INV.RT |
Returns the inverse of the right-tailed F probability distribution for two data sets |

BINOM.INV |
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value |

HYPGEOM.DIST |
Returns the hypergeometric distribution |

LOGNORM.DIST |
Returns the cumulative log-normal distribution |

LOGNORM.INV |
Returns the inverse of the lognormal distribution |

CONFIDENCE.NORM |
Returns the confidence interval for a population mean, using a normal distribution |

CHISQ.DIST.RT |
Returns the right-tailed probability of the chi-squared distribution |

F.DIST |
Returns the F probability distribution |

F.DIST.RT |
Returns the right-tailed F probability distribution for two data sets |

CHISQ.TEST |
Returns the chi-squared statistical test for independence |

CHISQ.INV |
Returns the inverse of the left-tailed probability of the chi-squared distribution |

CHISQ.INV.RT |
Returns the inverse of the right-tailed probability of the chi-squared distribution |

BINOM.DIST |
Returns the individual term binomial distribution probability |

Z.TEST |
Returns the one-tailed probability value of a z-test |

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) |

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) |

NEGBINOM.DIST |
Returns the negative binomial distribution |

POISSON.DIST |
Returns the Poisson distribution |

QUARTILE.EXC |
Returns the specified quartile of a set of supplied numbers, based on percentile value 0 - 1 (exclusive) |

QUARTILE.INC |
Returns the specified quartile of a set of supplied numbers, based on percentile value 0 - 1 (inclusive) |

AVEDEV |
Returns the average of the absolute deviations of data points from their mean |

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 |

GAMMALN |
Calculates the natural logarithm of the gamma function for a supplied value |

GAMMADIST |
Returns the gamma distribution |

GAMMAINV |
Returns the inverse gamma cumulative distribution |

GEOMEAN |
Returns the geometric mean of a set of supplied numbers |

HARMEAN |
Returns the harmonic mean of a set of supplied numbers |

HYPGEOMDIST |
Returns the hypergeometric distribution |

INTERCEPT |
Calculates the best fit regression line, through a supplied series of x- and y- values and returns the value at which this line intercepts the y-axis |

BINOMDIST |
Returns the individual term binomial distribution probability |

CHIDIST |
Returns the right-tailed probability of the chi-squared distribution |

CHIINV |
Returns the inverse of the right-tailed probability of the chi-squared distribution |

CHITEST |
Returns the chi-squared statistical test for independence |

NORMDIST |
Returns the normal cumulative distribution |

NORMINV |
Returns the inverse of the normal cumulative distribution |

NORMSINV |
Returns the inverse of the standard normal cumulative distribution |

NORMSDIST |
Returns the standard normal cumulative distribution |

CONFIDENCE |
Returns the confidence interval for a population mean, using a normal distribution |

CORREL |
Returns the correlation coefficient between two sets of values |

COUNT |
Returns the number of numerical values in a supplied set of cells or values |

COUNTA |
Returns the number of non-blanks in a supplied set of cells or values |

COUNTBLANK |
Returns the number of blank cells in a supplied range |

COUNTIF |
Returns the number of cells (of a supplied range), that satisfy a given criteria |

COVAR |
Returns population covariance (i.e. the average of the products of deviations for each pair within two supplied data sets) |

CRITBINOM |
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value |

DEVSQ |
Returns the sum of the squares of the deviations of a set of data points from their sample mean |

EXPONDIST |
Returns the exponential distribution |

FDIST |
Returns the F probability distribution (probability density or cumulative distribution function) |

FINV |
Returns the inverse of the right-tailed F probability distribution for two data sets |

FISHER |
Returns the Fisher transformation |

FISHERINV |
Returns the inverse of the Fisher transformation |

FORECAST |
Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values |

KURT |
Returns the kurtosis of a data set |

LARGE |
Returns the Kth LARGEST value from a list of supplied numbers, for a given value K |

LOGNORMDIST |
Returns the cumulative log-normal distribution |

LOGINV |
Returns the inverse of the lognormal distribution |

MODE |
Returns the Mode (the most frequently occurring value) of a list of supplied numbers |

NEGBINOMDIST |
Returns the negative binomial distribution |

PEARSON |
Returns the Pearson product moment correlation coefficient |

PERCENTILE |
Returns the K'th percentile of values in a supplied range, where K is in the range 0 - 1 (inclusive) |

PERCENTILERANK |
Returns the rank of a value in a data set, as a percentage (0 - 1 inclusive) |

PERMUT |
Returns the number of permutations for a given number of objects |

POISSON |
Returns the Poisson distribution |

PROB |
Returns the probability that values in a supplied range are within given limits |

QUARTILE |
Returns the specified quartile of a set of supplied numbers, based on percentile value 0 - 1 (inclusive) |

RANQ |
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) |

RSQ |
Returns the square of the Pearson product moment correlation coefficient |

SKEW |
Returns the skewness of a distribution |

SLOPE |
Returns the slope of the linear regression line through a supplied series of x- and y- values |

SMALL |
Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K |

STANDARDIZE |
Returns a normalized value |

STDEV |
Returns the standard deviation of a supplied set of values (which represent a sample of a population) |

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 |

STDEVP |
Returns the standard deviation of a supplied set of values (which represent an entire population) |

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 |

STEYX |
Returns the standard error of the predicted y-value for each x in the regression line for a set of supplied x- and y- values |

TRIMMEAN |
Returns the mean of the interior of a supplied set of values |

VAR |
Returns the variance of a supplied set of values (which represent a sample of a population) |

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 |

VARP |
Returns the variance of a supplied set of values (which represent an entire population) |

VARPA |
Returns the variance 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 |

WEIBULL |
Returns the Weibull distribution |

ZTEST |
Returns the one-tailed probability value of a z-test |

### Text Functions

Name |
Description |
---|---|

LEFT |
Returns a specified number of characters from the start of a supplied text string |

LEN |
Returns the length of a supplied text string |

TRUNC |
Truncates a number to an integer removing decimal part or fractional part |

MID |
Returns a specified number of characters from the middle of a supplied text string |

RIGHT |
Returns a specified number of characters from the end of a supplied text string |

VALUE |
Converts a text string into a numeric value |

DOLLAR |
Converts a supplied number into text, using a currency format |

FIXED |
Rounds a supplied number to a specified number of decimal places, and then converts this into text |

LOWER |
Converts all characters in a supplied text string to lower case |

UPPER |
Converts all characters in a supplied text string to upper case |

TEXT |
Converts a supplied value into text, using a user-specified format |

TRIM |
Removes duplicate spaces, and spaces at the start and end of a text string |

CONCATENATE |
Joins together two or more text strings |

SUBSTITUTE |
Substitutes all occurrences of a search text string, within an original text string, with the supplied replacement text |

T |
Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string. |

CODE |
Returns the numeric code for the first character of a supplied string |

FINDB |
Returns the position of a supplied character or text string from within a supplied text string (case-sensitive) |

LEFTB |
Returns a specified number of characters from the start of a supplied text string |

LENB |
Returns the length of a supplied text string |

MINB |
Returns the smallest value in a set of values. does not ignore logical text and values |

RIGHTB |
Returns a specified number of characters from the end of a supplied text string |

NUMBERVALUE |
Converts text to a number, in a locale-independent way |

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) |

REPLACE |
Replaces all or part of a text string with another string (from a user supplied position) |

REPT |
Returns a string consisting of a supplied text string, repeated a specified number of times |

SEARCHB |
Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive) |

UNICHAR |
Returns the Unicode character that is referenced by the given numeric value |

UNICODE |
Returns the number (code point) corresponding to the first character of a supplied text string |

### Web Functions

Name |
Description |
---|---|

ENCODEURL |
Returns a URL-encoded string |

FILTERXML |
Returns data from XML content, using a specified XPath |

WEBSERVICE |
Returns data from a web service on the Internet or Intranet |