ABS

def ABS(value)

ABS returns the absolute value of its argument. ABS(3) and ABS(-3) both return 3.

ACOS

def ACOS(value)

Return the arc cosine (measured in radians) of value

AND

def AND(*args)

AND takes any number of arguments and returns True if all of the arguments are True. If any of the arguments are False, it returns False into the cell containing the AND function. Arguments can be values, cells or cell ranges.

When used in a formula, AND must be called with at least one argument.

ASIN

def ASIN(value)

Return the arc sine (measured in radians) of value

ATAN

def ATAN(value)

Return the arc tangent (measured in radians) of value

AVEDEV

def AVEDEV(*args)

Return the mean of the absolute deviation of each sample from their mean.

AVERAGE

def AVERAGE(*args)

Average takes any number of arguments and returns the average value. =AVERAGE(B2:D40) will take an average of the cells in the range B2 to D40. Arguments can be numeric values, cells, cell ranges, rows or columnss. Rows, columns and cell ranges will have each populated cell treated as an individual argument. Empty cells and cells populated with non-numeric values will be ignored.

If the total number of items to be averaged is zero, AVERAGE will raise a ZeroDivisionError.

CHITEST

def CHITEST(actualFrequencies, expectedFrequencies)

This function is used to test whether separate events are independent.

If actualFrequencies and expectedFrequencies are 2-D cell ranges, they are interpreted as tables containing frequencies for different variables. If they are 1-D cell ranges or sequences, they are interpreted as tables with only one variable.

The chi squared statistic is then calculated from this table and the probability of a chi squared random variable with an appropriate degree of freedom taking a value greater than this statistic is returned.

The closer the probability is to 0, the more the two variables can be considered independent.

If the inputs are 2-d cell ranges, the number of degrees of freedom is (height - 1)(width - 1). If the inputs are 1-d cell ranges or sequences, the number of degrees of freedom is length - 1.

If the inputs do not have the same dimensions or if they both have a length of 1, ValueError is raised.

CONCATENATE

def CONCATENATE(*args)

CONCATENATE takes any number of arguments and concatenates them together, returning a string (text). The input arguments need not be strings.

Passing a cell range to CONCATENATE has the same effect as concatenating all of the values in the individual cells.

CONFIDENCE

def CONFIDENCE(alpha, stddev, size)

Return the confidence interval for the mean. alpha is the significance level, stddev is the standard deviation for the population, while size is the size of the sample of the population.

alpha must be in open interval between 0 and 1 and stddev and size must be greater that 0, otherwise ValueError will be raised.

CORREL

def CORREL(array1, array2)

Return the Pearson correlation coefficient.

Non-numeric values and empty cells are ignored. The arguments must be iterables of the same length.

If the standard deviation of the values is zero, ZeroDivisionError will be raised.

COS

def COS(value)

Return the cosine of value (measured in radians)

COUNT

def COUNT(*args)

Count the number of cells that contain numbers as well as the number of arguments that are numbers.

Arguments can be cells, cell ranges or any object.

COUNTA

def COUNTA(*args)

Count the number of cells that are not empty as well as the number of arguments that are not None

Arguments can be cells, cell ranges or any object.

COUNTIF

def COUNTIF(searchRange, value)

Count the number of cells in searchRange, whose value is equal to value. If value is a string, then it is matched in a case- insensitive way.

COVAR

def COVAR(seq1, seq2)

Return the covariance of seq1 and seq2.

The input data sets need to have the same length, otherwise ValueError will be raised. Non-numeric values and empty cells are ignored. If the data-sets have no numeric values, ZeroDivisionError will be raised.

DAY

def DAY(date)

Returns the Day component of the given date, or zero for empty cells

DDE

def DDE(function, parameters)

The DDE function is a placeholder for DDE link support, which may be implemented in future versions. Currently it simply raises a NotImplementedError.

DEVSQ

def DEVSQ(*args)

Returns the sum of squares of deviations of data points from their sample mean.

Non-numeric arguments are ignored.

EDATE

def EDATE(date, months)

Returns the given date plus the given number of months. months may be negative. If date is 0 or Empty, assume it is 00 Jan 1900 (e.g. EDATE(Empty, 1) returns 31 Jan 1900)

EOMONTH

def EOMONTH(date, months)

Add the given number of months to date. months may be negative. Round up the return value to the date at the end of that month. If date is 0 or Empty, assume it is 00 Jan 1900.

ERF

def ERF(x, y=None)

ERF (error function)

erf(x) = 2K / sqrt(pi)

Where K is the integral of exp(-t^2) from 0 to x, with respect to t.

The implementation is accurate to 13 significant figures.

ERFC

def ERFC(a)

ERFC (the complementary error function) is equal to 1 - ERF(x).

The implementation is accurate to 12 significant figures.

EXP

def EXP(value)

Returns e raised to the power of value

FIND

def FIND(needle, haystack, startPosition=1)

Returns the location of a substring in a string. The search is case-sensitive. startPosition defaults to 1 (start of string) If the Find function does not find a match, it will raise NotFoundError.

FORECAST

def FORECAST(x, known_y, known_x)

Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression.

ValueError is raised if data sets are of different lengths, or not enought data points are given

GEOMEAN

def GEOMEAN(*args)

Return the geometric mean of the arguments. Non-numeric values and empty cells are ignored.

HARMEAN

def HARMEAN(*args)

Return harmonic mean of the arguments. Non numeric values and empty cells are ignored. If any number is not positive a ValueError will be raised.

HLOOKUP

def HLOOKUP(key, cellRange, valueRow, rangeLookup=<Optional
            parameter>)

HLOOKUP is the horizontal lookup function. This function searches for key in the first row of cellRange and returns the corresponding value in row valueRow of cellRange. This search is always case-insensitive.

If rangeLookup is True then the matching key will be the key immediately preceding the first key greater than key. rangeLookup defaults to True. If the first row is not sorted in ascending order then it is unlikely that the result will be as desired.

If rangeLookup is False then only exact matches will return a value.

If the type of key fails to match the types of the keys in the first row of cellRange then a TypeError is raised.

If valueRow is greater than the height of cellRange, then an IndexError is raised.

If no matching key is found, then a NotFoundError is raised.

Note that if you are using HLOOKUP to find exact matches (rangeLookup is False), it is usually better to use header rows and columns. Then you can index the cell range with the name of the entry (the column title) and the name of the row that you want.

IF

def IF(condition, trueResult, falseResult=<Optional
       parameter>)

If condition evaluates to True, then trueResult is evaluated and returned, otherwise falseResult is evaluated and returned. If falseResult is omitted and condition is False, IF returns FALSE.

This allows you to write formulae like:

IF(A1>6, A2+3, A2-3)

If the value in A1 is greater than 6, then 'A2+3' is returned, otherwise 'A2-3' is returned.

Only one of trueResult or falseResult will actually be evaluated. This means that you can have function calls with side effects in them, and only the appropriate one will be evaluated.

This makes using IF from user code slightly more complex, it actually takes three functions as arguments. If the result of calling the condition function is True then trueResult is called and returned. Otherwise falseResult is called and returned.

In general, you shouldn't use the IF function inside user code and should use the normal Python 'if' statement instead.

INDEX

def INDEX(collection, row, col=None)

Indexes into the collection, which may be a list, tuple or CellRange Returns the value at the given index, using the given 1-based indexes. col is only used when indexing into a 2D CellRange. Indexing with row=0 is equivalent to row=1, and similarly col=0 is equivalent to col=1.

INT

def INT(value)

Reutrn the floor of value as an int.

INTERCEPT

def INTERCEPT(ys, xs)

Return the y-value where the regression line for those points with y and x co-ordinates as specified meets the y-axis.

ISBLANK

def ISBLANK(arg)

ISBLANK takes a single argument and returns TRUE if the argument is the special value Empty, and FALSE otherwise.

ISERR

def ISERR(arg)

ISERR is an alias to ISERROR, included for legacy compatibility.

Its behaviour is exactly the same as ISERROR.

ISERROR

def ISERROR(arg)

ISERROR evaluates an expression and returns True if the expression raises an error. It can also take a cell, and returns True if the cell is in an error state.

ISERROR is intended to be used inside formulae. If you use it from user code, you must wrap the cell or expression in a function. In user code it is generally better to check the Error attribute of a cell or use Python's built in exception handling.

For backwards compatibility reasons, ISERROR will also operate on cell ranges, but the rules are slightly eccentric. If the cell range encompasses more than one row, then ISERROR returns True. Otherwise, ISERROR will return the error state of the leftmost cell in the range.

ISNUMBER

def ISNUMBER(arg)

ISNUMBER takes a single argument and returns TRUE if the argument is a number, and FALSE otherwise.

LARGE

def LARGE(items, k)

Find the k-th largest numeric item in items. Non-numeric items (including empty cells) are ignored. If there are fewer than index numbers in items an IndexError is raised.

Example: LARGE([1, 2, 2, 3], 3) returns 2.

LEFT

def LEFT(text, num_chars=1)

Returns the number of characters from the beginning of the text provided. If num_chars is omitted it defaults to 1.

If text is not a string it will be converted to a string. num_chars must be an integer equal to or greater than zero.

If the input string comes from an empty cell (i.e. is the value Empty) then an empty string will be returned.

LINEST

def LINEST(y_s, x_s=<Optional parameter>, const=True,
           stats=False)

Attempt to approximate the given y-values with a formula of the form:

y = m_1 x_1 + m_2 x_2 + ... m_n x_n + c

Where x_s is a list of tuples of the form (x_1, x_2, ... x_n).

This function returns the list [m_n, m_n-1, ... m_1, c].

This approximation is selected to minimise the squares of the differences between y_s values and the values given by the approximation.

Sometimes there is not enough information to fully constrain the result - this situation is known as collinearity. In this case, a particular solution is chosen. If m_3 and m_4 were dependent then m_3 would be set to zero.

If x_s is not specified it is assumed to be the list [1, 2, 3, ...].

If y_s is a CellRange with width 1, and x_s is a CellRange, then x_s is interpreted as the list of x_s's rows. So, x_s and y_s must have the same height. Similarly, if y_s is a CellRange with height 1, x_s is interpreted a list of x_s's columns.

If y_s is a 2-D CellRange and x_s is a CellRange then x_s is interpreted as a list of numbers in raster order (left-to-right, then top-to-bottom), so x_s and y_s must have the same number of cells.

If const is False, then the c term in the approximation is forced to be zero. The default value is True.

If stats is True then a NotImplementedError is raised. This feature will be added in the future. The default value is False.

Example: LINEST([3, 5, 7, 9], [1, 2, 3, 4]) will return [2, 1], because y = 2x + 1 matches the data.

LN

def LN(value)

Returns the natural logarithm (base e) of value

LOG

def LOG(value, base=10)

Returns the logarithm of value to the given base. If the base not specified, base 10 is used.

MATCH

def MATCH(key, cellRange, matchType=1)

Searches for key in the elements of cellRange and returns the index of the first matching item.

key is the value to search for in the cellRange.

cellRange is a range of cells that contains the value that you are searching for. It can be either a single-row range, or a single-column range.

matchType is optional. It the type of match that the function will perform. The possible values are:

1 (default) Find the largest cellRange element that is less than or equal to key. The cellRange should be sorted in ascending order.

0 Find the first cellRange element that is equal to key. The cellRange can be sorted in any order.

-1 Find the smallest cellRange element that is greater than or equal to key. The cellRange should be sorted in descending order.

MATCH is case-insensitive.

If no match is found, MATCH will raise an error.

If matchType is 0 then you can use wildcards in key:

* matches any sequence of characters

? matches any single character

MAX

def MAX(*args)

MAX takes any number of arguments and returns the maximum value. Arguments can be numeric values, cells, cell ranges, rows or columns. Rows, columns and cell ranges will have each populated cell treated as an individual argument. Cells populated with non-numeric values will be ignored. =MAX(H8:M12) will find the maximum value within the stated range H8 to M12.

If cells with non-numeric values (text) are passed in, but no numeric values (so there are no empty cells or cells containing values as numbers), then a TypeError will be raised.

If no numeric values are passed in, and no non-numeric values (i.e. all the cells are empty), MAX returns 0.

MEDIAN

def MEDIAN(*args)

MEDIAN takes any number of arguments and returns the median value. Arguments can be numeric values, cells, cell ranges, rows or columns. Rows, columns and cell ranges will have each populated cell treated as an individual argument. Cells populated with non-numeric values will be ignored. =MEDIAN(F1:M30) will find the median (middle value) of the cells in the range F1 to M30.

If you call MEDIAN with no arguments then a TypeError will be raised.

If cells with non-numeric values (text) are passed in, but no numeric values (so there are no empty cells or cells containing values as numbers), then a TypeError will be raised.

If no numeric values are passed in, and no non-numeric values(i.e. all the cells are empty), MEDIAN returns 0.

MID

def MID(text, start, length=1)

Returns the substring starting at start with the specified length. If the argument is not a string, a conversion is performed.

MIN

def MIN(*args)

MIN takes any number of arguments and returns the minimum value. Arguments can be numeric values, cells, cell ranges, rows or columss. Rows, columns and cell ranges will have each populated cell treated as an individual argument. Cells populated with non-numeric values will be ignored. =MIN(G4:L80) will find the minimum value within the stated range G4 to L80.

If cells with non-numeric values (text) are passed in, but no numeric values (so there are no empty cells or cells containing values as numbers), then a TypeError will be raised.

If no numeric values are passed in, and no non-numeric values (i.e. all the cells are empty), MIN returns 0.

MOD

def MOD(number, divisor)

MOD return the remainder after number is divided by divisor.

MONTH

def MONTH(date)

Returns the Month component of the given date, or one for empty cells

NORMDIST

def NORMDIST(x, mu, sigma, cumulative)

Returns the value of the normal distribution at x.

The normal distribution is centered around mu, with a spread (standard deviation) characterised by sigma.

If cumulative is False, returns the value of the normal distribution at x, which approaches 0 for large negative or positive x, and peaks at x``==``mu. The peak of the curve is such that the area under the curve == 1. The width of the peak (measured between the two inflection points either side of the peak) is equal to sigma.

If cumulative is True, returns the cumulative distribution, which approaches 0 for large negative x, approaches 1 for large positive x, and passes through 0.5 as x``==``mu.

The implementation is accurate to 14 significant figures.

NORMSDIST

def NORMSDIST(x, cumulative=True)

Returns the value of the cumulative standard normal distribution.

If cumulative=False, this curve is the classic 'bell shape', centered about x=0, with a peak of about 0.6, such that the total area under the curve is 1.0.

If cumulative=True (the default), then this is the integral of the above curve w.r.t. x, which approaches 0 for large negative x, and approaches 1 for large positive x, and passes through 0.5 at x==0.

The implementation is accurate to 14 significant figures.

NOW

def NOW()

Returns a Date object representing the precise time the function was called (with the same resolution as the underlying .NET DateTime object)

OR

def OR(*args)

The OR function assesses any number of arguments and returns 'True' if any the arguments (statements about values, cells or cell ranges) are true. If all of the arguments are false, it returns 'False' into the cell. Arguments can be values, cells or cell ranges.

When used in a formula, OR must be called with at least one argument.

PEARSON

def PEARSON(array1, array2)

Compatibility alias to CORREL.

PERCENTILE

def PERCENTILE(values, percentile)

Return the value that represents the requested percentile in the data. percentile needs to be a number from 0 to 1, non-number values in the data are ignored.

PERCENTRANK

def PERCENTRANK(values, value, significance=3)

Return the percentile rank (between 0 and 1) of value in values. Non-number items in values are ignored. value must be within the range of the data in values. This function is the inverse of the PERCENTILE function. significance specifies how many decimal places to round to; it defaults to three for compatibility with Microsoft Excel.

PI

def PI()

This function, =PI(), returns pi. The empty brackets must be used. It exists as a function for compatibility with other spreadsheets.

RAND

def RAND()

Returns a random number r, where 0 <= r < 1.

RANK

def RANK(element, items, smallestFirst=False)

Find element's index in items sorted according to smallestFirst parameter. Non-numeric values in items are ignored, Empty cells are treated as zeros.

RIGHT

def RIGHT(text, num_chars=1)

Returns the number of characters from the end of the text provided. If num_chars is omitted it defaults to 1.

If text is not a string it will be converted to a string. num_chars must be an integer equal to or greater than zero.

If the input string comes from an empty cell (i.e. is the value Empty) then an empty string will be returned.

ROUND

def ROUND(value, decimal_places=0)

Returns value rounded to the given number of decimal_places

decimal_places defaults to 0, or can be negative: for example -1 rounds to the nearest 10.

RSQ

def RSQ(array1, array2)

Return the square of the Pearson correlation coefficient. See the CORREL documentation for error descriptions.

SIN

def SIN(value)

Return the sine of value (measured in radians)

SLOPE

def SLOPE(ys, xs)

Return the gradient of the regression line for those points with y and x co-ordinates as specified.

SMALL

def SMALL(items, k)

Find the k-th smallest numeric item in items. Non-numeric items (including empty cells) are ignored. If there are fewer than index numbers in item an IndexError is raised.

Example: SMALL([1, 2, 2, 3], 3) returns 2.

SQRT

def SQRT(item)

This function returns the square root of a value. =SQRT(L22) will return the square root of the value in cell L22. =SQRT(25) will return the square root of the value in brackets i.e. 5. The brackets must be used to return the correct value.

STANDARDIZE

def STANDARDIZE(x, mean, standard_dev)

Given a distribution with mean and standard_dev, return the normalized value of x. standard_dev needs to be positive

STDEV

def STDEV(*args)

Stdev takes any number of arguments and returns the standard deviation value. =STDEV(B2:D40) will return the standard deviaton of the cells in the range B2 to D40. Arguments can be numeric values, cells, cell ranges, rows or columns. Rows, columns and cell ranges will have each populated cell treated as an individual argument. Empty cells and cells populated with non-numeric values will be ignored.

If the total number of items to be calculated is zero or one, STDEV will raise a ZeroDivisionError.

STDEVP

def STDEVP(*args)

Calculates standard deviation for the entire population using N denominator. Any non-numeric value or empty cells are ignored.

If the total number of items to be calculated is zero or one, STDEV will raise a ZeroDivisionError.

SUM

def SUM(*args)

SUM takes any number of arguments and returns the sum of the values. Arguments can be numeric values, cells, cell ranges, rows, columns or worksheets. Rows, columns, worksheets and cell ranges will have each populated cell treated as an individual argument. Cells populated with non-numeric values will be ignored.

If no numeric values are passed in, SUM returns 0.

=SUM(A1:D8,E4:E11) will sum all the values in the range A1 and D8 plus the range E4 to E11

SUMIF

def SUMIF(searchRange, value, answerRange=<Optional
          parameter>)

SUMIF adds up all the cells from answerRange, where the corresponding cell in searchRange matches value. Each cell in searchRange is checked as to whether it matches value. If it matches, then the corresponding cell from answerRange is added to the total in the cell containing the SUMIF function. The "searchRange" is the range in which the function searches for the specified "value". The "value" can can be any type of value, but if it is a string of text then it is matched case-insensitively and must be put in quotes. To sum values that correspond to the stated "value", then an "answerRange" of the corresponding values needs to be specified.

=SUMIF(H2:H30, "Profit", F2:F30) would sum all the values in F2:F30 if "Profit" had appeared in cells H2:H30

The total is displayed in the cell containing the function, which must be outside the specified ranges.

The current implementation does not support 'conditions' in the value, the cell will only be matched if its value is equal to the value you pass to SUMIF.

The answerRange does not need to be the same size as the searchRange.

answerRange is optional, and if omitted then searchRange is also used as the answerRange.

answerRange can either be specified as a cell range, or as the cell at the top left corner of the range to be checked (in which case, the answerRange is assumed to be the same size as the searchRange).

Cells containing strings of text within the answerRange will be ignored in the calculation.

If no cells in searchRange match value, SUMIF returns 0.

SUMPRODUCT

def SUMPRODUCT(*args)

This function will group together all the initial elements, then all the second elements and so on, before taking the product of all elements in each group and returning the sum of these products.

TAN

def TAN(value)

Return the tangent of value (measured in radians)

TODAY

def TODAY()

Returns a Date object representing 00:00 on the day the function was called.

TRIM

def TRIM(text)

Strips space characters from the beginning and end of the given text, and reduces multiple consecutive spaces between words into a single space. Does not affect tab characters or other whitespace.

VALUE

def VALUE(item)

This function will convert a number contained in a string from a text format to a floating point numerical value. If the conversion fails, a TypeError is raised.

VAR

def VAR(*args)

Returns the variance, assuming the data represents a sample of the population. Use VARP if the data is the entire population.

Non-numeric values and empty cells will be ignored. If there is only one argument, ZeroDivisionError is raised.

VARP

def VARP(*args)

Returns the variance, assuming the data is the entire population. Use VAR if the data represents a sample of the population.

Non-numeric values and empty cells will be ignored.

VLOOKUP

def VLOOKUP(key, cellRange, valueColumn, rangeLookup=<Optional
            parameter>)

VLOOKUP is the vertical lookup function. This function searches for key in the first column of cellRange and returns the corresponding value in column valueColumn of cellRange. This search is always case-insensitive.

If rangeLookup is True then the matching key will be the key immediately preceding the first key greater than key. rangeLookup defaults to True. If the first column is not sorted in ascending order then it is unlikely that the result will be as desired.

If rangeLookup is False then only exact matches will return a value.

If the type of key fails to match the types of the keys in the first column of cellRange then a TypeError is raised.

If valueColumn is greater than the width of cellRange, then an IndexError is raised.

If no matching key is found, then a NotFoundError is raised.

Note that if you are using VLOOKUP to find exact matches (rangeLookup is False), it is usually better to use header rows and columns. Then you can index the cell range with the name of the entry (the row title) and the name of the column that you want.

WEEKDAY

def WEEKDAY(date, week_type=1)

Returns a number representing the day of the week, given a date value.

date may be a Date object, or a string which will be parsed by the Date constructor.

week_type determines what is returned by the function. It may be any of:

1 Returns a number from 1 (Sunday) to 7 (Saturday). (default) 2 Returns a number from 1 (Monday) to 7 (Sunday). 3 Returns a number from 0 (Monday) to 6 (Sunday).

YEAR

def YEAR(date)

Returns the Year component of the given date, or 1900 for empty cells