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.

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.

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.

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.

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.

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.

ISBLANK

def ISBLANK(arg):

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

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.

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.

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.

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.

PI

def PI():

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

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.

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

TODAY

def TODAY():

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

VALUE

def VALUE(item):

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

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.