Formulas in UWP Spreadsheet (SfSpreadsheet)

10 May 202121 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