Formulas in WPF Spreadsheet (SfSpreadsheet)

23 Jun 202322 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.

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

C#
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

C#
//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);

ExcelLikeComputations

To return the result of formula computations of Essential Calculate like Microsoft Excel computational result, this bool property ExcelLikeComputations is set to true.

C#
VB.NET
//Event subscription
spreadsheet.WorkbookLoaded += OnWorkbookLoaded; 

//Event customization
private void OnWorkbookLoaded(object sender, WorkbookLoadedEventArgs args)
{
    //Enable Excel-like computation
    spreadsheet.ActiveGrid.FormulaEngine.ExcelLikeComputations = true;
}
' Event subscription
AddHandler spreadsheet.WorkbookLoaded, AddressOf OnWorkbookLoaded

' Event customization
Private Sub OnWorkbookLoaded(ByVal sender As Object, ByVal args As WorkbookLoadedEventArgs)
	' Enable Excel-like computation
	spreadsheet.ActiveGrid.FormulaEngine.ExcelLikeComputations = True
End Sub

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

NOTE

You can refer to our WPF Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our WPF Spreadsheet example to know how to render and configure the spreadsheet.