# Formulas in WPF Spreadsheet (SfSpreadsheet)

23 Sep 2020 / 21 minutes to read

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 |