SpreadCE Help

(C) 2001 Bye Design Ltd

Basics
Menu options
Formulas
Functions
Charts
Macros
Objects


Basics

A spreadsheet file is made up of pages, called "sheets". There are different types of sheets: Worksheets - which are used for storing data and doing calculations, Chart sheets - which are used for displaying graphs and charts, and Macro sheets - which are used for storing the instructions that make up custom (user-defined) functions and commands.

A worksheet consists of many boxes, called "cells", arranged in a grid. There are 1048576 rows and 16384 columns of these. The rows are number from 1 to 1048576, and the columns are named after the letters of the alphabet, going from A to Z, then AA, AB ... AZ, BA, BB ... etc all the way up to XFD, which is the 16384th column.

An individual row is referred to by its row number, an individual column is referred to by its column name, and an individual cell is referred to by the column name and row number that it is in. For example the cell in the top left corner of a worksheet is referred to as cell A1, because it is in column A and row 1.

The sheets are referred to by their sheet names. When new sheets are added they are given default names such as Sheet1, Sheet2 etc. You can change these names by using the Format Sheet Rename menu option. The sheet names are displayed on tabs at the bottom of the screen. To select a particular sheet to be displayed, either tap on the sheet name tab, or use the arrow buttons in the bottom left corner.

The currently active, or 'cursor' cell is displayed with a thick black border around it. If there is a formula in this cell then it will be displayed in the formula bar at the top of the screen, and if you want to enter a formula then this is the cell where the formula will be stored. To move the cursor to a different cell you can use the up/down/left/right arrow keys on your keyboard, or use your mouse or pointer to click on a different cell.

Most operations on the spreadsheet affect the currently selected cells, called the selection. Normally there is only one cell selected, that is the cursor cell. For many operations you will want to select more than one cell. You can do this by moving the cursor to one corner of the area to be selected (see above) and then either hold down the shift key and use the arrow keys to extend the selection as far as necessary, or drag your mouse or pointer across the screen to the far corner of the area to be selected. The selected cells will be displayed with their colours reversed.


Menu options

File menu
Edit menu
Insert menu
Format menu


File menu

NewOpens a new blank workbook.
OpenDisplays the File Open dialog box for you to choose a spreadsheet file to open.
RecentDisplays a list of the 9 most recently used (i.e. opened or saved) files as a shortcut for opening them.
CloseCloses the current workbook, after prompting you to save any changes.
SaveSaves the current workbook back into the file it was opened from. If this spreadsheet was created with the File New menu option rather than opened with the File Open menu option, then you will be asked for a file name to save it under.
Save asDisplays the File Save dialog box for you to choose a name and location for saving the current workbook.
WindowDisplays a list of the currently open workbooks and allows you to select a different one as the active workbook.
ExitThis shuts down the program, after prompting you to save any changed workbooks.


Edit menu

UndoThis option undoes the last change you made to the spreadsheet, should you make a mistake or just change your mind. There are currently 16 levels of Undo, that is, the program remembers the last 16 things that you did and can step back through them using this menu option.
CutThis option removes the contents of the currently selected cells and places them on the clipboard, so that you can paste them somewhere else. Only one selection can be stored on the clipboard at a time, so this will replace whatever was previously on the clipboard.
CopyThis option copies the contents of the currently selected cells and places them on the clipboard, so that you can paste them somewhere else. Only one selection can be stored on the clipboard at a time, so this will replace whatever was previously on the clipboard.
PasteThis option takes the contents of the clipboard and puts it into the currently selected cells.
Paste SpecialWhen cells are cut or copied to the clipboard, the program stores not only what is displayed in the cell, but also the formula that is in the cell and the formatting. The Paste menu option will paste all of this information into the target cells, but this menu option allows you to paste just some of that information. A dialog box is displayed for you to choose what is pasted.
Fill
DownCopies the top row of the selection into the other rows of the selection.
RightCopies the left column of the selection into the other columns of the selection.
UpCopies the bottom row of the selection into the other rows of the selection.
LeftCopies the right column of the selection into the other columns of the selection.
SeriesDisplays a dialog box that allows you to fill the current selection with a series of numbers or dates.
Clear
AllRemoves the contents and formatting of the currently selected cells.
FormatsRemoves only the formatting of the currently selected cells, leaves the contents unchanged.
ContentsRemoves only the contents of the currently selected cells, leaves the formatting unchanged.
CommentsRemoves any comments associated with the currently selected cells.
DeleteIf entire rows or columns have been selected then this menu option will delete the selected rows or columns. Otherwise (that is, one or more cells have been selected) a dialog box will be displayed containing 4 options: (1) Shift cells left - delete the selected cells and move the cells on the right of them to the left to take their places, (2) Shift cells up - delete the selected cells and move the cells below them up to take their places, (3) Entire row - delete the entire rows that the selection spans, the rows below will be moved up, (4) Entire column - delete the entire columns that the selection spans, the columns to the right will be moved left.
Delete SheetDelete the currently displayed worksheet, chart or macro sheet. If there is only one worksheet in your spreadsheet file, you will not be allowed to delete it.
Move or Copy SheetAllows you to move or copy the current sheet to a different position in the file.
FindAllows you to look for cells that contain specified things. This menu option displays a dialog box asking you 4 things: (1) the text that you want to search for, (2) whether to look for it in the formulas (what was typed into the cells) or in the values (what is displayed in the cells), (3) whether the search is to be case-sensitive (whether you want upper and lower case letters to be treated as different), and (4) whether to find cells that are equal to the text that you typed or just contain the text that you typed. Entering these and pressing Find next will move the cursor to the first cell after the current cursor position that matches your request. The dialog box stays on the screen until it is cancelled, so repeated pressing of the Find next button will take you to each of the cells that match your request.
ReplaceAllows you to search for text in cell formulas and optionally replace one or all occurrences.
Go ToAllows you to move directly to another part of the worksheet. This menu option displays a dialog box that asks you for a cell reference. Entering the reference and pressing OK will move the cursor to the specified cell.


Insert menu

CellsIf entire rows or columns have been selected then this menu option will insert the selected rows or columns. Otherwise (that is, one or more cells have been selected) a dialog box will be displayed containing 4 options: (1) Shift cells right - the cells to the right of the selection will be moved to the right to make room for the new cells, (2) Shift cells down - the cells below the selection will be moved down to make room for the new cells, (3) Entire row - the entire rows that the selection spans will be inserted, (4) Entire column - the entire columns that the selection spans will be inserted.
RowsInserts one or more new rows where the selection is.
ColumnsInserts one or more new columns where the selection is.
WorksheetInserts a blank worksheet at the end of the spreadsheet file.
Chart
SheetInserts a new chart sheet at the end of the spreadsheet file. If no cells have been selected then the Format Chart dialog box will be displayed, otherwise a default column chart will be built using the selected data.
ObjectInserts a new chart object on the current worksheet. Drag the pointer across the worksheet to indicate where the chart should be placed. If no cells have been selected then the Format Chart dialog box will be displayed, otherwise a default column chart will be built using the selected data.
Macro sheetInserts a blank macro sheet at the end of the spreadsheet file. Macro sheets are used for storing custom (user-defined) functions and commands.
FunctionDisplays a dialog box from which you can select a function to be inserted into the current cell formula.
Name
DefineDisplays a dialog box showing the names that have been defined for references in this spreadsheet file. It allows you to add new names, change the references of existing names, or to delete names that are no longer required.
PasteDisplays a dialog box showing the names that have been defined for references in this spreadsheet file. It allows you to insert a name selected from the list, or to paste a list of all the names.
CommentThis option allows you to add a comment to the currently selected cell. If there is already a comment attached to the cell, then this option will be changed to Edit comment, and the existing comment will be displayed for you to change. You can press the Escape key to leave without entering or changing the comment, or you can click outside the comment box to save the changes. When a cell contains a comment, there will be a comment indicator in the top-right corner of the cell. To display a comment you can either use this menu option, or you can click and hold the pointer on the cell for 1/2 a second, the comment will be displayed until you move or release the pointer. To remove a comment from a cell, use the Edit Clear Comments menu option.
Control
LabelInserts a label.
Group boxInserts a group box.
Command buttonInserts a command button.
Check boxInserts a check box.
Option buttonInserts an option button.
List boxInserts a list box.
Combo boxInserts a combo box.
Scroll barInserts a horizontal or vertical scroll bar.
Spin controlInserts a spin control.
Drawing
LineInserts a line.
RectangleInserts a rectangle.
OvalInserts an oval.
Text boxInserts a text box.
AutoShapesInserts lines, connectors and other shapes.
HyperlinkDisplays a dialog box that allows you to add, change or remove a link from the current selection to a file and/or location. To specify a location in the current file leave the file name blank. To remove a link, delete the file name and the location name.


Format menu

CellsDisplays a dialog box that allows you to change the way that the contents of the selected cells are displayed. You can change the way that numbers, amounts of money, dates, times and text are formatted. You can change the colour of the text, the pattern and colour of the cell background, and the type and colour of the cell borders.
Row
HeightDisplays a dialog box showing the current height of the selected rows in points (the unit of measurement used for text), and allows you to change it. You can also change the row height by dragging the separator between the row numbers.
AutoFitSets the row height to the largest required for the values displayed in the row. You can also autofit a row by double-clicking on the separator below the row number.
HideHides the selected rows.
UnhideUnhides the selected rows. Because you cannot select individual hidden rows, you will have to use the Edit Go To menu option to move the cursor to the required row, or select all the rows from the one above the hidden rows to the one below, before using this option.
Column
WidthDisplays a dialog box showing the current width of the selected columns in characters, and allows you to change it. You can also change the column width by dragging the separator between the column headings.
AutoFitSets the column width to the largest required for the values displayed in the selected cells. You can also autofit a column by double-clicking on the separator to the right of the column heading.
HideHides the selected columns.
UnhideUnhides the selected columns. Because you cannot select individual hidden columns, you will have to use the Edit Go To menu option to move the cursor to the required column, or select all the columns from the one to the left of the hidden columns to the one to the right, before using this option.
Standard WidthDisplays a dialog box showing the default width of the columns on the current sheet, and allows you to change it.
Sheet
RenameDisplays a dialog box showing the current sheet name, and allows you to change it.
HideAllows you to hide the currently displayed sheet.
UnhideAllows you to select a hidden sheet to be displayed.
GridlinesAllows you to turn the gridlines on and off for the current sheet.
HeadingsAllows you to turn the row and column headings on and off for the current sheet.
Zero valuesAllows you to toggle the display of zero values on the sheet.
ZoomAllows you to set the zoom level for the current sheet.
ProtectAllows you to turn protection on and off for the current sheet. When protection is on, only cells that are not locked (set by using the Format Cells menu option) can be changed.
FormulasAllows you to toggle the display of the cell formulas and cell values for the current sheet.
Tab colorAllows you to change the tab color for the current sheet.
File
Vertical scrollbarAllows you to select whether the Vertical scrollbar is to be displayed for sheets in the current file.
Horizontal scrollbarAllows you to select whether the Horizontal scrollbar is to be displayed for sheets in the current file.
Sheet tabsAllows you to select whether the Sheet tabs are to be displayed for sheets in the current file.
Program
ToolbarsAllows you to show or hide the tool bars. The tool bar buttons provide short cuts to commonly-used menu options.
Formula barAllows you to show or hide the formula bar. This is the area where formulas are entered and edited.
Status barAllows you to show or hide the status bar. The status bar shows the current cursor position, and has a menu (activated by right-click or double-click) that allows you to select items to be displayed in the status bar, such as the sum of the selected cells.
Full screenAllows you to show or hide the toolbars, formula bar, status bar, headings, scroll bars and sheet tabs so that more cells can be displayed.
ConditionalAllows you to select alternative formatting for cells depending on conditions.
Freeze PanesOn a worksheet, this allows you to fix the currently displayed row and/or column headings so that they will not move when the sheet is scrolled. When you use this option, the rows above the cursor cell and the columns to the left of it will be fixed. For example, if you want the top row to stay the same when you page down through the sheet, move the cursor to cell A2 and then use this menu option, the row above cell A2, which is row 1, will stay displayed when you page through the sheet, and as there are no columns to the left of cell A2, no columns will be fixed. When this option has been used, the menu option changes to 'Unfreeze Panes', which allows you to free the rows and columns again.
Data
SortDisplays a dialog box that allows you to sort the currently selected cells, either by rows or columns, in ascending or descending order.
Filter
AutoFilterAllows you to filter the spreadsheet rows by specifying selection criteria.
Show AllTurns off any active filters.
Advanced FilterAllows you to filter the spreadsheet rows by specifying more complex criteria.
FormAllows you to display, update and search a table of data one record at a time.
ValidationAllows you to restrict the range of values that can be entered into a cell, or display a drop-down list to choose a value from, and display input and error messages.
Text to ColumnsAllows you to parse text into separate columns of data.
Goal seekAllows you to set a cell to a specific value by changing another cell.
ChartOn a chart sheet, this displays a dialog box that allows you to change the type of the chart, the data that is used, and the way in which the chart is formatted.
ControlDisplays a dialog box that allows you to set the attributes of the currently selected Control object.
DrawingDisplays a dialog box that allows you to set the attributes of the currently selected Drawing object.


Formulas

To enter text or number information into the spreadsheet, just move the cursor to the required cell, type the text or number in the formula bar and press the enter key. In most spreadsheets you will want to do some calculations with this text and number information. This is done using formulas. Formulas are entered into cells using an equal sign followed by the expression to be evaluated. The result of the calculation will be displayed in the cell that contains the formula. Examples:

=2+2Will display the value 4.
="Hello " & "world!"Will display Hello world!.
=SUM(A1:A10)Will add up the values in cells A1 to A10 and display the total.

Note that although you will usually see function names (like SUM above) in upper case, you can enter them in upper or lower case. The same applies to cell names, you could enter A1 or a1 to refer to the top left cell.

Many formulas, like SUM, allow you to specify not just one cell but a whole block of cells as a parameter. This is done by specifying the name of the cell at one corner of the block, followed by a colon, followed by the name of the cell at the opposite corner. For example the 2x2 square of cells in the top left corner of a worksheet could be specified by A1:B2 (or A2:B1, or B1:A2 etc, it doesn't matter which corner is specified first).

To refer to cells on other sheets, prefix the cell or range reference with the sheet name followed by an exclamation mark, for example Sheet1!A1 or Sheet1!A1:B2. If the sheet name contains spaces, you will need to put it in single quotes, like this: 'Other sheet'!A1.

Some formulas, such as =MMULT(A1:B2,C1:D2) (which multiplies two matrices together), can return more than one value. These are called Array formulas. To use these, you will first need to select a block of cells of the correct size and shape for the expected results, then type your formula into the formula bar, and enter it not by pressing the Enter key, but by holding down the Shift and Control keys and then pressing the Enter key. This tells the program that this is an Array formula.


Functions

Database
Date/time
Engineering
Financial
Information
Logical
Lookup/reference
Math/trigonometry
Statistical
Text
Macro control
Macro command
DDE/external
Web


Database functions

[ ] indicates optional parameters

DAVERAGE(database_range, field, criteria_range)
DCOUNT(database_range, [field], criteria_range)
DCOUNTA(database_range, [field], criteria_range)
DGET(database_range, field, criteria_range)
DMAX(database_range, field, criteria_range)
DMIN(database_range, field, criteria_range)
DPRODUCT(database_range, field, criteria_range)
DSTDEV(database_range, field, criteria_range)
DSTDEVP(database_range, field, criteria_range)
DSUM(database_range, field, criteria_range)
DVAR(database_range, field, criteria_range)
DVARP(database_range, field, criteria_range)


Date/time functions

[ ] indicates optional parameters

DATE(year, month, day)
DATEDIF(date1, date2, units)
DATEVALUE(date_text)
DAY(serial_number)
DAYS(end_date, start_date)
DAYS360(start_date, end_date, [method])
EDATE(start_date, months)
EOMONTH(start_date, months)
HOUR(serial_number)
ISOWEEKNUM(date)
MINUTE(serial_number)
MONTH(serial_number)
NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
NOW()
SECOND(serial_number)
TIME(hour, minute, second)
TIMEVALUE(time_text)
TODAY()
WEEKDAY(serial_number, [return_type])
WEEKNUM(serial_number, [return_type])
WORKDAY(start_date, number_days, [holidays])
WORKDAY.INTL(start_date, number_days, [weekend], [holidays])
YEAR(serial_number)
YEARFRAC(start_date, end_date, [basis])


Engineering functions

[ ] indicates optional parameters

BESSELI(x, n)
BESSELJ(x, n)
BESSELK(x, n)
BESSELY(x, n)
BIN2DEC(number)
BIN2HEX(number, [places])
BIN2OCT(number, [places])
BITAND(number1, number2)
BITLSHIFT(number, shift_amount)
BITOR(number1, number2)
BITRSHIFT(number, shift_amount)
BITXOR(number1, number2)
COMPLEX(real_num, imag_num, [suffix])
CONVERT(number, from_unit, to_unit)
DEC2BIN(number, [places])
DEC2HEX(number, [places])
DEC2OCT(number, [places])
DELTA(number1, [number2])
ERF(lower_limit, [upper_limit])
ERF.PRECISE(x)
ERFC(x)
ERFC.PRECISE(x)
GESTEP(number, [step])
HEX2BIN(number, [places])
HEX2DEC(number)
HEX2OCT(number, [places])
IMABS(inumber)
IMAGINARY(inumber)
IMARGUMENT(inumber)
IMCONJUGATE(inumber)
IMCOS(inumber)
IMCOSH(inumber)
IMCOT(inumber)
IMCSC(inumber)
IMCSCH(inumber)
IMDIV(inumber1, inumber2)
IMEXP(inumber)
IMLN(inumber)
IMLOG10(inumber)
IMLOG2(inumber)
IMPOWER(inumber, power)
IMPRODUCT(inumber1, [inumber2, ...])
IMREAL(inumber)
IMSEC(inumber)
IMSECH(inumber)
IMSIN(inumber)
IMSINH(inumber)
IMSQRT(inumber)
IMSUB(inumber1, inumber2)
IMSUM(inumber1, [inumber2, ...])
IMTAN(inumber)
OCT2BIN(number, [places])
OCT2DEC(number)
OCT2HEX(number, [places])


Financial functions

[ ] indicates optional parameters

ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])
ACCRINTM(issue_date, maturity_date, rate, [par], [basis])
AMORDEGRC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])
AMORLINC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])
COUPDAYBS(settlement_date, maturity_date, frequency, [basis])
COUPDAYS(settlement_date, maturity_date, frequency, [basis])
COUPDAYSNC(settlement_date, maturity_date, frequency, [basis])
COUPNCD(settlement_date, maturity_date, frequency, [basis])
COUPNUM(settlement_date, maturity_date, frequency, [basis])
COUPPCD(settlement_date, maturity_date, frequency, [basis])
CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMPRINC(rate, nper, pv, start_period, end_period, type)
DB(cost, salvage, life, period, [month])
DDB(cost, salvage, life, period, [factor])
DISC(settlement_date, maturity_date, pr, redemption, [basis])
DOLLARDE(fractional_dollar, fraction)
DOLLARFR(decimal_dollar, fraction)
DURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
EFFECT(nominal_rate, npery)
FV(rate, term, payment, [pv], [type])
FVSCHEDULE(principal, schedule_range)
INTRATE(settlement_date, maturity_date, investment_amount, redemption_amount, [basis])
IPMT(rate, period, term, pv, [fv], [type])
IRR(values_range, [guess])
ISPMT(rate, period, term, pv)
MDURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
MIRR(values_range, finance_rate, reinvest_rate)
NOMINAL(effect_rate, npery)
NPER(rate, payment, pv, [fv], [type])
NPV(rate, value1, [value2, ...])
ODDFPRICE(settlement_date, maturity_date, issue_date, first_coupon_date, rate, yield, redemption, frequency, [basis])
ODDFYIELD(settlement_date, maturity_date, issue_date, first_coupon_date, rate, price, redemption, frequency, [basis])
ODDLPRICE(settlement_date, maturity_date, last_coupon_date, rate, yield, redemption, frequency, [basis])
ODDLYIELD(settlement_date, maturity_date, last_coupon_date, rate, price, redemption, frequency, [basis])
PDURATION(rate, pv, fv)
PMT(rate, term, [pv], [fv], [type])
PPMT(rate, period, term, pv, [fv], [type])
PRICE(settlement_date, maturity_date, rate, yield, redemption, frequency, [basis])
PRICEDISC(settlement_date, maturity_date, discount, redemption, [basis])
PRICEMAT(settlement_date, maturity_date, issue_date, rate, yield, [basis])
PV(rate, term, payment, [fv], [type])
RATE(term, payment, pv, [fv], [type], [guess])
RECEIVED(settlement_date, maturity_date, investment, discount, [basis])
RRI(nper, pv, fv)
SLN(cost, salvage, life)
SYD(cost, salvage, life, period)
TBILLEQ(settlement_date, maturity_date, discount)
TBILLPRICE(settlement_date, maturity_date, discount)
TBILLYIELD(settlement_date, maturity_date, price)
VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
XIRR(values_range, dates_range, [guess])
XNPV(rate, values_range, dates_range)
YIELD(settlement_date, maturity_date, rate, price, redemption, frequency, [basis])
YIELDDISC(settlement_date, maturity_date, price, redemption, [basis])
YIELDMAT(settlement_date, maturity_date, issue_date, rate, price, [basis])


Information functions

[ ] indicates optional parameters

CELL(info_type, [reference])
ERROR.TYPE(error_val)
INFO(type_text)
ISBLANK(value)
ISERR(value)
ISERROR(value)
ISEVEN(number)
ISFORMULA(reference)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISODD(number)
ISREF(value)
ISTEXT(value)
N(value)
NA()
SHEET([value])
SHEETS([reference])
TYPE(value)


Logical functions

[ ] indicates optional parameters

AND(logical1, [logical2, ...])
FALSE()
IF(logical_test, [value_if_true], [value_if_false])
IFERROR(value, value_if_error)
IFNA(value, value_if_na)
IFS(logical1, [value_if_true1], [logical2, value_if_true2, ...])
NOT(logical)
OR(logical1, [logical2, ...])
SWITCH(expression, value1, result1, [default or value2, result2, ...])
TRUE()
XOR(logical1, [logical2, ...])


Lookup/reference functions

[ ] indicates optional parameters

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
AREAS(reference)
CHOOSE(index_num, value1, [value2, ...])
COLUMN([reference])
COLUMNS(array)
FORMULATEXT(reference)
HLOOKUP(lookup_value, table_range, row_index_num, [range_lookup])
HYPERLINK(link_location, [friendly_name])
INDEX(reference, [row_num], [col_num], [area_num])
INDIRECT(ref_text, [a1])
LOOKUP(lookup_value, lookup_range, [result_range])
MATCH(lookup_value, lookup_range, [match_type])
OFFSET(reference, rows, cols, [height], [width])
ROW([reference])
ROWS(array)
TRANSPOSE(array)
VLOOKUP(lookup_value, table_range, col_index_num, [range_lookup])


Math/trigonometry functions

[ ] indicates optional parameters

ABS(number)
ACOS(number)
ACOSH(number)
ACOT(number)
ACOTH(number)
AGGREGATE(function_num, [options], ref1, [ref2, ...])
ARABIC(text)
ASIN(number)
ASINH(number)
ATAN(number)
ATAN2(x_num, y_num)
ATANH(number)
BASE(number, radix, [min_length])
CEILING(number, significance)
CEILING.MATH(number, [significance], [mode])
CEILING.PRECISE(number, [significance])
COMBIN(number, number_chosen)
COS(number)
COSH(number)
COT(number)
COTH(number)
CSC(number)
CSCH(number)
DECIMAL(text, radix)
DEGREES(angle)
ECMA.CEILING(number, significance)
EVEN(number)
EXP(number)
FACT(number)
FACTDOUBLE(number)
FLOOR(number, significance)
FLOOR.MATH(number, [significance], [mode])
FLOOR.PRECISE(number, [significance])
GCD(number1, [number2, ...])
INT(number)
ISO.CEILING(number, [significance])
LCM(number1, [number2, ...])
LN(number)
LOG(number, [base])
LOG10(number)
MDETERM(array)
MINVERSE(array)
MMULT(array1, array2)
MOD(number, divisor)
MROUND(number, multiple)
MULTINOMIAL(number1, [number2, ...])
MUNIT(dimension)
ODD(number)
PI()
POWER(number, power)
PRODUCT(number1, [number2, ...])
QUOTIENT(numerator, denominator)
RADIANS(angle)
RAND()
RANDBETWEEN(smallest, largest)
ROMAN(number, [form])
ROUND(number, num_digits)
ROUNDDOWN(number, [num_digits])
ROUNDUP(number, [num_digits])
SEC(number)
SECH(number)
SERIESSUM(x, n, m, coefficients)
SIGN(number)
SIN(number)
SINH(number)
SQRT(number)
SQRTPI(number)
SUBTOTAL(function_num, ref1, [ref2, ...])
SUM(number1, [number2, ...])
SUMIF(range, criteria, [sum_range])
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
SUMPRODUCT(array1, [array2, ...])
SUMSQ(number1, [number2, ...])
SUMX2MY2(array_x, array_y)
SUMX2PY2(array_x, array_y)
SUMXMY2(array_x, array_y)
TAN(number)
TANH(number)
TRUNC(number, [num_digits])


Statistical functions

[ ] indicates optional parameters

AVEDEV(number1, [number2, ...])
AVERAGE(number1, [number2, ...])
AVERAGEA(value1, [value2, ...])
AVERAGEIF(range, criteria, [average_range])
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
BETA.DIST(x, alpha, beta, cumulative, [A], [B])
BETA.INV(probability, alpha, beta, [A], [B])
BETADIST(x, alpha, beta, [A], [B])
BETAINV(probability, alpha, beta, [A], [B])
BINOM.DIST(number_successes, trials, probability, cumulative)
BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2])
BINOM.INV(trials, probability_s, alpha)
BINOMDIST(number_successes, trials, probability, cumulative)
CHIDIST(x, degrees_freedom)
CHIINV(probability, degrees_freedom)
CHISQ.DIST(x, degrees_freedom, cumulative)
CHISQ.DIST.RT(x, degrees_freedom)
CHISQ.INV(probability, degrees_freedom)
CHISQ.INV.RT(probability, degrees_freedom)
CHISQ.TEST(actual_range, expected_range)
CHITEST(actual_range, expected_range)
CONFIDENCE(alpha, standard_deviation, size)
CONFIDENCE.NORM(alpha, standard_deviation, size)
CONFIDENCE.T(alpha, standard_deviation, size)
CORREL(range1, range2)
COUNT(value1, [value2, ...])
COUNTA(value1, [value2, ...])
COUNTBLANK(range)
COUNTIF(range, criteria)
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, ...])
COVAR(range1, range2)
COVARIANCE.P(range1, range2)
COVARIANCE.S(range1, range2)
CRITBINOM(trials, probability_s, alpha)
DEVSQ(number1, [number2, ...])
EXPON.DIST(x, lambda, cumulative)
EXPONDIST(x, lambda, cumulative)
F.DIST(x, degrees_freedom1, degrees_freedom2, cumulative)
F.DIST.RT(x, degrees_freedom1, degrees_freedom2)
FINV(probability, degrees_freedom1, degrees_freedom2)
F.INV.RT(probability, degrees_freedom1, degrees_freedom2)
F.TEST(array1, array2)
FDIST(x, degrees_freedom1, degrees_freedom2)
FINV(probability, degrees_freedom1, degrees_freedom2)
FISHER(x)
FISHERINV(y)
FORECAST(x, known_ys, known_xs)
FORECAST.LINEAR(x, known_ys, known_xs)
FREQUENCY(data_array, bins_array)
FTEST(array1, array2)
GAMMA(x)
GAMMA.DIST(x, alpha, beta, cumulative)
GAMMA.INV(probability, alpha, beta)
GAMMADIST(x, alpha, beta, cumulative)
GAMMAINV(probability, alpha, beta)
GAMMALN(x)
GAMMALN.PRECISE(x)
GAUSS(z)
GEOMEAN(number1, [number2, ...])
GROWTH(known_ys, [known_xs], [new_xs], [const])
HARMEAN(number1, [number2, ...])
HYPGEOM.DIST(sample_s, number_sample, population_s, number_population, cumulative)
HYPGEOMDIST(sample_s, number_sample, population_s, number_population)
INTERCEPT(y_range, x_range)
KURT(number1, [number2, ...])
LARGE(array, k)
LINEST(known_ys, [known_xs], [const], [stats])
LOGEST(known_ys, [known_xs], [const], [stats])
LOGINV(probability, mean, standard_dev)
LOGNORM.DIST(x, mean, standard_dev, cumulative)
LOGNORM.INV(probability, mean, standard_dev)
LOGNORMDIST(x, mean, standard_dev)
MAX(number1, [number2, ...])
MAXA(value1, [value2, ...])
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
MEDIAN(number1, [number2, ...])
MIN(number1, [number2, ...])
MINA(value1, [value2, ...])
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
MODE(number1, [number2, ...])
MODE.MULT(number1, [number2, ...])
MODE.SNGL(number1, [number2, ...])
NEGBINOM.DIST(number_f, number_s, probability_s, cumulative)
NEGBINOMDIST(number_f, number_s, probability_s)
NORM.DIST(x, mean, standard_dev, cumulative)
NORM.INV(probability, mean, standard_dev)
NORM.S.DIST(z,cumulative)
NORM.S.INV(probability)
NORMDIST(x, mean, standard_dev, cumulative)
NORMINV(probability, mean, standard_dev)
NORMSDIST(z)
NORMSINV(probability)
PEARSON(array1, array2)
PERCENTILE(array, k)
PERCENTILE.EXC(array, k)
PERCENTILE.INC(array, k)
PERCENTRANK(array, x, [significance])
PERCENTRANK.INC(array, x, [significance])
PERMUT(number, number_chosen)
PERMUTATIONA(number, number_chosen)
PHI(x)
POISSON(x, mean, cumulative)
POISSON.DIST(x, mean, cumulative)
PROB(x_range, prob_range, lower_limit, [upper_limit])
QUARTILE(array, quart)
QUARTILE.EXC(array, quart)
QUARTILE.INC(array, quart)
RANK(number, range, [order])
RANK.AVG(number, range, [order])
RANK.EQ(number, range, [order])
RSQ(y_array, x_array)
SKEW(number1, [number2, ...])
SKEW.P(number1, [number2, ...])
SLOPE(y_range, x_range)
SMALL(array, k)
STANDARDIZE(x, mean, standard_dev)
STDEV(number1, [number2, ...])
STDEV.P(number1, [number2, ...])
STDEV.S(number1, [number2, ...])
STDEVA(value1, [value2, ...])
STDEVP(number1, [number2, ...])
STDEVPA(value1, [value2, ...])
STEYX(y_range, x_range)
T.DIST(x, degrees_freedom, cumulative)
T.DIST.2T(x, degrees_freedom)
T.DIST.RT(x, degrees_freedom)
T.INV(probability, degrees_freedom)
T.INV.2T(probability, degrees_freedom)
T.TEST(array1, array2, tails, type)
TDIST(x, degrees_freedom, tails)
TINV(probability, degrees_freedom)
TREND(known_ys, [known_xs], [new_xs], [const])
TRIMMEAN(array, percent)
TTEST(array1, array2, tails, type)
VAR(number1, [number2, ...])
VAR.P(number1, [number2, ...])
VAR.S(number1, [number2, ...])
VARA(value1, [value2, ...])
VARP(number1, [number2, ...])
VARPA(value1, [value2, ...])
WEIBULL(x, alpha, beta, cumulative)
WEIBULL.DIST(x, alpha, beta, cumulative)
Z.TEST(array, x, [sigma])
ZTEST(array, x, [sigma])


Text functions

[ ] indicates optional parameters

CHAR(number)
CLEAN(text)
CODE(text)
CONCAT(text1, [text2, ...])
CONCATENATE(text1, [text2, ...])
DOLLAR(number, [decimals])
EXACT(text1, text2)
FIND(find_text, within_text, [start_num])
FIXED(number, [decimals], [no_commas])
LEFT(text, [num_chars])
LEN(text)
LOWER(text)
MID(text, start_num, num_chars)
NUMBERVALUE(text, [decimal_separator], [group_separator])
PROPER(text)
REPLACE(old_text, start_num, num_chars, new_text)
REPT(text, number_times)
RIGHT(text, [num_chars])
SEARCH(find_text, within_text, [start_num])
SUBSTITUTE(text, old_text, new_text, [instance_num])
T(value)
TEXT(value, format_text)
TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])
TRIM(text)
UNICHAR(number)
UNICODE(text)
UPPER(text)
VALUE(text)


Macro control functions

[ ] indicates optional parameters

ARGUMENT([name_text], [type], [reference])
BREAK()
ELSE()
ELSE.IF(logical_test)
END.IF()
FOR(counter_text, start_num, end_num, [step_num])
FOR.CELL(ref_name, [area_ref], [skip_blanks])
GOTO(reference)
HALT([cancel_close])
IF(logical_test)
NEXT()
RESTART([level_num])
RESULT([type_num])
RETURN([value])
SET.NAME(name_text, [value])
SET.VALUE(reference, values)
VOLATILE([logical])
WAIT([serial_number])
WHILE(logical_test)


Macro command functions

[ ] indicates optional parameters

ABSREF(ref_text, reference)
ACTIVATE([window_text], [pane_num])
ACTIVATE.NEXT([workbook_text])
ACTIVATE.PREV([workbook_text])
ACTIVE.CELL()
ALERT(message_text, [type_num], [help_ref])
ALIGNMENT([horiz_align], [wrap], [vert_align], [orientation], [add_indent])
ASSIGN.TO.OBJECT([macro_ref])
ATTACH.TEXT(attach_to_num, [series_num], [point_num])
AXES([x_primary], [y_primary], [x_secondary], [y_secondary])
BEEP([tone_num])
BORDER([outline], [left], [right], [top], [bottom], [shade], [outline_color], [left_color], [right_color], [top_color], [bottom_color])
BRING.TO.FRONT()
CALCULATE.DOCUMENT()
CALCULATE.NOW()
CALLER()
CANCEL.KEY(enable_logical, [macro_ref])
CELL.PROTECTION([locked], [hidden])
CHECKBOX.PROPERTIES([value], [link], [accel_text], [accel_text2], [3d_shading])
CLEAR([type_num])
COLOR.PALETTE(file_text)
COLUMN.WIDTH([width_num], [reference], [standard], [type_num], [standard_num])
COPY([from_reference], [to_reference])
CREATE.OBJECT(obj_type, ref1, [x_offset1], [y_offset1], ref2, [x_offset2], [y_offset2], [text], [fill], [editable])
CREATE.OBJECT(obj_type, ref1, [x_offset1], [y_offset1], ref2, [x_offset2], [y_offset2], array, [fill])
CUT([from_reference], [to_reference])
DATA.FORM()
DATA.LABEL([show_option], [auto_text], [show_key])
DATA.SERIES([rowcol], [type_num], [date_num], [step_value], [stop_value], [trend])
DEFINE.NAME(name_text, [refers_to], [macro_type], [shortcut_text], [hidden], [category], [local])
DELETE.FORMAT(format_text)
DELETE.NAME(name_text)
DEREF(reference)
DIRECTORY([path_text])
DOCUMENTS([type_num], [match_text])
DUPLICATE()
ECHO([logical])
EDIT.COLOR(color_num, [red_value], [green_value], [blue_value])
EDIT.DELETE([shift_num])
EDIT.SERIES([series_num], [name_ref], [x_ref], [y_ref], [z_ref], [plot_order])
ENABLE.OBJECT([object_id], [enable_logical])
ERROR(enable_logical, [macro_ref])
EVALUATE(formula_text)
EXEC(program_text, [window_num])
EXTEND.POLYGON(array)
FCLOSE(file_num)
FILE.CLOSE([save_logical], [route_logical])
FILE.DELETE(file_text)
FILES([directory_text])
FILL.DOWN()
FILL.LEFT()
FILL.RIGHT()
FILL.UP()
FILTER([field_num], [criteria1], [operation], [criteria2])
FILTER.ADVANCED(operation, list_ref, [criteria_ref], [copy_ref], [unique])
FILTER.SHOW.ALL()
FONT.PROPERTIES([font], [font_style], [size], [strikethrough], [superscript], [subscript], [outline], [shadow], [underline], [color], [normal], [background], [start_char], [char_count])
FOPEN(file_text, [access_num])
FORMAT.LEGEND(position_num)
FORMAT.MOVE([x_offset], [y_offset], [reference])
FORMAT.NUMBER(format_text)
FORMAT.SHAPE(vertex_num, insert, [reference], [x_offset], [y_offset])
FORMAT.TEXT([x_align], [y_align], [orient_num], [auto_text], [auto_size], [show_key], [show_value], [add_indent])
FORMULA(formula_text, [reference])
FORMULA.ARRAY(formula_text, [reference])
FORMULA.CONVERT(formula_text, from_a1, [to_a1], [to_ref_type], [rel_to_ref])
FORMULA.FILL(formula_text, [reference])
FORMULA.FIND(text, in_num, at_num, by_num, [dir_num], [match_case])
FORMULA.FIND.NEXT()
FORMULA.FIND.PREV()
FORMULA.GOTO([reference], [corner])
FORMULA.REPLACE(find_text, replace_text, [look_at], [look_by], [active_cell], [match_case])
FPOS(file_num, [position_num])
FREAD(file_num, num_chars)
FREADLN(file_num)
FREEZE.PANES([logical], [col_split], [row_split])
FSIZE(file_num)
FULL.SCREEN([logical])
FWRITE(file_num, text)
FWRITELN(file_num, text)
GALLERY.AREA(type_num, [delete_overlay])
GALLERY.BAR(type_num, [delete_overlay])
GALLERY.COLUMN(type_num, [delete_overlay])
GALLERY.DOUGHNUT(type_num, [delete_overlay])
GALLERY.LINE(type_num, [delete_overlay])
GALLERY.PIE(type_num, [delete_overlay])
GALLERY.RADAR(type_num, [delete_overlay])
GALLERY.SCATTER(type_num, [delete_overlay])
GET.CELL(type_num, [reference])
GET.DOCUMENT(type_num, [name_text])
GET.FORMULA(reference)
GET.NAME(name_text, [info_type])
GET.NOTE([cell_ref], [start_char], [num_chars])
GET.OBJECT(type_num, [object_id], [start_num], [count_num], [item_index])
GET.WINDOW(type_num, [window_text])
GET.WORKBOOK(type_num, [name_text])
GET.WORKSPACE(type_num)
GOAL.SEEK(target_cell, target_value, variable_cell)
GRIDLINES([x_major], [x_minor], [y_major], [y_minor], [z_major], [z_minor], [2D_effect])
HIDE.OBJECT([object_id], [hide_logical])
HLINE([num_columns])
HPAGE([num_windows])
HSCROLL(position, [col_logical])
INPUT(message_text, [type_num], [title_text], [default], [x_pos], [y_pos], [help_ref])
INSERT([shift_num])
INSERT.TITLE([chart], [y_primary], [x_primary], [y_secondary], [x_secondary])
LABEL.PROPERTIES([accel_text], [accel_text2], [3d_shading])
LEGEND([logical])
LIST.NAMES()
LISTBOX.PROPERTIES([range], [link], [drop_size], [multi_select], [3d_shading])
MESSAGE(logical, [text])
NAMES([document_text], [type_num], [match_text])
NEW([type_num], [xy_series], [add_logical])
NOTE([add_text], [cell_ref], [start_char], [num_chars])
OBJECT.PROPERTIES([placement_type], [print_object])
ON.DOUBLECLICK([sheet_text], [macro_text])
ON.ENTRY([sheet_text], [macro_text])
ON.SHEET([sheet_text], [macro_text], [activate_logical])
OPEN(file_text, [update_links], [read_only], [format], [prot_pwd], [write_res_pwd], [ignore_rorec], [file_origin], [custom_delimit], [add_logical], [editable], [file_access], [notify_logical], [converter])
OPEN.DIALOG([file_filter], [button_text], [title], [filter_index])
OPTIONS.VIEW([formula], [status], [notes], [show_info], [object_num], [page_breaks], [formulas], [gridlines], [color_num], [headings], [outline], [zeros], [hor_scroll], [vert_scroll], [sheet_tabs])
PASTE([to_reference])
PASTE.SPECIAL([paste_num], [operation_num], [skip_blanks], [transpose])
PATTERNS([apattern], [afore], [aback])
PATTERNS([lauto], [lstyle], [lcolor], [lwt], [hwidth], [hlength], [htype])
PATTERNS([bauto], [bstyle], [bcolor], [bwt], [shadow], [aauto], [apattern], [afore], [aback], [rounded], [newui])
PATTERNS([bauto], [bstyle], [bcolor], [bwt], [shadow], [aauto], [apattern], [afore], [aback], [invert], [apply], [new_fill])
PATTERNS([lauto], [lstyle], [lcolor], [lwt], [mauto], [mstyle], [mfore], [mback], [apply], [smooth])
PROTECT.DOCUMENT([contents], [windows], [password], [objects], [scenarios])
QUIT()
REFTEXT(reference, [a1])
RELREF(reference, rel_to_ref)
ROW.HEIGHT([height_num], [reference], [standard_height], [type_num])
SAVE()
SAVE.AS([document_text], [type_num], [prot_pwd], [backup], [write_res_pwd], [read_only_rec])
SAVE.AS?([document_text], [type_num], [prot_pwd], [backup], [write_res_pwd], [read_only_rec])
SAVE.COPY.AS([document_text])
SAVE.DIALOG([init_filename], [title], [button_text], [file_filter], [filter_index])
SCALE([min_num], [max_num], [major], [minor], [cross], [logarithmic], [reverse], [max])
SCROLLBAR.PROPERTIES([value], [min], [max], [inc], [page], [link], [3d_shading])
SELECT([selection], [active_cell])
SELECT([object_id], [replace])
SELECT([item_text], [single_point])
SELECT.END(direction_num)
SELECT.LAST.CELL()
SELECTION()
SEND.TO.BACK()
SET.CONTROL.VALUE([value])
SHOW.ACTIVE.CELL()
SORT([orientation], [key1], [order1], [key2], [order2], [key3], [order3], [header], [custom], [case])
STANDARD.WIDTH([standard_num])
TEXT.BOX(add_text, [object_id], [start_num], [num_chars])
TEXT.TO.COLUMNS([destination_ref], [data_type], [text_delim], [consecutive_delim], [tab], [semicolon], [comma], [space], [other], [other_char], [field_info])
TEXTREF(text, [a1])
UNDO()
VLINE([num_rows])
VPAGE([num_windows])
VSCROLL(position, [row_logical])
WORKBOOK.ACTIVATE(sheet_name)
WORKBOOK.DELETE([sheet_name])
WORKBOOK.HIDE([sheet_text], [very_hidden])
WORKBOOK.INSERT([type_num])
WORKBOOK.NAME(old_sheet_name, new_sheet_name)
WORKBOOK.NEXT()
WORKBOOK.PREV()
WORKBOOK.UNHIDE([sheet_text])
ZOOM([magnification])


DDE/external functions

[ ] indicates optional parameters

CALL(module_text, procedure, type_text, [argument1, ...])


Web functions

[ ] indicates optional parameters

ENCODEURL(text)
FILTERXML(xml, xpath)
WEBSERVICE(url)


Database functions

DAVERAGE(database_range, field, criteria_range)

Returns the average of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the average.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DCOUNT(database_range, [field], criteria_range)

Returns the count of the number of rows in the database that meet the selection criteria and where the specified field contains a number.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values that you want to count. If this parameter is omitted then the function will return the number of database rows that meet the selection criteria.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DCOUNTA(database_range, [field], criteria_range)

Returns the count of the number of rows in the database that meet the selection criteria and where the specified field is not blank.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values that you want to count. If this parameter is omitted then the function will return the number of database rows that meet the selection criteria.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DGET(database_range, field, criteria_range)

Returns the value of a specified field from the row in the database range that meets the selection criteria. There should be one and only one row that meets the selection criteria. If no rows meet the criteria then the error #VALUE! is returned. If more than one row meets the criteria then the error #NUM! is returned.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the value that you want to retrieve.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DMAX(database_range, field, criteria_range)

Returns the maximum of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the maximum.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DMIN(database_range, field, criteria_range)

Returns the minimum of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the minimum.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DPRODUCT(database_range, field, criteria_range)

Returns the product of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values that you want to multiply together.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DSTDEV(database_range, field, criteria_range)

Returns the standard deviation (based on a population sample) of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the standard deviation.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DSTDEVP(database_range, field, criteria_range)

Returns the standard deviation (based on the entire population) of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the standard deviation.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DSUM(database_range, field, criteria_range)

Returns the sum of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the sum.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DVAR(database_range, field, criteria_range)

Returns the variance (based on a population sample) of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the variance.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


DVARP(database_range, field, criteria_range)

Returns the variance (based on the entire population) of the values of a specified field in a database range.

database_rangeA reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this.
fieldThe column number or column heading name of the values of which you want to find the variance.
criteria_rangeA reference to cells that contain the criteria that you want to use when selecting records from the database to process. You can specify the same range as used for the database_range parameter if you want to select all the rows.


Date/time functions

DATE(year, month, day)

Returns the date value corresponding to a specified year, month and day. Note that only dates from 1900 onwards can be used.

yearThe year number. If you enter a number less than 1900 then 1900 will be added to the value.
monthThe month number. Preferably between 1 and 12.
dayThe day number. Preferably between 1 and 31.


DATEDIF(date1, date2, units)

Returns the difference between two dates in the units that you specify.

date1The 'from' date.
date2The 'to' date. This should be greater than or equal to the 'from' date.
unitsThe units in which you want the difference expressed. The possible values are:
"y"The difference in years.
"m"The difference in months.
"d"The difference in days.
"ym"The difference in months, dropping any whole years (the result will always be between 0 and 11).
"yd"The difference in days, dropping any whole years (the result will always be between 0 and 365).
"md"The difference in days, dropping any whole months (the result will always be between 0 and 30).


DATEVALUE(date_text)

Returns the date value that corresponds to a date in text form. Note that only dates from 1900 onwards can be used. You should not need to use this function, as dates are automatically converted from text to date values when they are used in formulas.

date_textThe date that you want to convert. It can contain date separators and/or a month name. If only the day and month is supplied then the year will default to the current year.


DAY(serial_number)

Returns the day number (between 1 and 31) from the specified date value.

serial_numberThe date value from which you want to extract the day number.


DAYS(end_date, start_date)

Returns the number of days between two dates.

end_dateThe 'to' date.
start_dateThe 'from' date.


DAYS360(start_date, end_date, [method])

Returns the number of days between two dates using a 360-day year (30 day months).

start_dateThe 'from' date.
end_dateThe 'to' date.
methodIndicates how to deal with end dates that are the 31st of the month (the start date will always be moved from the 31st to the 30th). The options are:
FALSEUS (NASD) method (if start date is less than the 30th then set end date to 1st of next month, else set end date to 30th).
TRUEEuropean method (end date moved form 31st to 30th).
If this parameter is omitted it defaults to FALSE.


EDATE(start_date, months)

Returns the date that is the start date plus or minus a number of months.

start_dateThe 'from' date.
monthsThe number of months to be added or subtracted.


EOMONTH(start_date, months)

Returns the date that is the last day of the month calculated from the start date plus or minus a number of months.

start_dateThe 'from' date.
monthsThe number of months to be added or subtracted.


HOUR(serial_number)

Returns the hour number (between 0 and 23) from the specified date/time value.

serial_numberThe date/time value from which you want to extract the hour number.


ISOWEEKNUM(date)

Returns the ISO week number for the specified date.

dateThe date for which you want the ISO week number.


MINUTE(serial_number)

Returns the minute number (between 0 and 59) from the specified date/time value.

serial_numberThe date/time value from which you want to extract the minute number.


MONTH(serial_number)

Returns the month number (between 1 and 12) from the specified date/time value.

serial_numberThe date/time value from which you want to extract the month number.


NETWORKDAYS(start_date, end_date, [holidays])

Returns the number of working days (weekdays) between the start and end dates, excluding any holidays.

start_dateThe 'from' date.
end_dateThe 'to' date.
holidaysA date or array of dates or a reference to a list of dates that are to be treated as non-working days.


NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Returns the number of working days (weekdays) between the start and end dates, excluding any holidays.

start_dateThe 'from' date.
end_dateThe 'to' date.
weekendA seven-character string that indicates which days of the week from Monday to Sunday are not working days, or a number that refers to such a string. The possible number values are:
1"0000011"
2"1000001"
3"1100000"
4"0110000"
5"0011000"
6"0001100"
7"0000110"
11"0000001"
12"1000000"
13"0100000"
14"0010000"
15"0001000"
16"0000100"
17"0000010"
If this parameter is omitted it defaults to 1.
holidaysA date or array of dates or a reference to a list of dates that are to be treated as non-working days.


NOW()

Returns the serial number corresponding to the current date and time.



SECOND(serial_number)

Returns the second number (between 0 and 59) from the specified date/time value.

serial_numberThe date/time value from which you want to extract the second number.


TIME(hour, minute, second)

Returns the serial number corresponding to a specified hour, minute and second.

hourThe hour number. Preferably between 0 and 23.
minuteThe minute number. Preferably between 0 and 59.
secondThe second number. Preferably between 0 and 59.


TIMEVALUE(time_text)

Returns the time value that corresponds to a time in text form. You should not need to use this function, as times are automatically converted from text to time values when they are used in formulas.

time_textThe time that you want to convert. It can contain time separators and/or AM/PM designators. If seconds or minutes and seconds are not specified then they will default to zero.


TODAY()

Returns the serial number corresponding to the current date.



WEEKDAY(serial_number, [return_type])

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

serial_numberThe date/time value from which you want the day of the week.
return_typeThe range of values returned and what they represent. The possible values are:
1From 1 to 7, where 1 = Sunday and 7 = Saturday.
2From 1 to 7, where 1 = Monday and 7 = Sunday.
3From 0 to 6, where 0 = Monday and 6 = Sunday.
If this parameter is omitted it defaults to 1.


WEEKNUM(serial_number, [return_type])

Returns the week of the year of a given date.

serial_numberThe date/time value from which you want the week number.
return_typeThe method used to calculate the week number. The possible values are:
0International Standard (ISO) week numbers. Weeks begin on Monday. Week 1 is the first week where 4 or more days are in the specified year.
1Microsoft week numbers. Weeks begin on Sunday. Week 1 is the week that contains January 1st.
2Microsoft week numbers. Weeks begin on Monday. Week 1 is the week that contains January 1st.
If this parameter is omitted it defaults to 1.


WORKDAY(start_date, number_days, [holidays])

Returns the working day that is the start date plus or minus a number of working days (weekdays), excluding any holidays.

start_dateThe 'from' date.
number_daysThe number of working days to add or subtract.
holidaysA date or array of dates or a reference to a list of dates that are to be treated as non-working days.


WORKDAY.INTL(start_date, number_days, [weekend], [holidays])

Returns the working day that is the start date plus or minus a number of working days (weekdays), excluding any holidays.

start_dateThe 'from' date.
number_daysThe number of working days to add or subtract.
weekendA seven-character string that indicates which days of the week from Monday to Sunday are not working days, or a number that refers to such a string. The possible number values are:
1"0000011"
2"1000001"
3"1100000"
4"0110000"
5"0011000"
6"0001100"
7"0000110"
11"0000001"
12"1000000"
13"0100000"
14"0010000"
15"0001000"
16"0000100"
17"0000010"
If this parameter is omitted it defaults to 1.
holidaysA date or array of dates or a reference to a list of dates that are to be treated as non-working days.


YEAR(serial_number)

Returns the year number (greater than or equal to 1900) from the specified date/time value.

serial_numberThe date/time value from which you want to extract the year number.


YEARFRAC(start_date, end_date, [basis])

Returns the fraction of a year represented by the difference between two dates.

start_dateThe 'from' date.
end_dateThe 'to' date.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


Engineering functions

BESSELI(x, n)

Returns the value of the modified Bessel function.

xThe value at which you want to evaluate the function.
nThe order of the Bessel function.


BESSELJ(x, n)

Returns the value of the Bessel function.

xThe value at which you want to evaluate the function.
nThe order of the Bessel function.


BESSELK(x, n)

Returns the value of the modified Bessel function.

xThe value at which you want to evaluate the function.
nThe order of the Bessel function.


BESSELY(x, n)

Returns the value of the Bessel function.

xThe value at which you want to evaluate the function.
nThe order of the Bessel function.


BIN2DEC(number)

Returns the decimal equivalent of a binary number.

numberThe binary number to be converted. It can be a number or a string up to 10 (binary) digits long.


BIN2HEX(number, [places])

Returns a string that is the hexadecimal equivalent of a binary number.

numberThe binary number to be converted. It can be a number or a string up to 10 (binary) digits long.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


BIN2OCT(number, [places])

Returns a string that is the octal equivalent of a binary number.

numberThe binary number to be converted. It can be a number or a string up to 10 (binary) digits long.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


BITAND(number1, number2)

Returns the bitwise AND of two numbers.

number1The first number whose bits are to be compared.
number2The second number whose bits are to be compared.


BITLSHIFT(number, shift_amount)

Returns the number shifted left by the specified number of bits.

numberThe number whose bits are to be shifted.
shift_amountThe number of places by which to shift the bits.


BITOR(number1, number2)

Returns the bitwise OR of two numbers.

number1The first number whose bits are to be compared.
number2The second number whose bits are to be compared.


BITRSHIFT(number, shift_amount)

Returns the number shifted right by the specified number of bits.

numberThe number whose bits are to be shifted.
shift_amountThe number of places by which to shift the bits.


BITXOR(number1, number2)

Returns the bitwise XOR of two numbers.

number1The first number whose bits are to be compared.
number2The second number whose bits are to be compared.


COMPLEX(real_num, imag_num, [suffix])

Returns a string representing an complex number made up of the specified real and imaginary parts.

real_numThe real part of the complex number.
imag_numThe imaginary part of the complex number.
suffixThe suffix to be used for the imaginary part. Should be "i" or "j". If this parameter is omitted it defaults to "i".


CONVERT(number, from_unit, to_unit)

Returns a value converted from the 'from' units to the 'to' units.

numberThe number to be converted.
from_unitThe units to convert from.
to_unitThe units to convert to.
The from and to units must belong to the same group. The values are case-sensitive and must be entered exactly as shown. The possible values are:
Weight and mass
"Eg"exagrams (1E18)
"Pg"petagrams (1E15)
"Tg"teragrams (1E12)
"Gg"gigagrams (1E9)
"Mg"megagrams (1E6)
"kg"kilograms (1E3)
"hg"hectograms (1E2)
"eg"dekaograms (1E1)
"g"grams (1)
"dg"decigrams (1E-1)
"cg"centigrams (1E-2)
"mg"milligrams (1E-3)
"ug"micrograms (1E-6)
"ng"nanograms (1E-9)
"pg"picograms (1E-12)
"fg"femtograms (1E-15)
"ag"attograms (1E-18)
"Eu"exa-(atomic mass unit)
"Pu"peta-(atomic mass unit)
"Tu"tera-(atomic mass unit)
"Gu"giga-(atomic mass unit)
"Mu"mega-(atomic mass unit)
"ku"kilo-(atomic mass unit)
"hu"hecto-(atomic mass unit)
"eu"dekao-(atomic mass unit)
"u"atomic mass unit
"du"deci-(atomic mass unit)
"cu"centi-(atomic mass unit)
"mu"milli-(atomic mass unit)
"uu"micro-(atomic mass unit)
"nu"nano-(atomic mass unit)
"pu"pico-(atomic mass unit)
"fu"femto-(atomic mass unit)
"au"atto-(atomic mass unit)
"ozm"ounce mass (avoirdupois)
"lbm"pound mass (avoirdupois)
"sg"slug
Distance
"Em"exameters
"Pm"petameters
"Tm"terameters
"Gm"gigameters
"Mm"megameters
"km"kilometers
"hm"hectometers
"em"dekaometers
"m"meters
"dm"decimeters
"cm"centimeters
"mm"millimeters
"um"micrometers
"nm"nanometers
"pm"picometers
"fm"femtometers
"am"attometers
"Eang"exa-(Angstrom units)
"Pang"peta--(Angstrom units)
"Tang"tera-(Angstrom units)
"Gang"giga-(Angstrom units)
"Mang"mega-(Angstrom units)
"kang"kilo-(Angstrom units)
"hang"hecto-(Angstrom units)
"eang"dekao-(Angstrom units)
"ang"Angstrom units
"dang"deci-(Angstrom units)
"cang"centi-(Angstrom units)
"mang"milli-(Angstrom units)
"uang"micro-(Angstrom units)
"nang"nano-(Angstrom units)
"pang"pico-(Angstrom units)
"fang"femto-(Angstrom units)
"aang"atto-(Angstrom units)
"Pica"pica (1/72 inch)
"in"inch
"ft"foot
"yd"yard
"mi"statute mile
"Nmi"nautical mile
Time
"Esec"exaseconds
"Psec"petaseconds
"Tsec"teraseconds
"Gsec"gigaseconds
"Msec"megaseconds
"ksec"kiloseconds
"hsec"hectoseconds
"esec"dekaoseconds
"sec"seconds
"dsec"deciseconds
"csec"centiseconds
"msec"milliseconds
"usec"microseconds
"nsec"nanoseconds
"psec"picoseconds
"fsec"femtoseconds
"asec"attoseconds
"mn"minutes
"hr"hours
"day"days
"yr"years
Pressure
"EPa"exapascal
"PPa"petapascal
"TPa"terapascal
"GPa"gigapascal
"MPa"megapascal
"kPa"kilopascal
"hPa"hectopascal
"ePa"dekaopascal
"Pa"pascal
"dPa"decipascal
"cPa"centipascal
"mPa"millipascal
"uPa"micropascal
"nPa"nanopascal
"pPa"picopascal
"fPa"femtopascal
"aPa"attopascal
"EmmHg"exa-(mm of mercury)
"PmmHg"peta-(mm of mercury)
"TmmHg"tera-(mm of mercury)
"GmmHg"giga-(mm of mercury)
"MmmHg"mega-(mm of mercury)
"kmmHg"kilo-(mm of mercury)
"hmmHg"hecto-(mm of mercury)
"emmHg"dekao-(mm of mercury)
"mmHg"mm of mercury
"dmmHg"deci-(mm of mercury)
"cmmHg"centi-(mm of mercury)
"mmmHg"milli-(mm of mercury)
"ummHg"micro-(mm of mercury)
"nmmHg"nano-(mm of mercury)
"pmmHg"pico-(mm of mercury)
"fmmHg"femto-(mm of mercury)
"ammHg"atto-(mm of mercury)
"Eatm"exaatmospheres
"Patm"petaatmospheres
"Tatm"teraatmospheres
"Gatm"gigaatmospheres
"Matm"megaatmosspheres
"katm"kiloatmospheres
"hatm"hectoatmospheres
"eatm"dekaoatmospheres
"atm"atmosphere
"datm"deciatmospheres
"catm"centiatmospheres
"matm"milliatmospheres
"uatm"microatmospheres
"natm"nanoatmospheres
"patm"picoatmospheres
"fatm"femtoatmospheres
"aatm"attoatmospheres
Force
"EN"exanewtons
"PN"petanewtons
"TN"teranewtons
"GN"giganewtons
"MN"meganewtons
"kN"kilonewtons
"hN"hectonewtons
"eN"dekaonewtons
"N"newtons
"dN"decinewtons
"cN"centinewtons
"mN"millinewtons
"uN"micronewtons
"nN"nanonewtons
"pN"piconewtons
"fN"femtonewtons
"aN"attonewtons
"Edyn"exadynes
"Pdyn"petadynes
"Tdyn"teradynes
"Gdyn"gigadynes
"Mdyn"megadynes
"kdyn"kilodynes
"hdyn"hectodynes
"edyn"dekaodynes
"dyn"dynes
"ddyn"decidynes
"cdyn"centidynes
"mdyn"millidynes
"udyn"microdynes
"ndyn"nanodynes
"pdyn"picodynes
"fdyn"femtodynes
"adyn"attodynes
"lbf"pound force
Energy
"EJ"exajoules
"PJ"petajoules
"TJ"terajoules
"GJ"gigajoules
"MJ"megajoules
"kJ"kilojoules
"hJ"hectojoules
"eJ"dekaojoules
"J"joules
"dJ"decijoules
"cJ"centijoules
"mJ"millijoules
"uJ"microjoules
"nJ"nanojoules
"pJ"picojoules
"fJ"femtojoules
"aJ"attojoules
"Ee"exaergs
"Pe"petaergs
"Te"teraergs
"Ge"gigaergs
"Me"megaergs
"ke"kiloergs
"he"hectoergs
"ee"dekaoergs
"e"ergs
"de"deciergs
"ce"centiergs
"me"milliergs
"ue"microergs
"ne"nanoergs
"pe"picoergs
"fe"femtoergs
"ae"attoergs
"Ec"exa-(thermodynamic calories)
"Pc"peta-(thermodynamic calories)
"Tc"tera-(thermodynamic calories)
"Gc"giga-(thermodynamic calories)
"Mc"mega-(thermodynamic calories)
"kc"kilo-(thermodynamic calories)
"hc"hecto-(thermodynamic calories)
"ec"dekao-(thermodynamic calories)
"c"thermodynamic calories
"dc"deci-(thermodynamic calories)
"cc"centi-(thermodynamic calories)
"mc"milli-(thermodynamic calories)
"uc"micro-(thermodynamic calories)
"nc"nano-(thermodynamic calories)
"pc"pico-(thermodynamic calories)
"fc"femto-(thermodynamic calories)
"ac"atto-(thermodynamic calories)
"Ecal"exa-(IT calories)
"Pcal"peta-(IT calories)
"Tcal"tera-(IT calories)
"Gcal"giga-(IT calories)
"Mcal"mega-(IT calories)
"kcal"kilo-(IT calories)
"hcal"hecto-(IT calories)
"ecal"dekao-(IT calories)
"cal"IT calories
"dcal"deci-(IT calories)
"ccal"centi-(IT calories)
"mcal"milli-(IT calories)
"ucal"micro-(IT calories)
"ncal"nano-(IT calories)
"pcal"pico-(IT calories)
"fcal"femto-(IT calories)
"acal"atto-(IT calories)
"EeV"exa-(electron volts)
"PeV"peta-(electron volts)
"TeV"tera-(electron volts)
"GeV"giga-(electron volts)
"MeV"mega-(electron volts)
"keV"kilo-(electron volts)
"heV"hecto-(electron volts)
"eeV"dekao-(electron volts)
"eV"electron volts
"deV"deci-(electron volts)
"ceV"centi-(electron volts)
"meV"milli-(electron volts)
"ueV"micro-(electron volts)
"neV"nano-(electron volts)
"peV"pico-(electron volts)
"feV"femto-(electron volts)
"aeV"atto-(electron volts)
"flb"foot-pound
"BTU"BTU
"EWh"exa-(watt-hour)
"PWh"peta-(watt-hour)
"TWh"tera-(watt-hour)
"GWh"giga-(watt-hour)
"MWh"mega-(watt-hour)
"kWh"kilo-(watt-hour)
"hWh"hecto-(watt-hour)
"eWh"dekao-(watt-hour)
"Wh"watt-hour
"dWh"deci-(watt-hour)
"cWh"centi-(watt-hour)
"mWh"milli-(watt-hour)
"uWh"micro-(watt-hour)
"nWh"nano-(watt-hour)
"pWh"pico-(watt-hour)
"fWh"femto-(watt-hour)
"aWh"atto-(watt-hour)
"HPh"Horsepower-hour
Power
"HP"Horsepower
"EW"exawatt
"PW"petawatt
"TW"terawatt
"GW"gigawatt
"MW"megawatt
"kW"kilowatt
"hW"hectowatt
"eW"dekaowatt
"W"watt
"dW"deciwatt
"cW"centiwatt
"mW"milliwatt
"uW"microwatt
"nW"nanowatt
"pW"picowatt
"fW"femtowatt
"aW"attowatt
Magnetism
"ET"exatesla
"PT"petatesla
"TT"teratesla
"GT"gigatesla
"MT"megatesla
"kT"kilotesla
"hT"hectotesla
"eT"dekaotesla
"T"tesla
"dT"decitesla
"cT"centitesla
"mT"millitesla
"uT"microtesla
"nT"nanotesla
"pT"picotesla
"fT"femtotesla
"aT"attotesla
"Ega"exagauss
"Pga"petagauss
"Tga"teragauss
"Gga"gigagauss
"Mga"megagauss
"kga"kilogauss
"hga"hectogauss
"ega"dekaogauss
"ga"gauss
"dga"decigauss
"cga"centigauss
"mga"milligauss
"uga"microgauss
"nga"nanogauss
"pga"picogauss
"fga"femtogauss
"aga"attogauss
Temperature
"C"celcius
"F"fahrenheit
"K"kelvin
Liquid measure
"tsp"teaspoon
"tbs"tablespoon
"oz"fluid ounce
"cup"cup
"pt"pint
"qt"quart
"gal"gallon
"El"exaliter
"Pl"petaliter
"Tl"teraliter
"Gl"gigaliter
"Ml"megaliter
"kl"kiloliter
"hl"hectoliter
"el"dekaoliter
"l"liter
"dl"deciliter
"cl"centiliter
"ml"milliliter
"ul"microliter
"nl"nanoliter
"pl"picoliter
"fl"femtoliter
"al"attoliter


DEC2BIN(number, [places])

Returns a string that is the binary equivalent of a decimal number.

numberThe decimal number to be converted.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


DEC2HEX(number, [places])

Returns a string that is the hexadecimal equivalent of a decimal number.

numberThe decimal number to be converted.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


DEC2OCT(number, [places])

Returns a string that is the octal equivalent of a decimal number.

numberThe decimal number to be converted.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


DELTA(number1, [number2])

Returns 1 if the numbers are equal, otherwise 0.

number1The first of the numbers to be compared.
number2The second of the numbers to be compared. If this parameter is omitted it defaults to zero.


ERF(lower_limit, [upper_limit])

Returns the integral of the error function between specified limits, or between zero and a specified limit.

lower_limitThe lower limit of the integral.
upper_limitThe upper limit of the integral. If this parameter is omitted integration is done between 0 and lower_limit.


ERF.PRECISE(x)

Returns the integral of the error function between specified limits, or between zero and a specified limit.

xThe lower limit of the integral.


ERFC(x)

Returns the integral of the error function between a specified limit and infinity.

xThe lower limit of the integral.


ERFC.PRECISE(x)

Returns the integral of the error function between a specified limit and infinity.

xThe lower limit of the integral.


GESTEP(number, [step])

Returns 1 if number is greater than or equal to step, otherwise 0.

numberThe first of the numbers to be compared.
stepThe second of the numbers to be compared. If this parameter is omitted it defaults to zero.


HEX2BIN(number, [places])

Returns a string that is the binary equivalent of a hexadecimal number.

numberThe hexadecimal number to be converted.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


HEX2DEC(number)

Returns a number that is the decimal equivalent of a hexadecimal number.

numberThe hexadecimal number to be converted.


HEX2OCT(number, [places])

Returns a string that is the octal equivalent of a hexadecimal number.

numberThe hexadecimal number to be converted.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


IMABS(inumber)

Returns a number that is the modulus of a complex number.

inumberThe complex number of which you want the modulus.


IMAGINARY(inumber)

Returns a number that is the imaginary part of a complex number.

inumberThe complex number of which you want the imaginary part.


IMARGUMENT(inumber)

Returns a number that is the angle (in radians) represented by a complex number.

inumberThe complex number of which you want the argument.


IMCONJUGATE(inumber)

Returns a complex number that is the conjugate of the specified complex number.

inumberThe complex number of which you want the conjugate.


IMCOS(inumber)

Returns a complex number that is the cosine of the specified complex number.

inumberThe complex number of which you want the cosine.


IMCOSH(inumber)

Returns a complex number that is the hyperbolic cosine of the specified complex number.

inumberThe complex number of which you want the hyperbolic cosine.


IMCOT(inumber)

Returns a complex number that is the cotangent of the specified complex number.

inumberThe complex number of which you want the cotangent.


IMCSC(inumber)

Returns a complex number that is the cosecant of the specified complex number.

inumberThe complex number of which you want the cosecant.


IMCSCH(inumber)

Returns a complex number that is the hyperbolic cosecant of the specified complex number.

inumberThe complex number of which you want the hyperbolic cosecant.


IMDIV(inumber1, inumber2)

Returns a complex number that is the quotient of the specified complex numbers.

inumber1The dividend complex number.
inumber2The divisor complex number.


IMEXP(inumber)

Returns a complex number that is the exponential of the specified complex number.

inumberThe complex number of which you want the exponential.


IMLN(inumber)

Returns a complex number that is the natural logarithm of the specified complex number.

inumberThe complex number of which you want the natural logarithm.


IMLOG10(inumber)

Returns a complex number that is the base 10 logarithm of the specified complex number.

inumberThe complex number of which you want the base 10 logarithm.


IMLOG2(inumber)

Returns a complex number that is the base 2 logarithm of the specified complex number.

inumberThe complex number of which you want the base 2 logarithm.


IMPOWER(inumber, power)

Returns a complex number that is the specified complex number raised to a power.

inumberThe complex number that you want to raise to a power.
powerThe power to which you want to raise the complex number.


IMPRODUCT(inumber1, [inumber2, ...])

Returns a complex number that is the product of the specified complex numbers.

inumber1, ...The complex numbers that you want to multiply together.


IMREAL(inumber)

Returns a number that is the real part of a complex number.

inumberThe complex number of which you want the real part.


IMSEC(inumber)

Returns a complex number that is the secant of the specified complex number.

inumberThe complex number of which you want the secant.


IMSECH(inumber)

Returns a complex number that is the hyperbolic secant of the specified complex number.

inumberThe complex number of which you want the hyperbolic secant.


IMSIN(inumber)

Returns a complex number that is the sine of the specified complex number.

inumberThe complex number of which you want the sine.


IMSINH(inumber)

Returns a complex number that is the hyperbolic sine of the specified complex number.

inumberThe complex number of which you want the hyperbolic sine.


IMSQRT(inumber)

Returns a complex number that is the square root of the specified complex number.

inumberThe complex number of which you want the square root.


IMSUB(inumber1, inumber2)

Returns a complex number that is the difference of the specified complex numbers.

inumber1The minuend complex number.
inumber2The subtrahend complex number.


IMSUM(inumber1, [inumber2, ...])

Returns a complex number that is the sum of the specified complex numbers.

inumber1, ...The complex numbers that you want to add together.


IMTAN(inumber)

Returns a complex number that is the tangent of the specified complex number.

inumberThe complex number of which you want the tangent.


OCT2BIN(number, [places])

Returns a string that is the binary equivalent of an octal number.

numberThe octal number to be converted.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


OCT2DEC(number)

Returns a number that is the decimal equivalent of an octal number.

numberThe octal number to be converted.


OCT2HEX(number, [places])

Returns a string that is the hexadecimal equivalent of an octal number.

numberThe octal number to be converted.
placesThe minimum length required in the returned string. If the string is less than this length then it will be padded with leading zeros. If this parameter is omitted it defaults to zero.


Financial functions

ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])

Returns accrued interest for a security that pays periodic interest.

issue_dateThe issue date.
first_interest_dateThe first interest date.
settlement_dateThe settlement date.
rateThe annual coupon rate.
parThe par value.
frequencyThe number of interest payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


ACCRINTM(issue_date, maturity_date, rate, [par], [basis])

Returns accrued interest for a security that pays interest at maturity.

issue_dateThe issue date.
maturity_dateThe maturity date.
rateThe annual coupon rate.
parThe par value. If this parameter is omitted it defaults to 1000.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


AMORDEGRC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])

Returns the depreciation for each accounting period.

costThe cost.
purchase_dateThe purchase date.
first_period_dateThe end date of the first period.
salvageThe salvage value.
periodThe period for which you want to calculate the depreciation.
rateThe depreciation rate.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


AMORLINC(cost, purchase_date, first_period_date, salvage, period, rate, [basis])

Returns the depreciation for each accounting period.

costThe cost.
purchase_dateThe purchase date.
first_period_dateThe end date of the first period.
salvageThe salvage value.
periodThe period for which you want to calculate the depreciation.
rateThe depreciation rate.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


COUPDAYBS(settlement_date, maturity_date, frequency, [basis])

Returns the number of days from the beginning of the coupon period to the settlement date.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


COUPDAYS(settlement_date, maturity_date, frequency, [basis])

Returns the number of days in the coupon period that contains the settlement date.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


COUPDAYSNC(settlement_date, maturity_date, frequency, [basis])

Returns the number of days from the settlement date to the next coupon date.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


COUPNCD(settlement_date, maturity_date, frequency, [basis])

Returns the next coupon date after the settlement date.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


COUPNUM(settlement_date, maturity_date, frequency, [basis])

Returns the number of coupon periods between the settlement date and the maturity date.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


COUPPCD(settlement_date, maturity_date, frequency, [basis])

Returns the coupon date before the settlement date.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


CUMIPMT(rate, nper, pv, start_period, end_period, type)

Returns the cumulative interest paid on a loan in the specified periods.

rateThe interest rate.
nperThe total number of periods.
pvThe present value.
start_periodThe first period number for which to calculate interest.
end_periodThe last period number for which to calculate interest.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.


CUMPRINC(rate, nper, pv, start_period, end_period, type)

Returns the cumulative principal paid on a loan in the specified periods.

rateThe interest rate.
nperThe total number of periods.
pvThe present value.
start_periodThe first period number for which to calculate interest.
end_periodThe last period number for which to calculate interest.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.


DB(cost, salvage, life, period, [month])

Returns the depreciation in a specified period using the fixed declining balance method.

costThe cost.
salvageThe salvage value.
lifeThe total number of periods.
periodThe period number for which to calculate depreciation.
monthThe number of months in the first year. If this parameter is omitted it defaults to 12.


DDB(cost, salvage, life, period, [factor])

Returns the depreciation in a specified period using the double declining balance method.

costThe cost.
salvageThe salvage value.
lifeThe total number of periods.
periodThe period number for which to calculate depreciation.
factorThe rate at which the balance declines. If this parameter is omitted it defaults to 2.


DISC(settlement_date, maturity_date, pr, redemption, [basis])

Returns the discount rate for a security.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
prThe price per $100 value.
redemptionThe redemption per $100 value.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


DOLLARDE(fractional_dollar, fraction)

Returns the decimal equivalent of a dollar price expressed as a fraction.

fractional_dollarThe value expressed as a fraction.
fractionThe fraction denominator.


DOLLARFR(decimal_dollar, fraction)

Returns the fraction equivalent of a dollar price expressed as a decimal.

decimal_dollarThe value expressed as a decimal.
fractionThe fraction denominator.


DURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])

Returns the Macauley duration for a value of $100.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
couponThe interest rate.
yieldThe annual yield rate.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


EFFECT(nominal_rate, npery)

Returns the effective annual interest rate.

nominal_rateThe nominal annual interest rate.
nperyThe number of compound interest payments per year.


FV(rate, term, payment, [pv], [type])

Returns the future value of an investment at a fixed rate.

rateThe interest rate per period.
termThe total number of periods.
paymentThe payment amount each period.
pvThe present value. If this parameter is omitted it is assumed to be zero.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.
If this parameter is omitted it defaults to 0.


FVSCHEDULE(principal, schedule_range)

Returns the future value of an investment at a variable rate.

principalThe initial value of the investment.
schedule_rangeThe list (array or reference) of interest rates to be applied.


INTRATE(settlement_date, maturity_date, investment_amount, redemption_amount, [basis])

Returns the interest rate for a fully invested security.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
investment_amountThe initial value.
redemption_amountThe final value.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


IPMT(rate, period, term, pv, [fv], [type])

Returns the interest payment for a given period.

rateThe interest rate per period.
periodThe period for which you want the interest amount.
termThe total number of periods.
pvThe present value.
fvThe future value. If this parameter is omitted it is assumed to be zero.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.
If this parameter is omitted it defaults to 0.


IRR(values_range, [guess])

Returns the internal rate of return.

values_rangeThe list (array or reference) of payment and income values.
guessThe estimated rate of return. If this parameter is omitted it defaults to 0.1.


ISPMT(rate, period, term, pv)

Returns the interest payment for a given period.

rateThe interest rate per period.
periodThe period for which you want the interest amount.
termThe total number of periods.
pvThe present value.


MDURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])

Returns the modified Macauley duration for a value of $100.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
couponThe interest rate.
yieldThe annual yield rate.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


MIRR(values_range, finance_rate, reinvest_rate)

Returns the modified internal rate of return.

values_rangeThe list (array or reference) of payment and income values.
finance_rateThe interest rate on the payment values.
reinvest_rateThe interest rate on the income values.


NOMINAL(effect_rate, npery)

Returns the nominal annual interest rate.

effect_rateThe effective annual interest rate.
nperyThe number of compound interest payments per year.


NPER(rate, payment, pv, [fv], [type])

Returns the number of periods required for an investment.

rateThe interest rate per period.
paymentThe payment amount per period.
pvThe present value.
fvThe future value. If this parameter is omitted it defaults to 0.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.
If this parameter is omitted it defaults to 0.


NPV(rate, value1, [value2, ...])

Returns the net present value of an investment.

rateThe discount rate per period.
value1, ...The payment and income amounts.


ODDFPRICE(settlement_date, maturity_date, issue_date, first_coupon_date, rate, yield, redemption, frequency, [basis])

Returns the price per $100 face value of a security having an odd (short or long) first period.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
issue_dateThe issue date.
first_coupon_dateThe first coupon date.
rateThe annual coupon rate.
yieldThe annual yield rate.
redemptionThe redemption value per $100.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


ODDFYIELD(settlement_date, maturity_date, issue_date, first_coupon_date, rate, price, redemption, frequency, [basis])

Returns the yield of a security having an odd (short or long) first period.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
issue_dateThe issue date.
first_coupon_dateThe first coupon date.
rateThe annual coupon rate.
priceThe price per $100.
redemptionThe redemption value per $100.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


ODDLPRICE(settlement_date, maturity_date, last_coupon_date, rate, yield, redemption, frequency, [basis])

Returns the price per $100 face value of a security having an odd (short or long) last period.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
last_coupon_dateThe last coupon date.
rateThe annual coupon rate.
yieldThe annual yield rate.
redemptionThe redemption value per $100.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


ODDLYIELD(settlement_date, maturity_date, last_coupon_date, rate, price, redemption, frequency, [basis])

Returns the yield of a security having an odd (short or long) last period.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
last_coupon_dateThe last coupon date.
rateThe annual coupon rate.
priceThe price per $100.
redemptionThe redemption value per $100.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


PDURATION(rate, pv, fv)

Returns the number of periods required for an investment to reach a specified value.

rateThe interest rate per period.
pvThe present value of the investment.
fvThe future value of the investment.


PMT(rate, term, [pv], [fv], [type])

Returns the payment amount for a loan.

rateThe interest rate per period.
termThe total number of periods.
pvThe present value of the loan. If this parameter is omitted it defaults to 0.
fvThe future value of the loan. If this parameter is omitted it defaults to 0.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.
If this parameter is omitted it defaults to 0.


PPMT(rate, period, term, pv, [fv], [type])

Returns the payment on the principal for a specified period.

rateThe interest rate per period.
periodThe period for which you want the payment amount.
termThe total number of periods.
pvThe present value of the loan.
fvThe future value of the loan. If this parameter is omitted it defaults to 0.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.
If this parameter is omitted it defaults to 0.


PRICE(settlement_date, maturity_date, rate, yield, redemption, frequency, [basis])

Returns the price per $100 of a security.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
rateThe annual coupon rate.
yieldThe annual yield rate.
redemptionThe redemption value per $100.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


PRICEDISC(settlement_date, maturity_date, discount, redemption, [basis])

Returns the price per $100 of a discounted security.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
discountThe discount rate.
redemptionThe redemption value per $100.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


PRICEMAT(settlement_date, maturity_date, issue_date, rate, yield, [basis])

Returns the price per $100 of a security that pays interest at maturity.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
issue_dateThe issue date.
rateThe interest rate.
yieldThe annual yield rate.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


PV(rate, term, payment, [fv], [type])

Returns the present value of an investment at a fixed rate.

rateThe interest rate per period.
termThe total number of periods.
paymentThe payment amount each period.
fvThe future value. If this parameter is omitted it is assumed to be zero.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.
If this parameter is omitted it defaults to 0.


RATE(term, payment, pv, [fv], [type], [guess])

Returns the interest rate per period of an annuity.

termThe total number of periods.
paymentThe payment amount each period.
pvThe present value.
fvThe future value. If this parameter is omitted it is assumed to be zero.
typeThe timing of the payment. The possible values are:
0Payment is made at the end of each period.
1Payment is made at the start of each period.
If this parameter is omitted it defaults to 0.
guessThe estimated rate of return. If this parameter is omitted it defaults to 0.1.


RECEIVED(settlement_date, maturity_date, investment, discount, [basis])

Returns the amount received at maturity for a fully invested security.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
investmentThe investment amount.
discountThe discount rate.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


RRI(nper, pv, fv)

Returns the interest rate for the growth of an investment.

nperThe number of periods.
salvageThe present value.
lifeThe future value.


SLN(cost, salvage, life)

Returns the straight line depreciation.

costThe cost.
salvageThe salvage value.
lifeThe total number of periods.


SYD(cost, salvage, life, period)

Returns the sum of years depreciation.

costThe cost.
salvageThe salvage value.
lifeThe total number of periods.
periodThe period for which you want the depreciation.


TBILLEQ(settlement_date, maturity_date, discount)

Returns the bond-equivalent yield for a treasury bill.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
discountThe discount rate.


TBILLPRICE(settlement_date, maturity_date, discount)

Returns the price per $100 for a treasury bill.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
discountThe discount rate.


TBILLYIELD(settlement_date, maturity_date, price)

Returns the yield for a treasury bill.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
priceThe price per $100.


VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])

Returns the depreciation in a specified range of periods using the variable declining balance method.

costThe cost.
salvageThe salvage value.
lifeThe total number of periods.
start_periodThe first period number for which to calculate depreciation.
end_periodThe last period number for which to calculate depreciation.
factorThe rate at which the balance declines. If this parameter is omitted it defaults to 2.
no_switchSpecifies whether to switch to straight-line depreciation when the straight-line depreciation is greater than the declining balance depreciation. The possible values are:
FALSESwitch to straight-line depreciation.
TRUEDo not switch to straight-line depreciation.
If this parameter is omitted it defaults to FALSE.


XIRR(values_range, dates_range, [guess])

Returns the internal rate of return.

values_rangeThe list (array or reference) of payment and income values.
dates_rangeThe list (array or reference) of the dates of the payment and income values.
guessThe estimated rate of return. If this parameter is omitted it defaults to 0.1.


XNPV(rate, values_range, dates_range)

Returns the net present value of an investment.

rateThe discount rate per period.
values_rangeThe list (array or reference) of payment and income values.
dates_rangeThe list (array or reference) of the dates of the payment and income values.


YIELD(settlement_date, maturity_date, rate, price, redemption, frequency, [basis])

Returns the yield on a security.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
rateThe annual coupon rate.
priceThe price per $100.
redemptionThe redemption value per $100.
frequencyThe number of coupon payments per year. Should be 1, 2 or 4.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


YIELDDISC(settlement_date, maturity_date, price, redemption, [basis])

Returns the annual yield for a discounted security.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
priceThe price per $100.
redemptionThe redemption value per $100.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


YIELDMAT(settlement_date, maturity_date, issue_date, rate, price, [basis])

Returns the annual yield of a security that pays interest at maturity.

settlement_dateThe settlement date.
maturity_dateThe maturity date.
issue_dateThe issue date.
rateThe interest rate.
priceThe price per $100.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:
0US (NASD) 30/360
1Actual/actual
2Actual/360
3Actual/365
4European 30/360
If this parameter is omitted it defaults to 0.


Information functions

CELL(info_type, [reference])

Returns information about a worksheet cell.

info_typeThe type of information you want returned. The possible values are:
"address"A string containing the cell reference
"col"The column number of the cell
"color"1 if the cell is formatted in color for negative values, otherwise 0 (not currently implemented)
"contents"The result of the formula in the cell, or 0 if the cell is empty
"filename"The path, filename and sheet name of the cell
"format"The number format that has been applied to the cell
"parentheses"1 if the cell is formatted in parentheses for positive values, otherwise 0 (not currently implemented)
"prefix"The alignment of text in the cell:
'left-aligned
"right-aligned
^centred
\filled
"protect"1 if the cell is locked, otherwise 0
"row"The row number of the cell
"type"A string representing the type of the cell result: "b" for blank, "l" for label (string), otherwise "v"
"width"The cell width in characters
referenceThe cell about which you want information. If this parameter is omitted it defaults to the cell containing the formula.


ERROR.TYPE(error_val)

Returns a number corresponding to the type of the error.

error_valThe error value, or cell containing the error value, that you want to check.
The returned values are:
#NULL!1
#DIV/0!2
#VALUE!3
#REF!4
#NAME?5
#NUM!6
#N/A7
other#N/A


INFO(type_text)

Returns information about the current spreadsheet.

type_textThe type of information you want returned. The possible values are:
"directory"The path of the current directory
"memavail"The number of bytes of memory available
"memused"The number of bytes of memory used
"numfile"The number of spreadsheet files that are open
"origin"A string containing the address of the cell in the top-left corner of the scrollable region
"osversion"The operating system version number
"recalc"A string containing "Automatic" or "Manual", depending on the recalculation mode
"release"The release version with which this program is compatible
"system"The operating system type
"totmem"The total number of bytes of memory


ISBLANK(value)

Returns TRUE if the value is a reference to an empty cell, otherwise FALSE.

valueThe cell whose value you want to check.


ISERR(value)

Returns TRUE if the value is an error other than #N/A, otherwise FALSE.

valueThe expression whose result you want to check.


ISERROR(value)

Returns TRUE if the value is an error, otherwise FALSE.

valueThe expression whose result you want to check.


ISEVEN(number)

Returns TRUE if the number is even, otherwise FALSE.

numberThe number you want to check.


ISFORMULA(reference)

Returns TRUE if the cell contains a formula, otherwise FALSE.

referenceThe cell you want to check.


ISLOGICAL(value)

Returns TRUE if the value is a logical value, otherwise FALSE.

valueThe expression whose result you want to check.


ISNA(value)

Returns TRUE if the value is the error #N/A, otherwise FALSE.

valueThe expression whose result you want to check.


ISNONTEXT(value)

Returns TRUE if the value is not a string, otherwise FALSE.

valueThe expression whose result you want to check.


ISNUMBER(value)

Returns TRUE if the value is a number, otherwise FALSE.

valueThe expression whose result you want to check.


ISODD(number)

Returns TRUE if the number is odd, otherwise FALSE.

numberThe number you want to check.


ISREF(value)

Returns TRUE if the value is a reference, otherwise FALSE.

valueThe expression whose result you want to check.


ISTEXT(value)

Returns TRUE if the value is a string, otherwise FALSE.

valueThe expression whose result you want to check.


N(value)

Returns a number corresponding to the input value, or 0 if the input value is a string. You should not need to use this function, as values are automatically converted where necessary when they are used in formulas.

valueThe value you want as a number.


NA()

Returns the error value #N/A.



SHEET([value])

Returns the number of the specified sheet.

valueA reference or sheet name. If this parameter is omitted it defaults to the sheet containing the function.


SHEETS([reference])

Returns the number of sheets in the specified reference.

valueA reference to one or more sheets. If this parameter is omitted it defaults to all sheets in the file containing the function.


TYPE(value)

Returns a number corresponding to the type of the value.

valueThe value, or cell containing the value, that you want to check.
The returned values are:
number1
text2
logical4
error16
array64


Logical functions

AND(logical1, [logical2, ...])

Returns TRUE if all of the input values are TRUE, otherwise FALSE.

logical1, ...The values that you want to check.


FALSE()

Returns the logical value FALSE.



IF(logical_test, [value_if_true], [value_if_false])

Returns one of two other values depending no whether the logical test evaluates to TRUE or FALSE.

logical_testAn expression that results in TRUE or FALSE.
value_if_trueThe value to be returned if the logical test is TRUE. If this parameter is omitted it defaults to 0.
value_if_falseThe value to be returned if the logical test is FALSE. If this parameter is omitted it defaults to FALSE.


IFERROR(value, value_if_error)

Returns the result of a formula, or a different value if the result of the formula is an error.

valueThe expression that may result in an error.
value_if_errorThe value to be returned if the expression returns an error.


IFNA(value, value_if_na)

Returns the result of a formula, or a different value if the result of the formula is the error #N/A.

valueThe expression that may result in the error #N/A.
value_if_errorThe value to be returned if the expression returns the error #N/A.


IFS(logical1, [value_if_true1], [logical2, value_if_true2, ...])

Returns the value corresponding to the first TRUE logical test in the list. If none of them are TRUE then it returns the error #N/A.

logical1 ...The value that you want to check.
value_if_true1 ...The value to be returned if the logical expression is true.


NOT(logical)

Returns TRUE if the input expression evaluates to FALSE, and vice versa.

logicalThe logical expression for which you want the opposite value.


OR(logical1, [logical2, ...])

Returns TRUE if any of the input values are TRUE, otherwise FALSE.

logical1, ...The values that you want to check.


SWITCH(expression, value1, result1, [default or value2, result2, ...])

Returns the result corresponding to the matching value, or the default value if there are no matches (or #N/A if there is no default value).

expressionThe value to be matched against the entries in the list.
value1 ...The entry to be compared with the expression.
result1 ...The result to be returned if the expression matches the value.


TRUE()

Returns the logical value TRUE.



XOR(logical1, [logical2, ...])

Returns TRUE if an odd number of the input values are TRUE, otherwise FALSE.

logical1, ...The values that you want to check.


Lookup/reference functions

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Returns a string containing the specified cell address.

row_numThe row number of the cell.
column_numThe column number of the cell.
abs_numA number representing whether the row or column are to be absolute or relative. The possible values are:
1Both absolute
2Absolute row, relative column
3Relative row, absolute column
4Both relative
If this parameter is omitted it defaults to 1.
a1Specifies the style of the reference. The possible values are:
FALSER1C1 style
TRUEA1 style
If this parameter is omitted it defaults to TRUE.
sheet_textOptional sheet name with which to prefix the reference.


AREAS(reference)

Returns the number of areas contained in the reference.

referenceThe reference whose areas you want to count.


CHOOSE(index_num, value1, [value2, ...])

Returns one of several values depending on the index.

index_numThe index of the value to be returned, should be in the range 1 to 29.
value1, ...Up to 29 values, one of which will be chosen to be the result.


COLUMN([reference])

Returns the column number of the reference.

referenceThe reference whose column number you want. If this parameter is omitted it defaults to the cell containing the function.


COLUMNS(array)

Returns the number of columns in the reference.

arrayThe reference whose columns you want to count.


FORMULATEXT(reference)

Returns the formula entered in a cell.

referenceThe reference whose formula you want to retrieve.


HLOOKUP(lookup_value, table_range, row_index_num, [range_lookup])

Returns a value from a horizontal table, found by searching for the lookup value in the top row of the table and then returning a value from the same or a different row in the table.

lookup_valueThe value to be found in the table.
table_rangeA reference containing the table cells.
row_index_numThe offset of the value to be returned, where 1 is the top row of the table.
range_lookupWhether to find an approximate or exact match. The possible values are:
FALSEAn exact match is required
TRUEIf an exact match is not found, use the closest value less than the lookup value
If this parameter is omitted it defaults to TRUE.


HYPERLINK(link_location, [friendly_name])

Jumps to a cell or range when this cell is selected.

link_locationA text expression that evaluates to the form "filename" or "[filename]reference".
friendly_nameThe text to be displayed in the cell. If this parameter is omitted it defaults to the link location text.


INDEX(reference, [row_num], [col_num], [area_num])

Returns a subset of an array or reference.

referenceThe array or reference of which you want the subset.
row_numThe number of the row to return. If this parameter is omitted all rows will be returned.
col_numThe number of the column to return. If this parameter is omitted all columns will be returned.
area_numThe number of the area to return when the reference contains more than one area. If this parameter is omitted it defaults to 1.


INDIRECT(ref_text, [a1])

Returns a reference from the specified text.

ref_textA text expression that evaluates to the name of a cell or range of cells.
a1Specifies the style of the reference. The possible values are:
FALSER1C1 style
TRUEA1 style
If this parameter is omitted it defaults to TRUE.


LOOKUP(lookup_value, lookup_range, [result_range])

Returns a value from a horizontal or vertical table, found by searching for the lookup value in the top row (for a horizontal table) or left column (for a vertical or square table) of the table and then returning a value from the corresponding position in the result range (if specified) or from the bottom row (for a horizontal table) or right column (for a vertical or square table) of the table.

lookup_valueThe value to be found in the table.
table_rangeA reference containing the table cells.
result_rangeThe range of cells from which to return a result. If this parameter is not specified the result will be returned from the opposite row or column of the table range.


MATCH(lookup_value, lookup_range, [match_type])

Returns a number representing the position of a value in a table.

lookup_valueThe value to be found in the table.
lookup_rangeA reference containing the table cells.
match_typeWhether to find an approximate or exact match. The possible values are:
1If an exact match is not found, use the closest value less than the lookup value (the table values should be in ascending order)
0An exact match is required
-1If an exact match is not found, use the closest value greater than the lookup value (the table values should be in descending order)
If this parameter is omitted it defaults to 1.


OFFSET(reference, rows, cols, [height], [width])

Returns a new reference based on the specified reference.

referenceThe reference to be used as a starting point.
rowsThe number of rows to move the reference up (negative) or down (positive).
colsThe number of columns to move the reference left (negative) or right (positive).
heightThe height of the new reference. If this parameter is omitted it defaults to the height of the old reference.
widthThe width of the new reference. If this parameter is omitted it defaults to the width of the old reference.


ROW([reference])

Returns the row number of the reference.

referenceThe reference whose row number you want. If this parameter is omitted it defaults to the cell containing the function.


ROWS(array)

Returns the number of rows in the reference.

arrayThe reference whose rows you want to count.


TRANSPOSE(array)

Returns the transposition of the specified array or reference.

arrayThe array or reference whose values you want to transpose.


VLOOKUP(lookup_value, table_range, col_index_num, [range_lookup])

Returns a value from a vertical table, found by searching for the lookup value in the left column of the table and then returning a value from the same or a different column in the table.

lookup_valueThe value to be found in the table.
table_rangeA reference containing the table cells.
col_index_numThe offset of the value to be returned, where 1 is the left column of the table.
range_lookupWhether to find an approximate or exact match. The possible values are:
FALSEAn exact match is required
TRUEIf an exact match is not found, use the closest value less than the lookup value
If this parameter is omitted it defaults to TRUE.


Math/trigonometry functions

ABS(number)

Returns the absolute value of a number.

numberThe number for which you want the absolute value.


ACOS(number)

Returns the angle in radians corresponding to the arccosine of a number.

numberThe number for which you want the arccosine.


ACOSH(number)

Returns the angle in radians corresponding to the inverse hyperbolic cosine of a number.

numberThe number for which you want the inverse hyperbolic cosine.


ACOT(number)

Returns the angle in radians corresponding to the arccotangent of a number.

numberThe number for which you want the arccotangent.


ACOTH(number)

Returns the angle in radians corresponding to the inverse hyperbolic cotangent of a number.

numberThe number for which you want the inverse hyperbolic cotangent.


AGGREGATE(function_num, [options], ref1, [ref2, ...])

Returns an aggregate value for items in a list.

function_numThe aggregate function that you want. The possible values are:
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9SUM
10VAR.S
11VAR.P
12MEDIAN
13MODE.SNGL
14LARGE
15SMALL
16PERCENTILE.INC
17QUARTILE.INC
18PERCENTILE.EXC
19QUARTILE.EXC
optionsWhich values are to be excluded from the calculation. The possible values are:
0Ignore nested SUBTOTAL and AGGREGATE functions
1Ignore nested SUBTOTAL and AGGREGATE functions, hidden rows
2Ignore nested SUBTOTAL and AGGREGATE functions, error values
3Ignore nested SUBTOTAL and AGGREGATE functions, hidden rows, error values
4Ignore nothing
5Ignore hidden rows
6Ignore error values
7Ignore hidden rows and error values
If this parameter is omitted it defaults to 0.
ref1, ...For functions 1 to 13, these are the ranges of cells that you want aggregated. For functions 14 to 19, ref1 is the range of cells and ref2 is the function second parameter.


ARABIC(text)

Returns the decimal equivalent of a number in Roman numerals.

numberThe number to convert from Roman numerals to decimal.


ASIN(number)

Returns the angle in radians corresponding to the arcsine of a number.

numberThe number for which you want the arcsine.


ASINH(number)

Returns the angle in radians corresponding to the inverse hyperbolic sine of a number.

numberThe number for which you want the inverse hyperbolic sine.


ATAN(number)

Returns the angle in radians corresponding to the arctangent of a number.

numberThe number for which you want the arctangent.


ATAN2(x_num, y_num)

Returns the angle in radians corresponding to a pair of co-ordinates.

x_numThe x co-ordinate for which you want the angle.
y_numThe y co-ordinate for which you want the angle.


ATANH(number)

Returns the angle in radians corresponding to the inverse hyperbolic tangent of a number.

numberThe number for which you want the inverse hyperbolic tangent.


BASE(number, radix, [min_length])

Returns the text representation of a decimal number in a different base.

numberThe number to be converted.
radixThe base to use for conversion. This should be between 2 and 36.
min_lengthThe minimum length for the returned string. If the result is shorter than this value, then it will be padded on the left with zeros.


CEILING(number, significance)

Returns the number rounded up (away from zero) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding.


CEILING.MATH(number, [significance], [mode])

Returns the number rounded up (usually towards +infinity) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding. If this parameter is omitted it defaults to 1.
modeIf mode is non-zero then negative numbers are rounded towards -infinity. If this parameter is omitted it defaults to 0.


CEILING.PRECISE(number, [significance])

Returns the number rounded up (towards +infinity) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding. If this parameter is omitted it defaults to 1.


COMBIN(number, number_chosen)

Returns the number of combinations in which a number of items can be chosen from a total number.

numberThe total number of items.
number_chosenThe number of items chosen.


COMBINA(number, number_chosen)

Returns the number of combinations in which a number of items can be chosen (with repetitions) from a total number.

numberThe total number of items.
number_chosenThe number of items chosen.


COS(number)

Returns the cosine of an angle.

numberThe angle for which you want the cosine. It must be specified in radians.


COSH(number)

Returns the hyperbolic cosine of an angle.

numberThe angle for which you want the hyperbolic cosine. It must be specified in radians.


COT(number)

Returns the cotangent of an angle.

numberThe angle for which you want the cotangent. It must be specified in radians.


COTH(number)

Returns the hyperbolic cotangent of an angle.

numberThe angle for which you want the hyperbolic cotangent. It must be specified in radians.


CSC(number)

Returns the cosecant of an angle.

numberThe angle for which you want the cosecant. It must be specified in radians.


CSCH(number)

Returns the hyperbolic cosecant of an angle.

numberThe angle for which you want the hyperbolic cosecant. It must be specified in radians.


DECIMAL(text, radix)

Returns the decimal value of a number in a specified base.

textThe number represented in the specified base.
radixThe base.


DEGREES(angle)

Converts an angle from radians to degrees.

angleThe angle that you want to convert.


ECMA.CEILING(number, significance)

Returns the number rounded up (away from zero) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding.


EVEN(number)

Returns the number rounded up to the next even number.

numberThe number which you want rounded.


EXP(number)

Returns the e raised to the power number.

numberThe power to which you want to raise e.


FACT(number)

Returns the factorial of a number.

numberThe number of which you want the factorial.


FACTDOUBLE(number)

Returns the double factorial of a number.

numberThe number of which you want the double factorial.


FLOOR(number, significance)

Returns the number rounded down (towards zero) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding.


FLOOR.MATH(number, [significance], [mode])

Returns the number rounded down (usually towards -infinity) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding. If this parameter is omitted it defaults to 1.
modeIf mode is non-zero then negative numbers are rounded towards +infinity. If this parameter is omitted it defaults to 0.


FLOOR.PRECISE(number, [significance])

Returns the number rounded down (towards -infinity) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding. If this parameter is omitted it defaults to 1.


GCD(number1, [number2, ...])

Returns the greatest common divisor of a set of numbers.

number1, ...The numbers of which you want the greatest common divisor.


INT(number)

Returns the number rounded down to the next integer.

numberThe number which you want rounded.


ISO.CEILING(number, [significance])

Returns the number rounded up (towards +infinity) to the next multiple of significance.

numberThe number which you want rounded.
significanceThe units you want to use for rounding. If this parameter is omitted it defaults to 1.


LCM(number1, [number2, ...])

Returns the lowest common multiple of a set of numbers.

number1, ...The numbers of which you want the lowest common multiple.


LN(number)

Returns the natural logarithm of a number.

numberThe number for which you want the natural logarithm.


LOG(number, [base])

Returns the logarithm of a number in a specified base.

numberThe number for which you want the logarithm.
baseThe base in which you want the logarithm. If this parameter is omitted it defaults to 10.


LOG10(number)

Returns the base 10 logarithm of a number.

numberThe number for which you want the base 10 logarithm.


MDETERM(array)

Returns the determinant of a matrix.

arrayAn array or reference to cells containing the matrix.


MINVERSE(array)

Returns the inverse of a matrix.

arrayAn array or reference to cells containing the matrix.


MMULT(array1, array2)

Returns the product of two matrices.

array1An array or reference to cells containing the first matrix.
array2An array or reference to cells containing the second matrix.


MOD(number, divisor)

Returns the remainder of a division.

numberThe dividend.
divisorThe divisor.


MROUND(number, multiple)

Returns the number rounded to a multiple.

numberThe number that you want rounded.
multipleThe multiple to which you want the number rounded.


MULTINOMIAL(number1, [number2, ...])

Returns the factorial of the sum of the values divided by the product of their factorials.

number1, ...The numbers of which you want the multinomial.


MUNIT(dimension)

Returns a unit matrix of the specified dimension.

dimensionThe dimension of the unit matrix.


ODD(number)

Returns the number rounded up to the next odd number.

numberThe number which you want rounded.


PI()

Returns the value of Pi.



POWER(number, power)

Returns the number raised to the power.

numberThe number which you want raised to a power.
powerThe power to which you want to raise the number.


PRODUCT(number1, [number2, ...])

Returns the product of a list of numbers.

number1, ...The numbers that you want to multiply together.


QUOTIENT(numerator, denominator)

Returns the integer result of a division.

numeratorThe dividend.
denominatorThe divisor.


RADIANS(angle)

Converts an angle from degrees to radians.

angleThe angle that you want to convert.


RAND()

Returns a random number between 0 and 1.



RANDBETWEEN(smallest, largest)

Returns a random integer in the range you specify.

smallestThe lower limit of the range.
largestThe upper limit of the range.


ROMAN(number, [form])

Returns a string representing a number converted to roman numerals.

numberThe number that you want to convert.
formSpecifies the compactness of the representation. The possible values are:
0Classic
1More compact
2More compact
3More compact
4Simplified
TRUEClassic
FALSESimplified
If this parameter is omitted it defaults to 0.


ROUND(number, num_digits)

Returns a number rounded to the number of decimal places specified.

numberThe number that you want rounded.
num_digitsThe number of decimal places to round to (may be negative).


ROUNDDOWN(number, [num_digits])

Returns a number rounded down to the number of decimal places specified.

numberThe number that you want rounded.
num_digitsThe number of decimal places to round to (may be negative). If this parameter is omitted it defaults to 0.


ROUNDUP(number, [num_digits])

Returns a number rounded up to the number of decimal places specified.

numberThe number that you want rounded.
num_digitsThe number of decimal places to round to (may be negative). If this parameter is omitted it defaults to 0.


SEC(number)

Returns the secant of an angle.

numberThe angle for which you want the secant. It must be specified in radians.


SECH(number)

Returns the hyperbolic secant of an angle.

numberThe angle for which you want the hyperbolic secant. It must be specified in radians.


SERIESSUM(x, n, m, coefficients)

Returns the value of a polynomial.

xThe number to be raised to a power.
nThe power of the first term in the series.
mThe difference between successive powers in the series.
coefficientsAn array or reference to cells containing the coefficients.


SIGN(number)

Returns the sign of a number; 1 for positive, -1 for negative and 0 for zero.

numberThe number of which you want the sign.


SIN(number)

Returns the sine of an angle.

numberThe angle for which you want the sine. It must be specified in radians.


SINH(number)

Returns the hyperbolic sine of an angle.

numberThe angle for which you want the hyperbolic sine. It must be specified in radians.


SQRT(number)

Returns the square root of a number.

numberThe number of which you want the square root.


SQRTPI(number)

Returns the square root of a number after it is multiplied by Pi.

numberThe number which you want to multiply by Pi and then take the square root.


SUBTOTAL(function_num, ref1, [ref2, ...])

Returns the subtotal of values in one or more ranges, excluding other subtotal figures.

function_numThe subtotal function that you want. The possible values are:
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP
ref1, ...The ranges of cells that you want subtotalled.


SUM(number1, [number2, ...])

Returns the sum of a list of numbers.

number1, ...The numbers that you want to add together.


SUMIF(range, criteria, [sum_range])

Returns the sum of cells in a specified range that meet the specified criteria.

rangeA reference to the cells you want to check.
criteriaA string containing a comparison expression.
sum_rangeA range containing values to be summed where the values in the first range meet the specified criteria. If this parameter is omitted then the values in the first range are summed.


SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Returns the sum of cells in a specified range where corresponding cells in other ranges all meet specified criteria.

sum_rangeA range containing values to be summed where the corresponding criteria are met.
criteria_range1, ...A reference to cells you want to check.
criteria1, ...A string containing a comparison expression.


SUMPRODUCT(array1, [array2, ...])

Returns the sum of the products of the cells in one or more ranges.

array1, ...The ranges of cells where corresponding values are multiplied together and then summed.


SUMSQ(number1, [number2, ...])

Returns the sum of the squares of the numbers in the list.

number1, ...The numbers that are to be squared and then summed.


SUMX2MY2(array_x, array_y)

Returns the sum of the differences of the squares of the numbers in the ranges.

array_xThe first range of numbers.
array_yThe second range of numbers.


SUMX2PY2(array_x, array_y)

Returns the sum of the sums of the squares of the numbers in the ranges.

array_xThe first range of numbers.
array_yThe second range of numbers.


SUMXMY2(array_x, array_y)

Returns the sum of the squares of the differences of the numbers in the ranges.

array_xThe first range of numbers.
array_yThe second range of numbers.


TAN(number)

Returns the tangent of an angle.

numberThe angle for which you want the tangent. It must be specified in radians.


TANH(number)

Returns the hyperbolic tangent of an angle.

numberThe angle for which you want the hyperbolic tangent. It must be specified in radians.


TRUNC(number, [num_digits])

Returns a number truncated to the number of decimal places specified.

numberThe number that you want truncated.
num_digitsThe number of decimal places to truncate to (may be negative).


Statistical functions

AVEDEV(number1, [number2, ...])

Returns the average of the differences of a set of numbers from their mean.

number1, ...The numbers of which you want the average deviation.


AVERAGE(number1, [number2, ...])

Returns the average of a set of numbers.

number1, ...The numbers of which you want the average.


AVERAGEA(value1, [value2, ...])

Returns the average of a set of values.

value1, ...The values of which you want the average.


AVERAGEIF(range, criteria, [average_range])

Returns the average of cells in a specified range that meet the specified criteria.

rangeA reference to the cells you want to check.
criteriaA string containing a comparison expression.
average_rangeA range containing values to be averaged where the values in the first range meet the specified criteria. If this parameter is omitted then the values in the first range are averaged.


AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Returns the average of cells in a specified range where corresponding cells in other ranges all meet specified criteria.

average_rangeA range containing values to be averaged where the corresponding criteria are met.
criteria_range1, ...A reference to cells you want to check.
criteria1, ...A string containing a comparison expression.


BETA.DIST(x, alpha, beta, cumulative, [A], [B])

Returns the beta distribution probability.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the probability density function.
TRUEReturn the cumulative distribution function.
AThe lower limit. If this parameter is omitted it defaults to 0.
BThe upper limit. If this parameter is omitted it defaults to 1.


BETA.INV(probability, alpha, beta, [A], [B])

Returns the value associated with the specified cumulative beta distribution probability.

probabilityThe cumulative beta distribution probability for which you want the value.
alphaThe alpha value.
betaThe beta value.
AThe lower limit. If this parameter is omitted it defaults to 0.
BThe upper limit. If this parameter is omitted it defaults to 1.


BETADIST(x, alpha, beta, [A], [B])

Returns the cumulative beta distribution probability.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
AThe lower limit. If this parameter is omitted it defaults to 0.
BThe upper limit. If this parameter is omitted it defaults to 1.


BETAINV(probability, alpha, beta, [A], [B])

Returns the value associated with the specified cumulative beta distribution probability.

probabilityThe cumulative beta distribution probability for which you want the value.
alphaThe alpha value.
betaThe beta value.
AThe lower limit. If this parameter is omitted it defaults to 0.
BThe upper limit. If this parameter is omitted it defaults to 1.


BINOM.DIST(number_successes, trials, probability, cumulative)

Returns the binomial distribution probability.

number_successesThe number of trials that are successful.
trialsThe total number of trials.
probabilityThe probability of a single trial being successful.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2])

Returns the binomial distribution probability.

trialsThe total number of trials.
probabilityThe probability of a single trial being successful.
number_sThe number of trials that are successful (lower limit).
number_s2The number of trials that are successful (upper limit).


BINOM.INV(trials, probability_s, alpha)

Returns the value at which the cumulative binomial distribution is greater than or equal to alpha.

trialsThe total number of trials.
probability_sThe probability of a single trial being successful.
alphaThe value at which you want to evaluate the function.


BINOMDIST(number_successes, trials, probability, cumulative)

Returns the binomial distribution probability.

number_successesThe number of trials that are successful.
trialsThe total number of trials.
probabilityThe probability of a single trial being successful.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


CHIDIST(x, degrees_freedom)

Returns the chi-squared distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe number of degrees of freedom.


CHIINV(probability, degrees_freedom)

Returns the value associated with the specified chi-squared distribution probability.

probabilityThe probability for which you want the value.
degrees_freedomThe number of degrees of freedom.


CHISQ.DIST(x, degrees_freedom, cumulative)

Returns the chi-squared distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe number of degrees of freedom.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the probability density function.
TRUEReturn the cumulative distribution function.


CHISQ.DIST.RT(x, degrees_freedom)

Returns the chi-squared distribution right-tailed probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe number of degrees of freedom.


CHISQ.INV(probability, degrees_freedom)

Returns the value associated with the specified chi-squared distribution left-tailed probability.

probabilityThe probability for which you want the value.
degrees_freedomThe number of degrees of freedom.


CHISQ.INV.RT(probability, degrees_freedom)

Returns the value associated with the specified chi-squared distribution right-tailed probability.

probabilityThe probability for which you want the value.
degrees_freedomThe number of degrees of freedom.


CHISQ.TEST(actual_range, expected_range)

Returns the probability result of the chi-squared test.

actual_rangeAn array or reference to cells containing the empirical results.
expected_rangeAn array or reference to cells containing the theoretical results.


CHITEST(actual_range, expected_range)

Returns the probability result of the chi-squared test.

actual_rangeAn array or reference to cells containing the empirical results.
expected_rangeAn array or reference to cells containing the theoretical results.


CONFIDENCE(alpha, standard_deviation, size)

Returns the confidence interval for a population mean.

alphaThe significance level.
standard_deviationThe population standard deviation.
sizeThe sample size.


CONFIDENCE.NORM(alpha, standard_deviation, size)

Returns the confidence interval for a population mean using a normal distribution.

alphaThe significance level.
standard_deviationThe population standard deviation.
sizeThe sample size.


CONFIDENCE.T(alpha, standard_deviation, size)

Returns the confidence interval for a population mean using a Student's T distribution.

alphaThe significance level.
standard_deviationThe population standard deviation.
sizeThe sample size.


CORREL(range1, range2)

Returns the correlation coefficient of two ranges.

range1The first range to be compared.
range2The second range to be compared.


COUNT(value1, [value2, ...])

Returns the count of numbers in a list.

value1, ...The items whose numbers are to be counted.


COUNTA(value1, [value2, ...])

Returns the count of values in a list.

value1, ...The items whose values are to be counted.


COUNTBLANK(range)

Returns the number of blank cells in a range.

rangeThe reference of the cells you want to check.


COUNTIF(range, criteria)

Returns the number of cells in a specified range that meet the specified criteria.

rangeA reference to the cells you want to count.
criteriaA string containing a comparison expression.


COUNTIFS(range1, criteria1, [range2, criteria2, ...])

Returns the number of times that cells in specified ranges all meet specified criteria.

range1, ...A reference to a group of cells you want to count.
criteria1, ...A string containing a comparison expression.


COVAR(range1, range2)

Returns the covariance of two ranges.

range1The first range to be compared.
range2The second range to be compared.


COVARIANCE.P(range1, range2)

Returns the population covariance of two ranges.

range1The first range to be compared.
range2The second range to be compared.


COVARIANCE.S(range1, range2)

Returns the sample covariance of two ranges.

range1The first range to be compared.
range2The second range to be compared.


CRITBINOM(trials, probability_s, alpha)

Returns the value at which the cumulative binomial distribution is greater than or equal to alpha.

trialsThe total number of trials.
probability_sThe probability of a single trial being successful.
alphaThe value at which you want to evaluate the function.


DEVSQ(number1, [number2, ...])

Returns the sum of the squares of the differences of a set of numbers from their mean.

number1, ...The numbers of which you want the squared deviations.


EXPON.DIST(x, lambda, cumulative)

Returns the exponential distribution probability.

xThe value at which you want to evaluate the function.
lambdaThe lambda value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


EXPONDIST(x, lambda, cumulative)

Returns the exponential distribution probability.

xThe value at which you want to evaluate the function.
lambdaThe lambda value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


F.DIST(x, degrees_freedom1, degrees_freedom2, cumulative)

Returns the F distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


F.DIST.RT(x, degrees_freedom1, degrees_freedom2)

Returns the right-tailed F distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.


F.INV(probability, degrees_freedom1, degrees_freedom2)

Returns the value associated with the specified F distribution probability.

probabilityThe probability for which you want the value.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.


F.INV.RT(probability, degrees_freedom1, degrees_freedom2)

Returns the value associated with the specified right-tailed F distribution probability.

probabilityThe probability for which you want the value.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.


F.TEST(array1, array2)

Returns the probability result of the F test.

array1The first range to be compared.
array2The second range to be compared.


FDIST(x, degrees_freedom1, degrees_freedom2)

Returns the F distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.


FINV(probability, degrees_freedom1, degrees_freedom2)

Returns the value associated with the specified F distribution probability.

probabilityThe probability for which you want the value.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.


FISHER(x)

Returns the Fisher transformation.

xThe value at which to evaluate the function.


FISHERINV(y)

Returns the inverse Fisher transformation.

yThe value at which to evaluate the function.


FORECAST(x, known_ys, known_xs)

Returns the expected value of y for a given x value for a line passing through a specified set of points.

xThe x value at which to evaluate the function.
known_ysAn array or reference to a range of cells containing the y values that are already known.
known_xsAn array or reference to a range of cells containing the x values that are already known.


FORECAST.LINEAR(x, known_ys, known_xs)

Returns the expected value of y for a given x value for a line passing through a specified set of points.

xThe x value at which to evaluate the function.
known_ysAn array or reference to a range of cells containing the y values that are already known.
known_xsAn array or reference to a range of cells containing the x values that are already known.


FREQUENCY(data_array, bins_array)

Returns the counts of items in specified numeric categories.

data_arrayAn array or reference to a range of cells containing values to be counted.
bins_arrayAn array or reference to a range of cells containing the upper limits for each category.


FTEST(array1, array2)

Returns the probability result of the F test.

array1The first range to be compared.
array2The second range to be compared.


GAMMA(x)

Returns the gamma function evaluated at x.

xThe value at which you want to evaluate the function.


GAMMA.DIST(x, alpha, beta, cumulative)

Returns the gamma distribution probability.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


GAMMA.INV(probability, alpha, beta)

Returns the value associated with the specified gamma distribution probability.

probabilityThe probability for which you want the value.
alphaThe alpha value.
betaThe beta value.


GAMMADIST(x, alpha, beta, cumulative)

Returns the gamma distribution probability.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


GAMMAINV(probability, alpha, beta)

Returns the value associated with the specified gamma distribution probability.

probabilityThe probability for which you want the value.
alphaThe alpha value.
betaThe beta value.


GAMMALN(x)

Returns the natural logarithm of the gamma function evaluated at x.

xThe value at which you want to evaluate the function.


GAMMALN.PRECISE(x)

Returns the natural logarithm of the gamma function evaluated at x.

xThe value at which you want to evaluate the function.


GAUSS(z)

Returns the gauss function evaluated at z.

zThe value at which you want to evaluate the function.


GEOMEAN(number1, [number2, ...])

Returns the geometric mean of a set of numbers.

number1, ...The numbers of which you want the geometric mean.


GROWTH(known_ys, [known_xs], [new_xs], [const])

Returns the expected values of y for given x values for an exponential curve passing through a specified set of points.

known_ysThe y values that are already known.
known_xsThe x values that are already known. If this parameter is omitted it defaults to an array of values from 1 to the number of known ys.
new_xsThe new x values for which y values are required. If this parameter is omitted it defaults to the known xs.
constSpecifies whether the line must pass through the origin. The possible values are:
FALSEThe intercept is forced to be 1.
TRUEThe intercept is calculated normally.
If this parameter is omitted it defaults to TRUE.


HARMEAN(number1, [number2, ...])

Returns the harmonic mean of a set of numbers.

number1, ...The numbers of which you want the harmonic mean.


HYPGEOM.DIST(sample_s, number_sample, population_s, number_population, cumulative)

Returns the hypergeometric distribution probability.

sample_sThe number of sample trials that are successful.
number_sampleThe total number of trials in the sample.
population_sThe number of population trials that are successful.
number_populationThe total number of trials in the population.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


HYPGEOMDIST(sample_s, number_sample, population_s, number_population)

Returns the hypergeometric distribution probability.

sample_sThe number of sample trials that are successful.
number_sampleThe total number of trials in the sample.
population_sThe number of population trials that are successful.
number_populationThe total number of trials in the population.


INTERCEPT(y_range, x_range)

Returns the expected value of y when x is zero for a line passing though a specified set of points.

y_rangeThe y values that are already known.
x_rangeThe x values that are already known.


KURT(number1, [number2, ...])

Returns the kurtosis of a set of numbers.

number1, ...The numbers of which you want the kurtosis.


LARGE(array, k)

Returns the kth largest number in a set of numbers.

arrayAn array or reference to cells containing numbers of which you want the kth largest.
kThe rank of the number that you want.


LINEST(known_ys, [known_xs], [const], [stats])

Returns the coefficients for a straight line using multiple linear regression.

known_ysThe y values that are already known.
known_xsOne or more sets of x values corresponding to the known y values. If this parameter is omitted it defaults to an array of values from 1 to the number of known ys.
constSpecifies whether the line must pass through the origin. The possible values are:
FALSEThe line is forced to pass through the origin.
TRUEThe line is not forced to pass through the origin.
If this parameter is omitted it defaults to TRUE.
statsSpecifies whether the additional statistics are returned in the rows below the coefficients. These are: the standard error values for the coefficients, the R2 coefficient, the standard error for the Y estimate, the F statistic, the degrees of freedom, the regression sum of squares and the residual sum of squares. The possible values are:
FALSEThe additional statistics are not returned.
TRUEThe additional statistics are returned.
If this parameter is omitted it defaults to FALSE.


LOGEST(known_ys, [known_xs], [const], [stats])

Returns the coefficients for an exponential curve using multiple linear regression.

known_ysThe y values that are already known.
known_xsOne or more sets of x values corresponding to the known y values. If this parameter is omitted it defaults to an array of values from 1 to the number of known ys.
constSpecifies whether the line must pass through the origin. The possible values are:
FALSEThe intercept is forced to be 1.
TRUEThe intercept is calculated normally.
If this parameter is omitted it defaults to TRUE.
statsSpecifies whether the additional statistics are returned in the rows below the coefficients. These are: the standard error values for the coefficients, the R2 coefficient, the standard error for the Y estimate, the F statistic, the degrees of freedom, the regression sum of squares and the residual sum of squares. The possible values are:
FALSEThe additional statistics are not returned.
TRUEThe additional statistics are returned.
If this parameter is omitted it defaults to FALSE.


LOGINV(probability, mean, standard_dev)

Returns the value associated with the specified cumulative lognormal distribution probability.

probabilityThe probability for which you want the value.
meanThe mean of the natural logarithms of the values.
standard_devThe standard deviation of the natural logarithms of the values.


LOGNORM.DIST(x, mean, standard_dev, cumulative)

Returns the cumulative lognormal distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the natural logarithms of the values.
standard_devThe standard deviation of the natural logarithms of the values.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


LOGNORM.INV(probability, mean, standard_dev)

Returns the value associated with the specified cumulative lognormal distribution probability.

probabilityThe probability for which you want the value.
meanThe mean of the natural logarithms of the values.
standard_devThe standard deviation of the natural logarithms of the values.


LOGNORMDIST(x, mean, standard_dev)

Returns the cumulative lognormal distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the natural logarithms of the values.
standard_devThe standard deviation of the natural logarithms of the values.


MAX(number1, [number2, ...])

Returns the maximum of a set of numbers.

number1, ...The numbers of which you want the maximum.


MAXA(value1, [value2, ...])

Returns the maximum of a set of values.

value1, ...The values of which you want the maximum.


MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Returns the maximum value of cells in a specified range where corresponding cells in other ranges all meet specified criteria.

max_rangeA range containing values to be compared where the corresponding criteria are met.
criteria_range1, ...A reference to cells you want to check.
criteria1, ...A string containing a comparison expression.


MEDIAN(number1, [number2, ...])

Returns the median of a set of numbers.

number1, ...The numbers of which you want the median.


MIN(number1, [number2, ...])

Returns the minimum of a set of numbers.

number1, ...The numbers of which you want the minimum.


MINA(value1, [value2, ...])

Returns the minimum of a set of values.

value1, ...The values of which you want the minimum.


MINIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Returns the minimum value of cells in a specified range where corresponding cells in other ranges all meet specified criteria.

min_rangeA range containing values to be compared where the corresponding criteria are met.
criteria_range1, ...A reference to cells you want to check.
criteria1, ...A string containing a comparison expression.


MODE(number1, [number2, ...])

Returns the mode of a set of numbers.

number1, ...The numbers of which you want the mode.


MODE.MULT(number1, [number2, ...])

Returns a vertical array containing the mode values of a set of numbers.

number1, ...The numbers of which you want the mode.


MODE.SNGL(number1, [number2, ...])

Returns the mode of a set of numbers.

number1, ...The numbers of which you want the mode.


NEGBINOM.DIST(number_f, number_s, probability_s, cumulative)

Returns the negative binomial distribution probability.

number_fThe number of trials that fail.
number_sThe threshold number of trials that are successful.
probability_sThe probability of a single trial being successful.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


NEGBINOMDIST(number_f, number_s, probability_s)

Returns the negative binomial distribution probability.

number_fThe number of trials that fail.
number_sThe threshold number of trials that are successful.
probability_sThe probability of a single trial being successful.


NORM.DIST(x, mean, standard_dev, cumulative)

Returns the normal distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the values.
standard_devThe standard deviation of the values.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


NORM.INV(probability, mean, standard_dev)

Returns the value associated with the specified cumulative normal distribution probability.

probabilityThe probability for which you want the value.
meanThe mean of the values.
standard_devThe standard deviation of the values.


NORM.S.DIST(z, cumulative)

Returns the standard normal distribution probability.

zThe value at which you want to evaluate the function.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


NORM.S.INV(probability)

Returns the value associated with the specified cumulative standard normal distribution probability.

probabilityThe probability for which you want the value.


NORMDIST(x, mean, standard_dev, cumulative)

Returns the normal distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the values.
standard_devThe standard deviation of the values.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


NORMINV(probability, mean, standard_dev)

Returns the value associated with the specified cumulative normal distribution probability.

probabilityThe probability for which you want the value.
meanThe mean of the values.
standard_devThe standard deviation of the values.


NORMSDIST(z)

Returns the cumulative standard normal distribution probability.

zThe value at which you want to evaluate the function.


NORMSINV(probability)

Returns the value associated with the specified cumulative standard normal distribution probability.

probabilityThe probability for which you want the value.


PEARSON(array1, array2)

Returns the Pearson correlation coefficient.

array1The first range to be compared.
array2The second range to be compared.


PERCENTILE(array, k)

Returns the kth percentile of a set of values, where k is between 0 and 1 inclusive.

arrayAn array or reference to cells containing the values.
kThe percentile value.


PERCENTILE.EXC(array, k)

Returns the kth percentile of a set of values, where k is between 0 and 1 exclusive.

arrayAn array or reference to cells containing the values.
kThe percentile value.


PERCENTILE.INC(array, k)

Returns the kth percentile of a set of values, where k is between 0 and 1 inclusive.

arrayAn array or reference to cells containing the values.
kThe percentile value.


PERCENTRANK(array, x, [significance])

Returns the percentile of a value in a set of values.

arrayAn array or reference to cells containing the values.
xThe value of which you want the percentile.
significanceThe number of decimal places required in the result. If this parameter is omitted it defaults to 3.


PERCENTRANK.INC(array, x, [significance])

Returns the percentile of a value in a set of values.

arrayAn array or reference to cells containing the values.
xThe value of which you want the percentile.
significanceThe number of decimal places required in the result. If this parameter is omitted it defaults to 3.


PERMUT(number, number_chosen)

Returns the number of permutations in which a number of items can be chosen from a total number.

numberThe total number of items.
number_chosenThe number of items chosen.


PERMUTATIONA(number, number_chosen)

Returns the number of permutations in which a number of items can be chosen (with repetitions) from a total number.

numberThe total number of items.
number_chosenThe number of items chosen.


PHI(x)

Returns the standard normal distribution probability density function.

xThe value at which you want to evaluate the function.


POISSON(x, mean, cumulative)

Returns the Poisson distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the values.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


POISSON.DIST(x, mean, cumulative)

Returns the Poisson distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the values.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


PROB(x_range, prob_range, lower_limit, [upper_limit])

Returns the probability that numbers in a set are between the specified limits.

x_rangeAn array or reference to cells containing the numbers.
prob_rangeAn array or reference to cells containing the probabilities associated with each number. These values must add up to 1.
lower_limitThe lower limit of the test.
upper_limitThe upper limit of the test. If this value is omitted it defaults to the value specified for the lower limit.


QUARTILE(array, quart)

Returns the specified quartile of a set of numbers, based on the range 0 to 1 inclusive.

arrayAn array or reference to cells containing the numbers.
quartSpecifies which quartile to return. The possible values are:
0Returns the minimum value.
1Returns the first quartile.
2Returns the second quartile.
3Returns the third quartile.
4Returns the maximum value.


QUARTILE.EXC(array, quart)

Returns the specified quartile of a set of numbers, based on the range 0 to 1 exclusive.

arrayAn array or reference to cells containing the numbers.
quartSpecifies which quartile to return. The possible values are:
1Returns the first quartile.
2Returns the second quartile.
3Returns the third quartile.


QUARTILE.INC(array, quart)

Returns the specified quartile of a set of numbers, based on the range 0 to 1 inclusive.

arrayAn array or reference to cells containing the numbers.
quartSpecifies which quartile to return. The possible values are:
0Returns the minimum value.
1Returns the first quartile.
2Returns the second quartile.
3Returns the third quartile.
4Returns the maximum value.


RANK(number, range, [order])

Returns the rank of a number in a set of numbers.

numberThe number of which you want the rank.
rangeAn array or reference to cells containing the values.
orderSpecifies whether the list is treated as being in ascending or descending order of value. The possible values are:
0The list is in descending order (the highest value has rank 1).
any other valueThe list is in ascending order (the lowest value has rank 1).
If this parameter is omitted it defaults to 0.


RANK.AVG(number, range, [order])

Returns the rank of a number in a set of numbers. If more than one value has the same rank then the average rank is returned.

numberThe number of which you want the rank.
rangeAn array or reference to cells containing the values.
orderSpecifies whether the list is treated as being in ascending or descending order of value. The possible values are:
0The list is in descending order (the highest value has rank 1).
any other valueThe list is in ascending order (the lowest value has rank 1).
If this parameter is omitted it defaults to 0.


RANK.EQ(number, range, [order])

Returns the rank of a number in a set of numbers.

numberThe number of which you want the rank.
rangeAn array or reference to cells containing the values.
orderSpecifies whether the list is treated as being in ascending or descending order of value. The possible values are:
0The list is in descending order (the highest value has rank 1).
any other valueThe list is in ascending order (the lowest value has rank 1).
If this parameter is omitted it defaults to 0.


RSQ(y_array, x_array)

Returns the square of the Pearson correlation coefficient.

y_arrayThe first range to be compared.
x_arrayThe second range to be compared.


SKEW(number1, [number2, ...])

Returns the skewness of a set of numbers.

number1, ...The numbers of which you want the skewness.


SKEW.P(number1, [number2, ...])

Returns the skewness of a set of numbers based on the population.

number1, ...The numbers of which you want the skewness.


SLOPE(y_range, x_range)

Returns the slope of a line passing through a specified set of points.

y_rangeThe y values that are already known.
x_rangeThe x values that are already known.


SMALL(array, k)

Returns the kth smallest number in a set of numbers.

arrayAn array or reference to cells containing numbers of which you want the kth smallest.
kThe rank of the number that you want.


STANDARDIZE(x, mean, standard_dev)

Returns the standardized value of x for the specified mean and standard deviation.

xThe value that you want to standardize.
meanThe mean of the values.
standard_devThe standard deviation of the values.


STDEV(number1, [number2, ...])

Returns the standard deviation (based on a population sample) of a set of numbers.

number1, ...The numbers of which you want the standard deviation.


STDEV.P(number1, [number2, ...])

Returns the standard deviation (based on the entire population) of a set of numbers.

number1, ...The numbers of which you want the standard deviation.


STDEV.S(number1, [number2, ...])

Returns the standard deviation (based on a population sample) of a set of numbers.

number1, ...The numbers of which you want the standard deviation.


STDEVA(value1, [value2, ...])

Returns the standard deviation (based on a population sample) of a set of values.

value1, ...The values of which you want the standard deviation.


STDEVP(number1, [number2, ...])

Returns the standard deviation (based on the entire population) of a set of numbers.

number1, ...The numbers of which you want the standard deviation.


STDEVPA(value1, [value2, ...])

Returns the standard deviation (based on the entire population) of a set of values.

value1, ...The values of which you want the standard deviation.


STEYX(y_range, x_range)

Returns the standard error of the y values of a line passing through a specified set of points.

y_rangeThe y values that are already known.
x_rangeThe x values that are already known.


T.DIST(x, degrees_freedom, cumulative)

Returns the Student's left-tailed T distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe degrees of freedom.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


T.DIST.2T(x, degrees_freedom)

Returns the Student's two-tailed T distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe degrees of freedom.


T.DIST.RT(x, degrees_freedom)

Returns the Student's right-tailed T distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe degrees of freedom.


T.INV(probability, degrees_freedom)

Returns the value associated with the specified Student's left-tailed T distribution probability.

probabilityThe probability for which you want the value.
degrees_freedomThe degrees of freedom.


T.INV.2T(probability, degrees_freedom)

Returns the value associated with the specified Student's two-tailed T distribution probability.

probabilityThe probability for which you want the value.
degrees_freedomThe degrees of freedom.


T.TEST(array1, array2, tails, type)

Returns the probability result of the Student's T test.

array1The first range to be compared.
array2The second range to be compared.
tailsSpecifies the tails to include in the distribution. Should be 1 or 2.
typeSpecifies which type of test is required. The possible values are:
1Paired.
2Equal variance.
3Unequal variance.


TDIST(x, degrees_freedom, tails)

Returns the Student's T distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe degrees of freedom.
tailsSpecifies the tails to include in the distribution. Should be 1 or 2.


TINV(probability, degrees_freedom)

Returns the value associated with the specified Student's T distribution probability.

probabilityThe probability for which you want the value.
degrees_freedomThe degrees of freedom.


TREND(known_ys, [known_xs], [new_xs], [const])

Returns the expected values of y for given x values for a line passing through a specified set of points.

known_ysThe y values that are already known.
known_xsThe x values that are already known. If this parameter is omitted it defaults to an array of values from 1 to the number of known ys.
new_xsThe new x values for which y values are required. If this parameter is omitted it defaults to the known xs.
constSpecifies whether the line must pass through the origin. The possible values are:
FALSEThe line is forced to pass through the origin.
TRUEThe line is not forced to pass through the origin.
If this parameter is omitted it defaults to TRUE.


TRIMMEAN(array, percent)

Returns the mean of a set of numbers with the extreme values removed.

arrayAn array or reference to cells containing the numbers.
percentThe percentage of the numbers to exclude from the calculation.


TTEST(array1, array2, tails, type)

Returns the probability result of the Student's T test.

array1The first range to be compared.
array2The second range to be compared.
tailsSpecifies the tails to include in the distribution. Should be 1 or 2.
typeSpecifies which type of test is required. The possible values are:
1Paired.
2Equal variance.
3Unequal variance.


VAR(number1, [number2, ...])

Returns the variance (based on a population sample) of a set of numbers.

number1, ...The numbers of which you want the variance.


VAR.P(number1, [number2, ...])

Returns the variance (based on the entire population) of a set of numbers.

number1, ...The numbers of which you want the variance.


VAR.S(number1, [number2, ...])

Returns the variance (based on a population sample) of a set of numbers.

number1, ...The numbers of which you want the variance.


VARA(value1, [value2, ...])

Returns the variance (based on a population sample) of a set of values.

value1, ...The values of which you want the variance.


VARP(number1, [number2, ...])

Returns the variance (based on the entire population) of a set of numbers.

number1, ...The numbers of which you want the variance.


VARPA(value1, [value2, ...])

Returns the variance (based on the entire population) of a set of values.

value1, ...The values of which you want the variance.


WEIBULL(x, alpha, beta, cumulative)

Returns the Weibull distribution probability.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


WEIBULL.DIST(x, alpha, beta, cumulative)

Returns the Weibull distribution probability.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:
FALSEReturn the value of the function.
TRUEReturn the integral of the function.


Z.TEST(array, x, [sigma])

Returns the probability result of the z test.

arrayAn array or reference to cells containing the data against which x is to be tested.
xThe value to be tested.
sigmaThe population standard deviation. If this parameter is omitted it defaults to the sample standard deviation of the data.


ZTEST(array, x, [sigma])

Returns the probability result of the z test.

arrayAn array or reference to cells containing the data against which x is to be tested.
xThe value to be tested.
sigmaThe population standard deviation. If this parameter is omitted it defaults to the sample standard deviation of the data.


Text functions

CHAR(number)

Returns the character corresponding to the specified position in the character set.

numberThe number of the character that you want.


CLEAN(text)

Returns a string corresponding to the input string with any unprintable characters removed.

textThe text from which you want to remove unprintable characters.


CODE(text)

Returns the position in the character set of the first character in the string.

textThe character of which you want the character code.


CONCAT(text1, [text2, ...])

Returns a string consisting of the input strings concatenated together.

text1, ...The strings or ranges containing strings that you want to concatenate.


CONCATENATE(text1, [text2, ...])

Returns a string consisting of the input strings concatenated together.

text1, ...The strings that you want to concatenate.


DOLLAR(number, [decimals])

Returns a string consisting of the input number rounded to the specified number of decimal places and converted to currency format.

numberThe amount that you want to format.
decimalsThe number of decimal places required. If this parameter is omitted it defaults to 2.


EXACT(text1, text2)

Returns TRUE if the input strings are exactly the same, otherwise FALSE.

text1The first string to be compared.
text2The second string to be compared.


FIND(find_text, within_text, [start_num])

Returns a number corresponding to the position of one text string within another (case-sensitive).

find_textThe text you want to find.
within_textThe text to be searched.
start_numThe position at which to start searching. If this parameter is omitted it defaults to 1.


FIXED(number, [decimals], [no_commas])

Returns a string consisting of the input number rounded to the specified number of decimal places.

numberThe number that you want to format.
decimalsThe number of decimal places required. If this parameter is omitted it defaults to 2.
no_commasSpecifies whether to suppress the thousands separator in the formatted number. The possible values are:
FALSEThe thousands separator is used.
TRUEThe thousands separator is not used.
If this parameter is omitted it defaults to FALSE.


LEFT(text, [num_chars])

Returns a string consisting of the specified number of characters from the beginning of the input string.

textThe string of which you want the beginning.
num_charsThe number of characters to extract from the string. If this parameter is omitted it defaults to 1.


LEN(text)

Returns the length of the input string.

textThe string of which you want the length.


LOWER(text)

Returns a string which is the input string with all of the characters converted to lower case.

textThe string you want to convert.


MID(text, start_num, num_chars)

Returns a string consisting of the specified number of characters from the specified position of the input string.

textThe string of which you want the extract.
start_numThe starting position of characters to extract from the string.
num_charsThe number of characters to extract from the string.


NUMBERVALUE(text, [decimal_separator], [group_separator])

Returns a number from a string with optional separators.

textThe string containing the number.
decimal_separatorThe character to be used as the decimal separator. If this parameter is omitted it defaults to the current local decimal separator.
group_separatorThe character to be used as the group separator. If this parameter is omitted it defaults to the current local group separator.


PROPER(text)

Returns a string which is the input string with the first letter of each word converted to upper case and all of the other characters converted to lower case.

textThe string you want to convert.


REPLACE(old_text, start_num, num_chars, new_text)

Returns a string which is the input string with a specified portion replaced.

old_textThe string you want to convert.
start_numThe starting position of the text to be removed.
num_charsThe number of characters to be removed.
new_textThe text to be inserted into the string.


REPT(text, number_times)

Returns a string which is the input string repeated a specified number of times.

textThe string you want to repeat.
number_timesThe number of times to repeat the string.


RIGHT(text, [num_chars])

Returns a string consisting of the specified number of characters from the end of the input string.

textThe string of which you want the end.
num_charsThe number of characters to extract from the string. If this parameter is omitted it defaults to 1.


SEARCH(find_text, within_text, [start_num])

Returns a number corresponding to the position of one text string within another (not case-sensitive).

find_textThe text you want to find.
within_textThe text to be searched.
start_numThe position at which to start searching. If this parameter is omitted it defaults to 1.


SUBSTITUTE(text, old_text, new_text, [instance_num])

Returns a string which is the input string with specified text replaced.

textThe string you want to convert.
old_textThe string to be removed from the original text.
new_textThe string to be inserted in the original text.
instance_numSpecifies which occurrence of old_text is to be replaced. If this parameter is omitted then all occurrences are replaced.


T(value)

Returns a string which is the input value if it is text, otherwise an empty string.

valueThe value you want if it is a string.


TEXT(value, format_text)

Returns a string consisting of the input number formatted using the specified format string.

valueThe number that you want to format.
format_textThe format string to use.


TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

Returns a string consisting of the text values concatenated with the specified delimiter.

delimiterA string or reference to one or more cells that contain the delimiter(s) to be used.
ignore_emptySpecifies whether to ignore empty cells in the text references. The possible values are:
FALSEInclude empty cells when concatenating values.
TRUEExclude empty cells when concatenating values.
text1, ...The strings or references to one or more cells that you want to concatenate.


TRIM(text)

Returns a string consisting of the input string with any extra spaces removed.

textThe string that you want to format.


UNICHAR(number)

Returns a string which is the Unicode character for the specified code point.

numberThe code point that you want to convert.


UNICODE(text)

Returns a number which is the Unicode code point for the first character of the specified text.

textThe text that you want to convert.


UPPER(text)

Returns a string which is the input string with all of the characters converted to upper case.

textThe string that you want to convert.


VALUE(text)

Returns a number which corresponds to the value in the input string. You should not need to use this function, as values are automatically converted from text to numeric when required in formulas.

textThe string that you want to convert to a number.


Macro control functions

ARGUMENT([name_text], [type], [reference])

Defines an argument for a custom function.

name_textThe defined name that will be assigned to the argument. If this parameter is omitted then no name will be assigned.
typeThe acceptable data type(s) for the argument value. It can be any combination of the following (to specify more than one value, add the numbers together):
1Number
2Text
4Logical
8Reference
16Error
64Array
If this parameter is omitted it defaults to 7.
referenceThe cell reference where the argument value will be stored. If this parameter is omitted then the argument value will not be stored.


BREAK()

Ends the processing of a FOR, FOR.CELL or WHILE loop. The macro will continue with the statement after the NEXT function.



ELSE()

Introduces the block of statements to be processed when the corresponding IF or ELSE.IF condition is false.



ELSE.IF(logical_test)

Specifies another logical test that conditions a block of statements when the corresponding IF or ELSE.IF condition is false.

logical_testAn expression that results in TRUE or FALSE.


END.IF()

Specifies the end of the block of statements conditioned by the corresponding IF or ELSE.IF.



FOR(counter_text, start_num, end_num, [step_num])

Performs a group of instructions a specified number of times. The end of the group is indicated by a NEXT function.

counter_textA defined name that will be used to store the current index.
start_numThe initial value to be assigned to the counter.
end_numThe last value to be assigned to the counter.
step_numThe value to be added to the counter each time around the loop. If this parameter is omitted it defaults to 1.


FOR.CELL(ref_name, [area_ref], [skip_blanks])

Performs a group of instructions for each cell in a specified area. The end of the group is indicated by a NEXT function.

ref_nameA defined name that will be used to store the current reference.
area_refThe range of cells to be processed.
If this parameter is omitted it defaults to the current selection.
skip_blanksWhether blank cells are to be skipped. The possible values are:
FALSEAll cells in the area are processed
TRUEOnly the non-blank cells in the area are processed
If this parameter is omitted it defaults to FALSE.


GOTO(reference)

Forces the macro to continue with the statement at the reference.

referenceThe reference of the cell where macro execution is to continue.


HALT([cancel_close])

Terminates all running macros.

cancel_closeWhether, in an Auto_Close macro, to prevent the workbook from being closed (not currently implemented).


IF(logical_test)

Specifies a logical test that conditions the execution of a block of statements.

logical_testAn expression that results in TRUE or FALSE.


NEXT()

Specifies the end of the block of statements contained in a FOR, FOR.CELL or WHILE loop.



RESTART([level_num])

Specifies the number of levels that will be skipped when this macro returns.

level_numThe number of levels to skip. If this parameter is omitted then all levels are skipped.


RESULT([type_num])

Specifies the return type of a user-defined function.

type_numThe preferred data type(s) for the return value. It can be any combination of the following (to specify more than one value, add the numbers together):
1Number
2Text
4Logical
8Reference
16Error
64Array
If this parameter is omitted it defaults to 7.


RETURN([value])

Forces the macro to end, and if it is a custom function then to return the specified value.

valueFor custom functions, the value to be returned.


SET.NAME(name_text, [value])

Assigns a defined name to the specified value.

name_textThe defined name to use.
valueThe value to be associated with the name. If this parameter is omitted then the name is deleted.


SET.VALUE(reference, values)

Stores the specified values in the specified cells on the macro sheet. Do not use this function to try to update cells on a worksheet.

referenceThe cells where the values are to be stored.
valuesThe values to be stored.


VOLATILE([logical])

Specifies whether a user-defined function is to be recalculated each time the worksheet changes.

logicalWhether the function is volatile. The possible values are:
FALSEThe function is not volatile
TRUEThe function is volatile
If this parameter is omitted it defaults to TRUE.


WAIT([serial_number])

Forces the macro to wait until the date/time specified by the serial number.

serial_numberThe date/time when the macro is to resume. If this parameter is omitted the macro does not wait.


WHILE(logical_test)

Specifies a logical test that conditions the execution of a block of statements multiple times. The statements between the WHILE and NEXT functions will be repeated as long as the logical test returns the value TRUE.

logical_testAn expression that results in TRUE or FALSE.


Macro command functions

ABSREF(ref_text, reference)

Returns an absolute reference corresponding to an offset from another reference.

ref_textThe offset specified in R1C1 format.
referenceThe base reference.


ACTIVATE([window_text], [pane_num])

Specifies the workbook, sheet and/or pane that is to be active.

window_textThe workbook and/or sheet name. If this parameter is omitted then the active window is not changed.
pane_numFor a split window, specifies which pane is to be active. The possible values are:
1Upper or upper left.
2Upper right.
3Lower or lower left.
4Lower right.
If this parameter is omitted then the active pane is not changed.


ACTIVATE.NEXT([workbook_text])

Simulates the Ctrl+Page Down or Ctrl+Tab key combination.

workbook_textThe workbook for which the next sheet is to be selected. If this parameter is omitted then the next workbook is selected.


ACTIVATE.PREV([workbook_text])

Simulates the Ctrl+Page Up or Ctrl+Shift+Tab key combination.

workbook_textThe workbook for which the previous sheet is to be selected. If this parameter is omitted then the previous workbook is selected.


ACTIVE.CELL()

Returns a reference which represents the current position of the cursor on the active worksheet.



ALERT(message_text, [type_num], [help_ref])

Displays a message box containing the specified text. It returns TRUE if the OK button is pressed, otherwise FALSE.

message_textThe text to be displayed in the message box.
type_numThe type of message box to be displayed. The possible values are:
1A box containing a question mark icon and OK and Cancel buttons.
2A box containing an information icon and an OK button.
3A box containing an exclamation icon and an OK button.
If this parameter is omitted it defaults to 2.
help_refA reference to a Help topic (not currently implemented).


ALIGNMENT([horiz_align], [wrap], [vert_align], [orientation], [add_indent])

Simulates the Format Cells dialog Alignment page.

horiz_alignThe horizontal alignment to be used. The possible values are:
1General
2Left
3Center
4Right
5Fill
6Justify
7Center across selection
If this parameter is omitted the setting is not changed.
wrapWhether to wrap the text in the cells. The possible values are:
FALSEThe text is not wrapped
TRUEThe text is wrapped
If this parameter is omitted the setting is not changed.
vert_alignThe vertical alignment to be used. The possible values are:
1Top
2Center
3Bottom
4Justify
If this parameter is omitted the setting is not changed.
orientationThe text orientation. The possible valus are:
0Horizontal
1Vertical
2Upward
3Downward
If this parameter is omitted the setting is not changed.
add_indentNot currently implemented.


ASSIGN.TO.OBJECT([macro_ref])

Assigns a macro to an object.

macro_refThe reference of the macro to be assigned. If this parameter is omitted then any existing macro is removed.


ATTACH.TEXT(attach_to_num, [series_num], [point_num])

Attaches default text to the active chart. The SELECT and FORMULA functions can be used to change the text.

attach_to_numThe item where the default text is to be attached. The possible values are:
1Chart title
2Primary y-axis title
3Primary x-axis title
4Data label (not currently implemented)
5Secondary y-axis title (not currently implemented)
6Secondary x-axis title (not currently implemented)
series_numThe series number of the data label (not currently implemented)
point_numThe point number of the data label (not currently implemented)


AXES([x_primary], [y_primary], [x_secondary], [y_secondary])

Changes the active chart axes.

x_primaryWhether to draw the X-axis. The possible values are:
FALSEDo not draw the X-axis.
TRUEDo draw the X-axis.
If this parameter is omitted the setting is not changed.
y_primaryWhether to draw the Y-axis. The possible values are:
FALSEDo not draw the Y-axis.
TRUEDo draw the Y-axis.
If this parameter is omitted the setting is not changed.
x_secondaryWhether to draw the secondary X-axis (not currently implemented).
y_secondaryWhether to draw the secondary Y-axis (not currently implemented).


BEEP([tone_num])

Causes the computer to beep.

tone_numA number from 1 to 4 indicating which type of beep is to be produced (not currently implemented - all values will produce the same tone). If this parameter is omitted it defaults to 1.


BORDER([outline], [left], [right], [top], [bottom], [shade], [outline_color], [left_color], [right_color], [top_color], [bottom_color])

Simulates the Format Cells dialog Border page.

outlineThe border type to be applied to the outline of the selected cells. The possible values are:
0No border
1Thin
2Medium
3Dashed
4Dotted
5Thick
6Double
7Hairline
If this parameter is omitted the setting is not changed.
leftThe border type to be used for the left border of the selected cells. The possible values are the same as for the outline parameter.
If this parameter is omitted the setting is not changed.
rightThe border type to be used for the right border of the selected cells. The possible values are the same as for the outline parameter.
If this parameter is omitted the setting is not changed.
topThe border type to be used for the top border of the selected cells. The possible values are the same as for the outline parameter.
If this parameter is omitted the setting is not changed.
bottomThe border type to be used for the bottom border of the selected cells. The possible values are the same as for the outline parameter.
If this parameter is omitted the setting is not changed.
shadeNot implemented.
outline_colorThe color to be used for the outline border. The possible values are:
0Automatic
1 to 56One of the colors displayed in the Format Cells dialog Border page
If this parameter is omitted the setting is not changed.
left_colorThe color to be used for the left border. The possible values are the same as for the outline_color parameter.
If this parameter is omitted the setting is not changed.
right_colorThe color to be used for the right border. The possible values are the same as for the outline_color parameter.
If this parameter is omitted the setting is not changed.
top_colorThe color to be used for the top border. The possible values are the same as for the outline_color parameter.
If this parameter is omitted the setting is not changed.
bottom_colorThe color to be used for the bottom border. The possible values are the same as for the outline_color parameter.
If this parameter is omitted the setting is not changed.


BRING.TO.FRONT()

Changes the order of the objects on the active worksheet by moving the currently selected object to the front.



CALCULATE.DOCUMENT()

Causes the current worksheet to be recalculated.



CALCULATE.NOW()

Causes all worksheets to be recalculated.



CALLER()

Returns information about the caller of the macro.

Macro called from:Function returns:
User-defined functionreference of cell containing function call
User-defined function in array formulareference of array formula range
Attached to control or drawing objectobject identifier
Auto_Open macrofile name
Manually (control key combination)error value #REF!
ON.DOUBLECLICK macroreference of cell that was double-clicked
ON.ENTRY macroreference of cell where data was entered
ON.SHEET macroerror value #REF!


CANCEL.KEY(enable_logical, [macro_ref])

Specifies the action taken when the Escape key is pressed in a macro.

enable_logicalWhether to interrupt the macro. The possible values are:
FALSEThe macro continues
TRUEThe macro is interrupted
macro_refThe macro to be run when the Escape key is pressed (not currently implemented)


CELL.PROTECTION([locked], [hidden])

Simulates the Format Cells dialog Protection page.

lockedWhether the cell should be locked when the sheet is protected. The possible values are:
FALSEThe cell is not locked
TRUEThe cell is locked
If this parameter is omitted the setting is not changed.
hiddenWhether the cell formula should be hidden when the sheet is protected. The possible values are:
FALSEThe cell formula is not hidden
TRUEThe cell formula is hidden
If this parameter is omitted the setting is not changed.


CHECKBOX.PROPERTIES([value], [link], [accel_text], [accel_text2], [3d_shading])

Changes the properties of a check box or option button control.

valueThe value of the control. The possible values are:
FALSEUnchecked
TRUEChecked
0Unchecked
1Checked
2Mixed
If this parameter is omitted the value is not changed.
linkThe cell that is updated by the control. If this parameter is omitted the link is not changed.
accel_textThe accelerator key on a dialog sheet (not currently implemented).
accel_text2The second accelerator key on a dialog sheet (not currently implemented).
3d_shadingWhether to draw the control with 3d shading. The possible values are:
FALSE2d
TRUE3d
If this parameter is omitted the setting is not changed.


CLEAR([type_num])

Simulates the Edit Clear menu options.

type_numThe type of information to clear from the current selection on the active worksheet. The possible values are:
1Clear all
2Clear formats
3Clear contents
4Clear comments
If this parameter is omitted it defaults to 3.


COLOR.PALETTE(file_text)

Copies the color palette from another file.

file_textThe name of the file from which to copy the palette. The file must already be open. If this parameter is an empty string then the colors are reset to the default values.


COLUMN.WIDTH([width_num], [reference], [standard], [type_num], [standard_num])

Simulates the Format Column menu options.

width_numThe new width of the columns. The width is measured in characters. This parameter is ignored if standard is TRUE or type_num is specified. If this parameter is omitted the width of the specified columns is not changed.
referenceThe columns whose width is to be changed. If this parameter is omitted it defaults to the current selection on the active worksheet.
standardSpecifies whether the columns are to be set to the standard column width. The possible values are:
TRUEThe columns are set to the standard width.
FALSEThe columns are not set to the standard width.
If this parameter is omitted it defaults to FALSE.
type_numSpecifies whether the columns are to be hidden, unhidden or automatically sized. This parameter is ignored if standard is TRUE. The possible values are:
1The columns are hidden.
2The columns are unhidden.
3The columns are set to the width of the longest displayed value.
If this parameter is omitted the columns will not be hidden, unhidden or auto-sized.
standard_numSpecifies the value to be used for the standard width. The width is measured in characters. If this parameter is omitted the standard width is not changed.


COPY([from_reference], [to_reference])

Simulates the Edit Copy menu option.

from_referenceThe cells that are to be copied to the clipboard. If this parameter is omitted it defaults to the current selection on the active worksheet.
to_referenceThe cells where the copied information is to be pasted. If this parameter is omitted then the cells are not pasted.


CREATE.OBJECT(obj_type, ref1, [x_offset1], [y_offset1], ref2, [x_offset2], [y_offset2], [text], [fill], [editable])

Simulates the Insert Drawing and Insert Control menu options.

obj_typeThe type of object to be created. The possible values are:
1Line
2Rectangle
3Oval
6Text box
7Command button
11Check box
12Option button
14Label
16Spin control
17Scroll bar
18List box
19Group box
20Combo box
ref1The cell containing the top-left corner of the object.
x_offset1The offset in points of the top-left corner of the object from the left edge of the cell. If this parameter is omitted it defaults to 0.
y_offset1The offset in points of the top-left corner of the object from the top edge of the cell. If this parameter is omitted it defaults to 0.
ref2The cell containing the bottom-right corner of the object.
x_offset2The offset in points of the bottom-right corner of the object from the left edge of the cell. If this parameter is omitted it defaults to 0.
y_offset2The offset in points of the bottom-right corner of the object from the top edge of the cell. If this parameter is omitted it defaults to 0.
textThe text to be assigned to the object. If this parameter is omitted then no text is assigned.
fillSpecifies whether the object is filled or transparent (not currently implemented).
editableSpecifies whether a combo box is editable (not currently implemented).


CREATE.OBJECT(obj_type, ref1, [x_offset1], [y_offset1], ref2, [x_offset2], [y_offset2], array, [fill])

Simulates the Insert Drawing and Insert Control menu options.

obj_typeThe type of object to be created. The possible values are:
9Closed polygon
10Open polygon
ref1The cell containing the top-left corner of the object.
x_offset1The offset in points of the top-left corner of the object from the left edge of the cell. If this parameter is omitted it defaults to 0.
y_offset1The offset in points of the top-left corner of the object from the top edge of the cell. If this parameter is omitted it defaults to 0.
ref2The cell containing the bottom-right corner of the object.
x_offset2The offset in points of the bottom-right corner of the object from the left edge of the cell. If this parameter is omitted it defaults to 0.
y_offset2The offset in points of the bottom-right corner of the object from the top edge of the cell. If this parameter is omitted it defaults to 0.
arrayAn array or reference with 2 columns and at least 2 rows, containing the x and y points of the polygon.
fillSpecifies whether the object is filled or transparent (not currently implemented).


CUT([from_reference], [to_reference])

Simulates the Edit Cut menu option.

from_referenceThe cells that are to be cut to the clipboard. If this parameter is omitted it defaults to the current selection on the active worksheet.
to_referenceThe cells where the cut information is to be pasted. If this parameter is omitted then the cells are not pasted.


DATA.FORM()

Simulates the Format Data Form menu option.



DATA.LABEL([show_option], [auto_text], [show_key])

Changes the active chart data labels.

show_optionThe type of label to show. The possible values are:
1None
2Show value
3Show percent
4Show label
5Show label and percent
If this parameter is omitted the data labels are not changed.
auto_textWhether to reset the data labels (not currently implemented).
show_keyWhether to show the legend key next to the label (not currently implemented).


DATA.SERIES([rowcol], [type_num], [date_num], [step_value], [stop_value], [trend])

Simulates the Edit Fill Series menu option.

rowcolIndicates whether the series is in rows or columns. The possible values are:
1Rows
2Columns
If this parameter is omitted it defaults depending on the shape of the current selection.
type_numThe way in which the series values change. The possible values are:
1Linear
2Growth
3Date
4Auto (not currently implemented)
If this parameter is omitted it defaults to 1.
date_numThe way in which the date values change. The possible values are:
1Day
2Weekday
3Month
4Year
If this parameter is omitted it defaults to 1.
step_valueThe number used to add to or multiply by the series value. If this parameter is omitted it defaults to 1.
stop_valueThe value at which to stop filling the series. If this parameter is omitted then the entire selection is filled.
trendIndicates whether to fill the series using trend values calculated from existing data. The possible values are:
FALSEDon't use trend values.
TRUEUse trend values (not currently implemented).
If this parameter is omitted it defaults to FALSE.


DEFINE.NAME(name_text, [refers_to], [macro_type], [shortcut_text], [hidden], [category], [local])

Simulates the Insert Name Define Add menu option.

name_textA string containing the name to be defined.
refers_toThe value that is to be assigned to the name. If this parameter is omitted it defaults to a reference to the current selection on the active sheet.
macro_typeThe type of macro that this name refers to (not currently implemented).
shortcut_textThe shortcut key used to activate a command macro. If this parameter is omitted no shortcut key is assigned.
hiddenWhether the name is to be hidden in the name definition list. The possible values are:
FALSEShow the name in the list.
TRUEDo not show the name in the list.
If this parameter is omitted it defaults to FALSE.
categoryIdentifies the category of a custom function (not currently implemented).
localWhether the name is to local to the active sheet. The possible values are:
FALSEThe name is defined as global.
TRUEThe name is defined as local.
If this parameter is omitted it defaults to FALSE.


DELETE.FORMAT(format_text)

Deletes the specified custom number format. Any cells using this format will be changed to use the General format.

format_textA string containing the format to be deleted.


DELETE.NAME(name_text)

Simulates the Insert Name Define Delete menu option.

name_textA string containing the name to be deleted.


DEREF(reference)

Returns the values of cells in a reference. You should not need to use this function, as references are automatically converted to values where necessary when they are used in formulas.

referenceThe reference to the cells whose values you want.


DIRECTORY([path_text])

Returns the path of the current directory, and optionally allows you to change it.

path_textThe path to which you want to change the current directory. If this parameter is omitted the current directory is not changed.


DOCUMENTS([type_num], [match_text])

Returns a horizontal array containing the names of the open files.

type_numSpecifies whether to include add-in workbooks in the list. The possible values are:
1List only open files that are not add-in workbooks.
2List only open files that are add-in workbooks.
3List all open files.
If this parameter is omitted it defaults to 1.
match_textA template name that may contain wildcard characters. If this parameter is omitted then all workbook names will be included.


DUPLICATE()

Duplicates the currently selected object.



ECHO([logical])

Controls screen updating while a macro is running.

logicalWhether to update the screen while the macro is running. The possible values are:
FALSEDo not update the screen
TRUEDo update the screen
If this parameter is omitted the setting is toggled.


EDIT.COLOR(color_num, [red_value], [green_value], [blue_value])

Changes the color associated with a specific color number.

color_numA number from 1 to 56, indicating which color is to be changed.
red_valueA number from 0 to 255, specifying the intensity of the red component. If this parameter is omitted the red component is not changed.
green_valueA number from 0 to 255, specifying the intensity of the green component. If this parameter is omitted the green component is not changed.
blue_valueA number from 0 to 255, specifying the intensity of the blue component. If this parameter is omitted the blue component is not changed.


EDIT.DELETE([shift_num])

Simulates the Edit Delete menu option.

shift_numThe way in which the cell deletion is to be processed. The possible values are:
1Shift cells left
2Shift cells up
3Delete entire rows
4Delete entire columns
If this parameter is omitted it defaults to 2.


EDIT.SERIES([series_num], [name_ref], [x_ref], [y_ref], [z_ref], [plot_order])

Adds or changes a series on the active chart.

series_numThe number of the series to be changed. If this parameter is zero or omitted then a new series is added.
name_refThe series name. If this parameter is omitted the name is not changed.
x_refThe X categories or values reference. If this parameter is omitted the reference is not changed.
y_refThe Y values reference. If this parameter is omitted the reference is not changed.
z_refThe Z values reference (not currently implemented).
plot_orderThe order of this series on the chart (not currently implemented).


ENABLE.OBJECT([object_id], [enable_logical])

Specifies whether an object should respond to mouse clicks.

object_idThe object to be enabled or disabled. If this parameter is omitted the currently selected object is used.
enable_logicalWhether the object is enabled. The possible values are:
TRUEThe object is enabled
FALSEThe object is disabled
If this parameter is omitted the setting is not changed.


ERROR(enable_logical, [macro_ref])

Specifies the action taken when an error occurs in a macro.

enable_logicalWhether to interrupt the macro. The possible values are:
FALSEThe macro continues
TRUEThe macro is interrupted
macro_refThe macro to be run when an error occurs (not currently implemented)


EVALUATE(formula_text)

Returns the result of evaluating a formula.

formula_textThe formula to be evaluated.


EXEC(program_text, [window_num])

Executes another program.

program_textThe program to be executed.
window_numThe window state for the executed program. The possible values are:
1Normal
2Minimized
3Maximized
If this parameter is omitted it defaults to 2.


EXTEND.POLYGON(array)

Adds points to a polygon created by the CREATE.OBJECT function.

arrayAn array or reference with 2 columns, containing the x and y points of the polygon.


FCLOSE(file_num)

Closes a file that was previously opened by the FOPEN function.

file_numThe file number that was returned by the FOPEN function.


FILE.CLOSE([save_logical], [route_logical])

Simulates the File Close menu option.

save_logicalWhether to save a changed file before closing it. The possible values are:
FALSEDo not save the file
TRUESave the file
If this parameter is omitted then the prompt is displayed.
route_logicalWhether to route the file after closing it (not currently implemented).


FILE.DELETE(file_text)

Deletes a file.

file_textThe name of the file to be deleted.


FILES([directory_text])

Returns a horizontal array containing the names of the files in a directory.

directory_textA template name that may contain wildcard characters. If this parameter is omitted then all file names will be included.


FILL.DOWN()

Simulates the Edit Fill Down menu option.



FILL.LEFT()

Simulates the Edit Fill Left menu option.



FILL.RIGHT()

Simulates the Edit Fill Right menu option.



FILL.UP()

Simulates the Edit Fill Up menu option.



FILTER([field_num], [criteria1], [operation], [criteria2])

Simulates the Format Data Filter AutoFilter menu option.

field_numThe filter column number whose criteria are to be changed. The columns are number from left to right, with 1 being the first filtered column. If this parameter is omitted the filter is just turned on or off.
criteria1A string specifying the criteria to be used. If this parameter is omitted any filter criteria are removed from the specified column.
operationThe combination operation when multiple criteria are used. The possible values are:
1AND
2OR
If this parameter is omitted the second criteria is not used.
criteria2A string specifying the criteria to be used. If this parameter is omitted the second criteria is not used.


FILTER.ADVANCED(operation, list_ref, [criteria_ref], [copy_ref], [unique])

Simulates the Format Data Filter Advanced filter menu option.

operationWhether to filter in place or copy to another location. The possible values are:
1Filter the list in place
2Copy the list to another location
list_refThe location of the list to be filtered.
criteria_refThe location of any filter criteria to be used. If this parameter is omitted no criteria will be applied.
copy_refThe destination of the copied data. This is only used when operation is 2.
uniqueWhether to select unique records only. The possible values are:
FALSEDisplay all records that match the criteria.
TRUEDisplay only unique records that match the criteria.
If this parameter is omitted it defaults to FALSE.


FILTER.SHOW.ALL()

Simulates the Format Data Filter Show All menu option.



FONT.PROPERTIES([font], [font_style], [size], [strikethrough], [superscript], [subscript], [outline], [shadow], [underline], [color], [normal], [background], [start_char], [char_count])

Simulates the Format Cells dialog Font page or changes the font of the selected chart text.

fontThe name of the font to be used.
font_styleThe style to be used. The possible values are:
"Regular"Removes the bold and italic attributes.
"Bold"Sets the font to bold.
"Italic"Sets the font to italic.
"Bold Italic"Sets the font to bold and italic.
If this parameter is omitted the style is not changed.
sizeThe point size to be used. If this parameter is omitted the size is not changed.
strikethroughSets the strikethrough attribute. The possible values are:
FALSEThe font does not have the strikethrough attribute
TRUEThe font has the strikethrough attribute
If this parameter is omitted the strikethrough attribute is not changed.
superscriptNot currently implemented.
subscriptNot currently implemented.
outlineNot currently implemented.
shadowNot currently implemented.
underlineThe underline style to be used. The possible values are:
0None
1Single
2Double (not currently implemented)
3Single accounting (not currently implemented)
4Double accounting (not currently implemented)
If this parameter is omitted the underline style is not changed.
colorThe color to be used. The possible values are:
0Automatic
1 to 56One of the colors displayed in the Format Cells dialog Font page
If this parameter is omitted the color is not changed.
normalNot currently implemented.
backgroundNot currently implemented.
start_charNot currently implemented.
char_countNot currently implemented.


FOPEN(file_text, [access_num])

Opens a file for reading or writing. The return value is a file number that can be passed as a parameter to the FREAD, FREADLN, FWRITE, FWRITELN, FPOS, FSIZE and FCLOSE functions.

file_textThe name of the file to open.
access_numThe access required to the file. The possible values are:
1Open the file for reading and writing. The file must already exist.
2Open the file for reading only. The file must already exist.
3Create a new file for reading and writing. The file will be cleared if it already exists.
If this parameter is omitted it defaults to 1.


FORMAT.LEGEND(position_num)

Changes the active chart legend placement.

position_numThe placement of the legend. The possible values are:
1Bottom
2Corner
3Top
4Right
5Left


FORMAT.MOVE([x_offset], [y_offset], [reference])

Moves the currently selected object.

x_offsetThe offset in points of the top-left corner of the object from the left edge of the cell. If this parameter is omitted it defaults to 0.
y_offsetThe offset in points of the top-left corner of the object from the top edge of the cell. If this parameter is omitted it defaults to 0.
referenceThe cell containing the top-left corner of the object. If this parameter is omitted it defaults to A1.


FORMAT.NUMBER(format_text)

Simulates the Format Cells dialog Number page.

format_textThe number format to be used.


FORMAT.SHAPE(vertex_num, insert, [reference], [x_offset], [y_offset])

Inserts, moves or deletes polygon points.

vertex_numThe number of the vertex to be inserted, moved or deleted.
insertWhether the vertex is to be inserted, moved or deleted. The possible values are:
FALSEThe vertex is to be deleted (if none of the subsequent parameters are entered) or moved (if any of the remaining parameters are entered)
TRUEThe vertex is to be inserted
referenceThe cell from which the insert or move position is measured. If this parameter is omitted it defaults to the top left corner of the polygon.
x_offsetThe horizontal offset from the reference, measured in points. If this parameter is omitted it defaults to zero.
y_offsetThe vertical offset from the reference, measured in points. If this parameter is omitted it defaults to zero.


FORMAT.TEXT([x_align], [y_align], [orient_num], [auto_text], [auto_size], [show_key], [show_value], [add_indent])

Formats the text in a text box or command button.

x_alignThe horizontal alignment. The possible values are:
1Left
2Center
3Right
4Justify
If this parameter is omitted the setting is not changed.
y_alignThe vertical alignment. The possible values are:
1Top
2Center
3Bottom
4Justify
If this parameter is omitted the setting is not changed.
orient_numThe text orientation (not currently implemented).
auto_textResets data label text (not currently implemented).
auto_sizeResets the border around the text (not currently implemented).
show_keyShows legend key for data labels (not currently implemented).
show_valueShows values for data labels (not currently implemented).
add_indentUsed for Far East versions only (not currently implemented).


FORMULA(formula_text, [reference])

Enters the specified formula in the specified reference. Any references in the formula must be in R1C1 format. The FORMULA.CONVERT function can be used to convert references.

formula_textThe formula to be inserted.
referenceThe reference where formula to be inserted. If this parameter is omitted it defaults to the cursor position on the active worksheet.


FORMULA.ARRAY(formula_text, [reference])

Enters the specified array formula in the specified reference. Any references in the formula must be in R1C1 format. The FORMULA.CONVERT function can be used to convert references.

formula_textThe formula to be inserted.
referenceThe reference where formula to be inserted. If this parameter is omitted it defaults to the current selection on the active worksheet.


FORMULA.CONVERT(formula_text, from_a1, [to_a1], [to_ref_type], [rel_to_ref])

Converts the references in the specified formula from one type to another.

formula_textThe formula to be converted.
from_a1Whether references in the formula are in A1 or R1C1 format. The possible values are:
FALSEThe references are in R1C1 format
TRUEThe references are in A1 format
to_a1Whether references in the formula are converted to A1 or R1C1 format. The possible values are:
FALSEThe references are converted to R1C1 format
TRUEThe references are converted to A1 format
If this parameter is omitted the format is not changed.
to_ref_typeWhether references in the formula are converted to use absolute or relative reference type. The possible values are:
1Absolute row, absolute column
2Absolute row, relative column
3Relative row, absolute column
4Relative row, relative column
If this parameter is omitted the reference type is not changed.
rel_to_refThe cell that R1C1 references are relative to. If this parameter is omitted it defaults to the cell containing this function.


FORMULA.FILL(formula_text, [reference])

Enters the specified formula in the specified range. Any references in the formula must be in R1C1 format. The FORMULA.CONVERT function can be used to convert references.

formula_textThe formula to be inserted.
referenceThe reference where formula to be inserted. If this parameter is omitted it defaults to the current selection on the active worksheet.


FORMULA.FIND(text, in_num, at_num, by_num, [dir_num], [match_case])

Simulates the Edit Find menu option.

textThe text that you want to find.
in_numWhere you want to look for the text. The possible values are:
1Formulas
2Values
3Comments
at_numWhether to match the text against all or part of a cell. The possible values are:
1Entire cells only
2Any part of the text in a cell
by_numWhether to search by rows or by columns. The possible values are:
1By rows
2By columns
dir_numWhether to search for the next or previous match. The possible values are:
1Next
2Previous
If this parameter is omitted it defaults to 1.
match_caseWhether the search is case-sensitive. The possible values are:
TRUEThe search is case-sensitive
FALSEThe search is not case-sensitive
If this parameter is omitted it defaults to FALSE.


FORMULA.FIND.NEXT()

Finds the next cell based on the current criteria.



FORMULA.FIND.PREV()

Finds the previous cell based on the current criteria.



FORMULA.GOTO([reference], [corner])

Simulates the Edit Goto menu option.

referenceThe cells that are to be selected. If the sheet name is omitted then it defaults to the currently active sheet. If this parameter is omitted it defaults to the sheet and cells that were selected before the last goto command.
cornerSpecifies whether the top-left corner of the selection is to be placed in the top-left corner of the window. The possible values are:
FALSEThe sheet is not scrolled to move the selection to the top-left corner of the window.
TRUEThe sheet is scrolled to move the selection to the top-left corner of the window.
If this parameter is omitted it defaults to FALSE.


FORMULA.REPLACE(find_text, replace_text, [look_at], [look_by], [active_cell], [match_case])

Simulates the Edit Find/Replace menu option.

find_textThe text that you want to find.
replace_textThe text to replace the found text with.
look_atWhether to match the text against all or part of a cell. The possible values are:
1Entire cells only
2Any part of the text in a cell
If this parameter is omitted it defaults to 1.
look_byWhether to search by rows or by columns. The possible values are:
1By rows
2By columns
If this parameter is omitted it defaults to 1.
active_cellWhether to replace in just the current cell or in the whole sheet. The possible values are:
TRUEText in the active cell is replaced
FALSEText in the whole sheet is replaced
If this parameter is omitted it defaults to FALSE.
match_caseWhether the search is case-sensitive. The possible values are:
TRUEThe search is case-sensitive
FALSEThe search is not case-sensitive
If this parameter is omitted it defaults to the value last used.


FPOS(file_num, [position_num])

Returns the current read/write position in a file, and optionally allows you to change it.

file_numThe file number that was returned by the FOPEN function.
position_numThe location where the next read or write will occur.
If this parameter is omitted the current position is not changed.


FREAD(file_num, num_chars)

Returns a string of characters from a file.

file_numThe file number that was returned by the FOPEN function.
num_charsThe number of characters to read.


FREADLN(file_num)

Returns a string of characters from a file, up to the next end of line marker.

file_numThe file number that was returned by the FOPEN function.


FREEZE.PANES([logical], [col_split], [row_split])

Simulates the Format Freeze Panes menu option.

logicalWhether to freeze or unfreeze the panes. The possible values are:
FALSEUnfreeze the panes
TRUEFreeze the panes
If this parameter is omitted the setting is toggled.
col_splitThe number of columns to be frozen. If this parameter is omitted all columns left of the cursor cell are frozen.
row_splitThe number of rows to be frozen. If this parameter is omitted all rows above the cursor cell are frozen.


FSIZE(file_num)

Returns a size of a file.

file_numThe file number that was returned by the FOPEN function.


FULL.SCREEN([logical])

Simulates the Format Program Full screen menu option.

logicalWhether to switch to or from full screen mode. The possible values are:
FALSESwitch from full screen mode
TRUESwitch to full screen mode
If this parameter is omitted it defaults to TRUE.


FWRITE(file_num, text)

Writes a string of characters to a file.

file_numThe file number that was returned by the FOPEN function.
textThe text to be written.


FWRITELN(file_num, text)

Writes a string of characters to a file, followed by an end of line marker.

file_numThe file number that was returned by the FOPEN function.
textThe text to be written.


GALLERY.AREA(type_num, [delete_overlay])

Changes the active chart to an area chart.

type_numThe format to be applied to the chart. The possible values are:
1no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, no drop lines
2no horizontal gridlines, no vertical gridlines, 100% y axis, no data labels, no drop lines (not currently implemented)
3no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, drop lines (not currently implemented)
4horizontal gridlines, vertical gridlines, linear y axis, no data labels, no drop lines
5no horizontal gridlines, no vertical gridlines, linear y axis, data labels, no drop lines
delete_overlayWhether to delete any overlays (not currently implemented).


GALLERY.BAR(type_num, [delete_overlay])

Changes the active chart to a bar chart.

type_numThe format to be applied to the chart. The possible values are:
1no horizontal gridlines, no vertical gridlines, linear x axis, no data labels, not stacked, no series lines
2no horizontal gridlines, no vertical gridlines, linear x axis, no data labels, not stacked, no series lines
3no horizontal gridlines, no vertical gridlines, linear x axis, no data labels, stacked, no series lines (not currently implemented)
4no horizontal gridlines, no vertical gridlines, linear x axis, no data labels, not stacked, no series lines
5no horizontal gridlines, no vertical gridlines, 100% x axis, no data labels, stacked, no series lines (not currently implemented)
6no horizontal gridlines, vertical gridlines, linear x axis, no data labels, not stacked, no series lines
7no horizontal gridlines, no vertical gridlines, linear x axis, data values, not stacked, no series lines
8no horizontal gridlines, no vertical gridlines, linear x axis, no data labels, not stacked, no series lines
9no horizontal gridlines, no vertical gridlines, linear x axis, no data labels, stacked, series lines (not currently implemented)
10no horizontal gridlines, no vertical gridlines, 100% x axis, no data labels, stacked, series lines (not currently implemented)
delete_overlayWhether to delete any overlays (not currently implemented).


GALLERY.COLUMN(type_num, [delete_overlay])

Changes the active chart to a column chart.

type_numThe format to be applied to the chart. The possible values are:
1no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, not stacked, no series lines
2no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, not stacked, no series lines
3no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, stacked, no series lines (not currently implemented)
4no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, not stacked, no series lines
5no horizontal gridlines, no vertical gridlines, 100% y axis, no data labels, stacked, no series lines (not currently implemented)
6horizontal gridlines, no vertical gridlines, linear y axis, no data labels, not stacked, no series lines
7no horizontal gridlines, no vertical gridlines, linear y axis, data values, not stacked, no series lines
8no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, not stacked, no series lines
9no horizontal gridlines, no vertical gridlines, linear y axis, no data labels, stacked, series lines (not currently implemented)
10no horizontal gridlines, no vertical gridlines, 100% y axis, no data labels, stacked, series lines (not currently implemented)
delete_overlayWhether to delete any overlays (not currently implemented).


GALLERY.DOUGHNUT(type_num, [delete_overlay])

Changes the active chart to a doughnut chart.

type_numThe format to be applied to the chart. The possible values are:
1no data labels, not exploded
2data labels, not exploded
3no data labels, first segment exploded (not currently implemented)
4no data labels, all segments exploded (not currently implemented)
5data labels, not exploded
6data percentages, not exploded
7data labels and percentages, not exploded
delete_overlayWhether to delete any overlays (not currently implemented).


GALLERY.LINE(type_num, [delete_overlay])

Changes the active chart to a line chart.

type_numThe format to be applied to the chart. The possible values are:
1markers, straight lines, no horizontal gridlines, no vertical gridlines, linear y axis, no hi-lo lines
2no markers, straight lines, no horizontal gridlines, no vertical gridlines, linear y axis, no hi-lo lines
3markers, no lines, no horizontal gridlines, no vertical gridlines, linear y axis, no hi-lo lines
4markers, straight lines, horizontal gridlines, no vertical gridlines, linear y axis, no hi-lo lines
5markers, straight lines, horizontal gridlines, vertical gridlines, linear y axis, no hi-lo lines
6markers, straight lines, horizontal gridlines, no vertical gridlines, logarithmic y axis, no hi-lo lines
7markers, no lines, no horizontal gridlines, no vertical gridlines, linear y axis, hi-lo lines (not currently implemented)
8high-low-close stock chart (not currently implemented)
9open-high-low-close stock chart (not currently implemented)
10no markers, curved lines, no horizontal gridlines, no vertical gridlines, linear y axis, no hi-lo lines (not currently implemented)
delete_overlayWhether to delete any overlays (not currently implemented).


GALLERY.PIE(type_num, [delete_overlay])

Changes the active chart to a pie chart.

type_numThe format to be applied to the chart. The possible values are:
1no data labels, not exploded
2data labels, not exploded
3no data labels, first segment exploded (not currently implemented)
4no data labels, all segments exploded (not currently implemented)
5data labels, not exploded
6data percentages, not exploded
7data labels and percentages, not exploded
delete_overlayWhether to delete any overlays (not currently implemented).


GALLERY.RADAR(type_num, [delete_overlay])

Changes the active chart to a radar chart.

type_numThe format to be applied to the chart. The possible values are:
1markers, no horizontal gridlines, linear y axis, not filled
2no markers, no horizontal gridlines, linear y axis, not filled
3no markers, no horizontal gridlines, no y axis, not filled
4no markers, horizontal gridlines, linear y axis, not filled
5no markers, horizontal gridlines, logarithmic y axis, not filled
6no markers, no horizontal gridlines, linear y axis, filled (not currently implemented)
delete_overlayWhether to delete any overlays (not currently implemented).


GALLERY.SCATTER(type_num, [delete_overlay])

Changes the active chart to a scatter (XY) chart.

type_numThe format to be applied to the chart. The possible values are:
1markers, no lines, no horizontal gridlines, no vertical gridlines, linear y axis, linear x axis
2markers, straight lines, no horizontal gridlines, no vertical gridlines, linear y axis, linear x axis
3markers, no lines, horizontal gridlines, vertical gridlines, linear y axis, linear x axis
4markers, no lines, horizontal gridlines, no vertical gridlines, logarithmic y axis, linear x axis
5markers, no lines, horizontal gridlines, vertical gridlines, logarithmic y axis, logarithmic x axis
6no markers, curved lines, no horizontal gridlines, no vertical gridlines, linear y axis, linear x axis (not currently implemented)
delete_overlayWhether to delete any overlays (not currently implemented).


GET.CELL(type_num, [reference])

Returns information about the specified cell.

type_numThe type of information to return. The possible values are:
1Absolute reference of the upper-left cell in reference, as text
2Row number of the top cell in reference
3Column number of the leftmost cell in reference
4Same as TYPE(reference)
5Contents of reference
6Formula in reference, as text
7Number format of the cell, as text
8Number indicating the cell's horizontal alignment
9Number indicating the left-border style assigned to the cell
10Number indicating the right-border style assigned to the cell
11Number indicating the top-border style assigned to the cell
12Number indicating the bottom-border style assigned to the cell
13Number from 0 to 18, indicating the pattern of the selected cell
14If the cell is locked, returns TRUE; otherwise, returns FALSE
15If the cell's formula is hidden, returns TRUE; otherwise, returns FALSE
16A two-item horizontal array containing the width of the active cell and a logical value indicating whether the cell's width is set to change as the standard width changes (TRUE) or is a custom width (FALSE)
17Row height of cell, in points
18Name of font, as text
19Size of font, in points
20If the cell is bold returns TRUE; otherwise returns FALSE
21If the cell is italic returns TRUE; otherwise returns FALSE
22If the cell is underlined returns TRUE; otherwise returns FALSE
23If the cell is struck out returns TRUE; otherwise returns FALSE
24Font color of cell, as a number in the range 1 to 56. If font color is automatic, returns 0
25If the cell is outlined returns TRUE; otherwise returns FALSE
26If the cell is shadowed returns TRUE; otherwise returns FALSE
32Name of the workbook and sheet containing the cell
33If the cell is formatted to wrap, returns TRUE; otherwise, returns FALSE
34Left-border color as a number in the range 1 to 56. If color is automatic, returns 0
35Right-border color as a number in the range 1 to 56. If color is automatic, returns 0
36Top-border color as a number in the range 1 to 56. If color is automatic, returns 0
37Bottom-border color as a number in the range 1 to 56. If color is automatic, returns 0
38Shade foreground color as a number in the range 1 to 56. If color is automatic, returns 0
39Shade background color as a number in the range 1 to 56. If color is automatic, returns 0
42The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell
43The vertical distance, measured in points, from the top edge of the active window to the top edge of the cell
44The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell
45The vertical distance, measured in points, from the top edge of the active window to the bottom edge of the cell
46If the cell contains a comment, returns TRUE; otherwise, returns FALSE
48If the cell contains a formula, returns TRUE; if a constant, returns FALSE
49If the cell is part of an array, returns TRUE; otherwise, returns FALSE
50Number indicating the cell's vertical alignment
51Number indicating the cell's text orientation
53Contents of the cell as it is currently displayed, as text
57If the cell is superscript returns TRUE; otherwise returns FALSE
58Returns the font style of the cell as text
59Returns the number for the underline style
60If the cell is subscript returns TRUE; otherwise returns FALSE
62The book and sheet name in the form "[Book1.xls]Sheet1"
66The name of the workbook containing the cell in the form "Book1.xls"
referenceThe cell whose information is to be returned. If this parameter is omitted it defaults to the active cell.


GET.DOCUMENT(type_num, [name_text])

Returns information about the specified file.

type_numThe type of information to return. The possible values are:
1The book and sheet name in the form "[Book1.xls]Sheet1"
2The path of the directory containing the file, or #N/A is the file has not been saved
3A number indicating the active sheet type: 1=worksheet, 2=chart sheet, 3=macro sheet, 4=info window, 5=reserved, 6=module, 7=dialog
4A logical value indicating whether the file has been changed since last save
7A logical value indicating whether the sheet is protected
9For worksheets, the number of the first used row (0 if empty)
For chart sheets, a number indicating the chart type: 1=area, 2=bar, 3=column, 4=line, 5=pie, 6=scatter, 7=3d area, 8=3d column, 9=3d line, 10=3d pie, 11=radar, 12=3d bar, 13=3d surface, 14=doughnut
10For worksheets, the number of the last used row (0 if empty)
11For worksheets, the number of the first used column (0 if empty)
For chart sheets, the chart series count
12For worksheets, the number of the last used column (0 if empty)
13The number of windows
14A number indicating the calculation mode: 1=automatic, 2=automatic except tables, 3=manual
20A logical value corresponding to the calculation - 1904 date system setting
37A number indicating the file type. For a list of these see the SAVE.AS macro command
42A horizontal array of the objects on a sheet, or #N/A if there are none
48A number indicating the standard column width in characters
68The book name in the form "Book1.xls"
76The book and sheet name in the form "[Book1.xls]Sheet1"
85A logical value indicating whether the Advanced Filter is on
86A logical value indicating whether the AutoFilter is on
87The active sheet number
88The name of the active workbook in the form "Book1.xls"
name_textThe file whose information is to be returned. If this parameter is omitted it defaults to the active file.


GET.FORMULA(reference)

Returns the contents of a cell as a string. Any references in a formula will be returned in R1C1 format.

referenceThe cell whose contents are to be returned.


GET.NAME(name_text, [info_type])

Returns information about a name.

name_textA string containing the name.
info_typeThe type of information to be returned. The possible values are:
1Return the definition.
2Return TRUE if it is a local name, or FALSE if it is a global name.
If this parameter is omitted it defaults to 1.


GET.NOTE([cell_ref], [start_char], [num_chars])

Returns the text of a comment.

cell_refThe reference of the cell containing the comment. If this parameter is omitted it defaults to the active cell.
start_charThe position of the first character to be returned. If this parameter is omitted it defaults to 1.
num_charsThe length of the text to be returned. If this parameter is omitted it defaults to the length of the comment.


GET.OBJECT(type_num, [object_id], [start_num], [count_num], [item_index])

Returns information about the specified object.

type_numThe type of information to return. The possible values are:
1object type as number:
1 line
2 rectangle
3 oval
6 text box
7 command button
11 check box
12 option button
14 label
16 spinner
17 scroll bar
18 list box
19 group box
20 drop down box
2locked
3z-order
4top-left cell as R1C1 reference
5x-offset of top-left corner in points
6y-offset of top-left corner in points
7bottom-right cell as R1C1 reference
8x-offset of bottom-right corner in points
9y-offset of bottom-right corner in points
10reference of the macro as text
11object positioning as number:
1 move and size with cells
2 move but don't size with cells
3 don't move or size with cells
12object text from start_num for count_num characters
13font name
14font size
15bold
16italic
17underline
18strikethrough
19outline
20shadow
21text color
22horizontal alignment as number:
1 left
2 center
3 right
4 justified
23vertical alignment as number:
1 top
2 center
3 bottom
4 justified
26visible
48linked cell reference as text
49object id number
50object class name
51object name (class name and id number)
56enabled
62current value
63minimum value
64maximum value
65increment value
66page value
71list/combo entries
73input range reference as text
74dropdown lines
75draw 3d
object_idThe object identifier. If this parameter is omitted it defaults to the currently selected object on the active worksheet.
start_numThe text substring start position for type_num 12. If this parameter is omitted it defaults to 1.
count_numThe text substring length for type_num 12. If this parameter is omitted it defaults to 255.
item_indexThis parameter is not currently used.


GET.WINDOW(type_num, [window_text])

Returns information about the specified window.

type_numThe type of information to return. The possible values are:
1The book and sheet name in the form "[Book1.xls]Sheet1".
2The number of the window.
3The x-position in points from the left edge of the workspace to the left edge of the window.
4The y-position in points from the bottom of the formula bar to the top edge of the window.
5The window width, measured in points.
6The window height, measured in points.
7If the window is hidden then TRUE, otherwise FALSE.
8If formulas are displayed then TRUE, otherwise FALSE.
9If gridlines are displayed then TRUE, otherwise FALSE.
10If headings are displayed then TRUE, otherwise FALSE.
11If zeros are displayed then TRUE, otherwise FALSE.
12The gridline color (1-56) or 0 if automatic.
13The leftmost column number of each pane, in a horizontal array.
14The top row number of each pane, in a horizontal array.
15The number of columns in each pane, in a horizontal array.
16The number of rows in each pane, in a horizontal array.
17The split type: 1=none, 2=vertical, 3=horizontal, 4=both.
18If the window has a vertical split then TRUE, otherwise FALSE.
19If the window has a horizontal split then TRUE, otherwise FALSE.
20If the window is maximized then TRUE, otherwise FALSE.
21Reserved
22If View Outline Symbols is selected then TRUE, otherwise FALSE.
23The window status: 1=restored, 2=minimized, 3=maximized.
24If window panes are frozen then TRUE, otherwise FALSE.
25The zoom percentage.
26If horizontal scrollbars are displayed then TRUE, otherwise FALSE.
27If vertical scrollbars are displayed then TRUE, otherwise FALSE.
28The ratio of the sheet tabs to the horizontal scrollbar.
29If sheet tabs are displayed then TRUE, otherwise FALSE.
30The book and sheet name in the form "[Book1.xls]Sheet1".
31The book name in the form "Book1.xls".
window_textThe window whose information is to be returned. If this parameter is omitted it defaults to the active window.


GET.WORKBOOK(type_num, [name_text])

Returns information about the specified file.

type_numThe type of information to return. The possible values are:
1A horizontal array of sheet names in the form "[Book1.xls]Sheet1"
4The number of sheets in the workbook
16The workbook name in the form "Book1.xls"
20A number indicating the file type: 1=normal, 3=text, 6=CSV, 17=template
24A logical value indicating whether the file has been changed since last save
38The name of the active sheet in the form "Sheet1"
name_textThe file whose information is to be returned. If this parameter is omitted it defaults to the active file.


GET.WORKSPACE(type_num)

Returns information about the workspace.

type_numThe type of information to return. The possible values are:
6If the status bar is shown then TRUE, otherwise FALSE
7If the formula bar is shown then TRUE, otherwise FALSE
21If the standard toolbar is shown then TRUE, otherwise FALSE
26Name of user
27Name of organisation
32The full path to the application
40If screen updating is on then TRUE, otherwise FALSE
50If full screen mode is on then TRUE, otherwise FALSE


GOAL.SEEK(target_cell, target_value, variable_cell)

Simulates the Format Data Goal seek menu option.

target_cellThe cell that is required to be a certain value. It must be a single cell containing a formula that returns a numeric result.
target_valueThe value that the target cell needs to reach.
variable_cellThe cell whose value can be changed. It must be a single cell that either contains a number or is empty.


GRIDLINES([x_major], [x_minor], [y_major], [y_minor], [z_major], [z_minor], [2D_effect])

Changes the active chart gridlines.

x_majorWhether to draw the X-axis major gridlines. The possible values are:
FALSEDo not draw the X-axis major gridlines.
TRUEDo draw the X-axis major gridlines.
If this parameter is omitted the setting is not changed.
x_minorWhether to draw the X-axis minor gridlines (not currently implemented).
y_majorWhether to draw the Y-axis major gridlines. The possible values are:
FALSEDo not draw the Y-axis major gridlines.
TRUEDo draw the Y-axis major gridlines.
If this parameter is omitted the setting is not changed.
y_minorWhether to draw the Y-axis minor gridlines (not currently implemented).
z_majorWhether to draw the Z-axis minor gridlines (not currently implemented).
z_minorWhether to draw the Z-axis minor gridlines (not currently implemented).
2D_effectWhether to draw 2D gridlines on 3D charts (not currently implemented).


HIDE.OBJECT([object_id], [hide_logical])

Hides or shows an object.

object_idThe object to be hidden or shown. If this parameter is omitted the currently selected object is used.
hide_logicalWhether the object is hidden. The possible values are:
FALSEThe object is shown
TRUEThe object is hidden
If this parameter is omitted it defaults to TRUE.


HLINE([num_columns])

Scrolls the worksheet horizontally by the specified number of columns.

num_columnsThe number of columns to scroll. A negative number will scroll to the left. If this parameter is omitted it defaults to 1.


HPAGE([num_windows])

Scrolls the worksheet horizontally by the specified number of pages.

num_windowsThe number of pages to scroll. A negative number will scroll to the left. If this parameter is omitted it defaults to 1.


HSCROLL(position, [col_logical])

Scrolls the worksheet horizontally to the specified position.

positionThe proportional or absolute position to scroll to.
col_logicalWhether the position parameter represents a proportional or absolute column number. The possible values are:
FALSEThe position parameter is a number between 0 and 1 that represents how far across the worksheet to scroll to.
TRUEThe position parameter is a number between 1 and 256 that represents the column number to scroll to.
If this parameter is omitted it defaults to FALSE.


INPUT(message_text, [type_num], [title_text], [default], [x_pos], [y_pos], [help_ref])

Displays a message in a dialog box with an area for user input and OK and Cancel buttons. If the OK button is pressed then the text entered by the user is returned. If the Cancel button is pressed then this function returns the value FALSE.

message_textThe message that you want displayed in the dialog box.
type_numThe acceptable data type(s) for the data that you want returned. It can be any combination of the following (to specify more than one value, add the numbers together):
0Formula
1Number
2Text
4Logical
8Reference
16Error
64Array
If this parameter is omitted it defaults to 2.
title_textThe title to be used for the dialog box. If this parameter is omitted it defaults to "Input".
defaultThe text that you want to be initially displayed in the input area of the dialog box. If this parameter is omitted the input area will be blank.
x_posThe initial x position on the screen where the dialog box will be displayed. If this parameter is omitted it defaults to the center of the screen. (Not currently implemented)
y_posThe initial y position on the screen where the dialog box will be displayed. If this parameter is omitted it defaults to the center of the screen. (Not currently implemented)
help_refThe name of help topic that is to be displayed if the user presses the Help button in the dialog box. If this parameter is omitted then no Help button is displayed. (Not currently implemented)


INSERT([shift_num])

Simulates the Insert Cells menu option.

shift_numThe way in which the cell insertion is to be processed. The possible values are:
1Shift cells right
2Shift cells down
3Insert entire rows
4Insert entire columns
If this parameter is omitted it defaults to 2.


INSERT.TITLE([chart], [y_primary], [x_primary], [y_secondary], [x_secondary])

Attaches default text to the active chart. The SELECT and FORMULA functions can be used to change the text.

chartWhether to add a default chart title. The possible values are:
FALSEDelete any existing chart title.
TRUEAdd default chart title if one does not already exist.
If this parameter is omitted the chart title is not changed.
y_primaryWhether to add a default primary y-axis title. The possible values are:
FALSEDelete any existing primary y-axis title.
TRUEAdd default primary y-axis title if one does not already exist.
If this parameter is omitted the primary y-axis title is not changed.
x_primaryWhether to add a default primary x-axis title. The possible values are:
FALSEDelete any existing primary x-axis title.
TRUEAdd default primary x-axis title if one does not already exist.
If this parameter is omitted the primary x-axis title is not changed.
y_secondaryWhether to add a default secondary y-axis title (not currently implemented).
x_secondaryWhether to add a default secondary x-axis title (not currently implemented).


LABEL.PROPERTIES([accel_text], [accel_text2], [3d_shading])

Changes the properties of a label or group box control.

accel_textThe accelerator key on a dialog sheet (not currently implemented).
accel_text2The second accelerator key on a dialog sheet (not currently implemented).
3d_shadingWhether to draw the control with 3d shading. The possible values are:
FALSE2d
TRUE3d
If this parameter is omitted the setting is not changed.


LEGEND([logical])

Changes the active chart legend visibility.

logicalWhether to show the legend. The possible values are:
FALSEDo not show the legend
TRUEDo show the legend
If this parameter is omitted it defaults to TRUE.


LIST.NAMES()

Simulates the Insert Name Paste menu option.



LISTBOX.PROPERTIES([range], [link], [drop_size], [multi_select], [3d_shading])

Changes the properties of a list box or combo box control.

rangeThe input range from which to fill the list. If this parameter is omitted the setting is not changed.
linkThe cell that is updated by the control. If this parameter is omitted the link is not changed.
drop_sizeThe number of lines in the drop-down list of a combo box. If this parameter is omitted the setting is not changed.
multi_selectWhether to allow more than one list selection (not currently implemented).
3d_shadingWhether to draw the control with 3d shading. The possible values are:
FALSE2d
TRUE3d
If this parameter is omitted the setting is not changed.


MESSAGE(logical, [text])

Displays a message on the status bar.

logicalWhether to display or hide messages. The possible values are:
TRUEDisplay a message on the status bar.
FALSERemove any message from the status bar.
textThe message to be displayed. If this parameter is omitted it defaults to an empty string.


NAMES([document_text], [type_num], [match_text])

Returns a horizontal array containing defined names.

document_textThe name of the workbook containing the names. If this parameter is omitted it defaults to the active workbook.
type_numThe type of name to retrieve. The possible values are:
1Visible names
2Hidden names
3All names
If this parameter is omitted it defaults to 1.
match_textA template name that may contain wildcard characters. If this parameter is omitted all names will match.


NEW([type_num], [xy_series], [add_logical])

Simulates the File New menu option.

type_numThe type of workbook to create. The possible values are:
omittedNew workbook with 1 worksheet of the same type as the active worksheet.
1New workbook with 1 worksheet.
2New workbook with 1 chart based on the current selection (not currently implemented).
3New workbook with 1 macro sheet.
4New workbook with 1 international macro sheet (not currently implemented).
5New workbook based on Book.xlt or default template.
6New workbook with 1 VB module (not currently implemented).
7New workbook with 1 dialog sheet (not currently implemented).
file nameNew workbook based on template file.
xy_seriesHow to interpret selected data for a new chart (not currently implemented).
add_logicalWhether to add the specified sheet to the current workbook (not currently implemented).


NOTE([add_text], [cell_ref], [start_char], [num_chars])

Simulates the Insert Comment menu option.

add_textThe text that is to be added. If this parameter is omitted it defaults to an empty string.
cell_refThe cell where the comment is to be inserted. If this parameter is omitted it defaults to the active cell.
start_charThe start position in an existing comment where the new text is to be inserted. If this parameter is omitted it defaults to 1.
num_charsThe length of text in an existing comment that is to be replaced. If this parameter is omitted it defaults to the length of the existing text.


OBJECT.PROPERTIES([placement_type], [print_object])

Changes the properties of an object.

placement_typeHow the object is attached to the cells. The possible values are:
1Move and size with cells
2Move but don't size with cells
3Don't move or size with cells
If this parameter is omitted the setting is not changed.
print_objectWhether the object can be printed. The possible values are:
FALSEThe object is not printed
TRUEThe object is printed
If this parameter is omitted the setting is not changed.


ON.DOUBLECLICK([sheet_text], [macro_text])

Specifies a macro to be run when a cell is double-clicked.

sheet_textThe name of the sheet for which the macro is to be run. If this parameter is omitted then the macro will be run for all sheets.
macro_textA string containing a defined name or R1C1 reference indicating the macro that is to be run. If this parameter is omitted then no macro will be run.


ON.ENTRY([sheet_text], [macro_text])

Specifies a macro to be run when data is entered.

sheet_textThe name of the sheet for which the macro is to be run. If this parameter is omitted then the macro will be run for all sheets.
macro_textA string containing a defined name or R1C1 reference indicating the macro that is to be run. If this parameter is omitted then no macro will be run.


ON.SHEET([sheet_text], [macro_text], [activate_logical])

Specifies a macro to be run when a sheet is activated or deactivated.

sheet_textThe name of the sheet for which the macro is to be run. If this parameter is omitted then the macro will be run for all sheets.
macro_textA string containing a defined name or R1C1 reference indicating the macro that is to be run. If this parameter is omitted then no macro will be run.
activate_logicalSpecifies whether the macro is to be run when the sheet is activated or deactivated. The possible values are:
TRUEThe macro will be run when the sheet is activated.
FALSEThe macro will be run when the sheet is deactivated.
If this parameter is omitted it defaults to TRUE.


OPEN(file_text, [update_links], [read_only], [format], [prot_pwd], [write_res_pwd], [ignore_rorec], [file_origin], [custom_delimit], [add_logical], [editable], [file_access], [notify_logical], [converter])

Simulates the File Open menu option.

file_textThe name of the file to be opened.
update_linksWhether to update external references. The possible values are:
0Do not update any references
1Update external references
2Update remote references (not currently implemented)
3Update external and remote references (not currently implemented)
If this parameter is omitted and external references exist then a prompt will be displayed.
read_onlyWhether to open the file as read-only (not currently implemented).
formatThe character used as a delimiter for text files. The possible values are:
1Tab
2Comma
3Space
4Semicolon
5Nothing
6Custom (specified in the custom_delimit parameter)
If this parameter is omitted the text file delimiter will be determined from the file extension.
prot_pwdThe password required to unprotect a file (not currently implemented).
write_res_pwdThe password required to open a read-only file for writing (not currently implemented).
ignore_rorecWhether to suppress the display of the read-only recommended message (not currently implemented).
file_originThe operating system where the file originated (not currently implemented).
custom_delimitThe character used as a custom delimiter for text files.
add_logicalWhether to add the specified file to the current workbook (not currently implemented).
editableWhether a template file should be opened in read-write mode (not currently implemented).
file_accessWhether to change the access of a currently open file (not currently implemented).
notify_logicalWhether to send a message when a shared workbook becomes available (not currently implemented).
converterOverrides the default file type (not currently implemented).


OPEN.DIALOG([file_filter], [button_text], [title], [filter_index])

Returns a file name from the File Open dialog box, or FALSE if Cancel is selected.

file_filterOne or more file filters, separated by commas. If this parameter is omitted it defaults to "All Files (*.*), *.*".
button_textReplacement text for the Open button (not currently implemented).
titleReplacement text for the dialog box title. If this parameter is omitted the title is not changed.
filter_indexThe index of the initial file filter. If this parameter is omitted it defaults to 1.


OPTIONS.VIEW([formula], [status], [notes], [show_info], [object_num], [page_breaks], [formulas], [gridlines], [color_num], [headings], [outline], [zeros], [hor_scroll], [vert_scroll], [sheet_tabs])

Changes the program, file or sheet view options.

formulaSpecifies whether the formula bar is displayed. The possible values are:
FALSEThe formula bar is not displayed
TRUEThe formula bar is displayed
If this parameter is omitted the setting is not changed.
statusSpecifies whether the status bar is displayed. The possible values are:
FALSEThe status bar is not displayed
TRUEThe status bar is displayed
If this parameter is omitted the setting is not changed.
notesSpecifies whether comments and indicators are displayed (not currently implemented).
show_infoSpecifies whether the info window is displayed (not currently implemented).
object_numSpecifies how objects are displayed (not currently implemented).
page_breaksSpecifies whether page breaks are displayed (not currently implemented).
formulasSpecifies whether to display formulas or values in cells. The possible values are:
FALSEValues are displayed
TRUEFormulas are displayed
If this parameter is omitted the setting is not changed.
gridlinesSpecifies whether gridlines are displayed. The possible values are:
FALSEGridlines are not displayed
TRUEGridlines are displayed
If this parameter is omitted the setting is not changed.
color_numSpecifies the color to be used for drawing gridlines (not currently implemented).
headingsSpecifies whether row and column headings are displayed. The possible values are:
FALSERow and column headings are not displayed
TRUERow and column headings are displayed
If this parameter is omitted the setting is not changed.
outlineSpecifies whether outline symbols are displayed (not currently implemented).
zerosSpecifies whether zero values are displayed (not currently implemented).
hor_scrollSpecifies whether the horizontal scroll bar is displayed. The possible values are:
FALSEThe horizontal scroll bar is not displayed
TRUEThe horizontal scroll bar is displayed
If this parameter is omitted the setting is not changed.
vert_scrollSpecifies whether the vertical scroll bar is displayed. The possible values are:
FALSEThe vertical scroll bar is not displayed
TRUEThe vertical scroll bar is displayed
If this parameter is omitted the setting is not changed.
sheet_tabsSpecifies whether the sheet tabs are displayed. The possible values are:
FALSEThe sheet tabs are not displayed
TRUEThe sheet tabs are displayed
If this parameter is omitted the setting is not changed.


PASTE([to_reference])

Simulates the Edit Paste menu option.

to_referenceThe cells where the copied information is to be pasted. If this parameter is omitted it defaults to the current selection on the active worksheet.


PASTE.SPECIAL([paste_num], [operation_num], [skip_blanks], [transpose])

Simulates the Edit Paste Special menu option.

paste_numThe type of information to be pasted. The possible values are:
1All
2Formulas
3Values
4Formats
5Comments
6All except borders
If this parameter is omitted it defaults to 1.
operation_numThe way in which the data being pasted is merged with existing data. The possible values are:
1None
2Add
3Subtract
4Multiply
5Divide
If this parameter is omitted it defaults to 1.
skip_blanksWhether to ignore blank cells in the data being pasted. The possible values are:
FALSEBlank cells are included.
TRUEBlank cells are skipped.
If this parameter is omitted it defaults to FALSE.
transposeWhether to transpose the data being pasted. The possible values are:
FALSEData is not transposed.
TRUEData is transposed.
If this parameter is omitted it defaults to FALSE.


PATTERNS([apattern], [afore], [aback])

Simulates the Format Cells dialog Patterns page.

apatternThe number of the pattern to be applied. The possible values are:
0No pattern
1Solid
250% Gray
375% Gray
425% Gray
5Horizontal Stripe
6Vertical Stripe
7Reverse Diagonal Stripe
8Diagonal Stripe
9Diagonal Crosshatch
10Thick Diagonal Crosshatch
11Thin Horizontal Stripe
12Thin Vertical Stripe
13Thin Reverse Diagonal Stripe
14Thin Diagonal Stripe
15Thin Horizontal Crosshatch
16Thin Diagonal Crosshatch
1712.5% Gray
186.25% Gray
If this parameter is omitted the setting is not changed.
aforeThe number of the foreground color to be applied. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
abackThe number of the background color to be applied. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.


PATTERNS([lauto], [lstyle], [lcolor], [lwt], [hwidth], [hlength], [htype])

Changes the attributes of the selected line.

lautoThe line setting. The possible values are:
0Custom
1Automatic
2None
If this parameter is omitted the setting is not changed.
lstyleThe line style (not currently implemented).
lcolorThe line color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
lwtThe line weight (not currently implemented).
hwidthThe arrowhead width (not currently implemented).
hlengthThe arrowhead length (not currently implemented).
htypeThe arrowhead type. The possible values are:
1None
2Open
3Closed
4Double open
5Double closed
If this parameter is omitted the setting is not changed.


PATTERNS([bauto], [bstyle], [bcolor], [bwt], [shadow], [aauto], [apattern], [afore], [aback], [rounded], [newui])

Changes the attributes of the selected text box, rectangle or oval.

bautoThe border setting. The possible values are:
0Custom
1Automatic
2None
If this parameter is omitted the setting is not changed.
bstyleThe border style (not currently implemented).
bcolorThe border color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
bwtThe border weight (not currently implemented).
shadowWhether to apply a shadow to the object (not currently implemented).
aautoThe area setting. The possible values are:
0Custom
1Automatic
2None
If this parameter is omitted the setting is not changed.
apatternThe area pattern (not currently implemented).
aforeThe area foreground color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
abackThe area background color (not currently implemented).
roundedWhether to draw the object with rounded corners (not currently implemented).
newuiWhether to use the new or old user interface color scheme (not currently implemented).


PATTERNS([bauto], [bstyle], [bcolor], [bwt], [shadow], [aauto], [apattern], [afore], [aback], [invert], [apply], [new_fill])

Changes the attributes of the selected chart plot areas, bars, columns, pie slices or text labels.

bautoThe border setting. The possible values are:
0Custom
1Automatic
2None
If this parameter is omitted the setting is not changed.
bstyleThe border style. The possible values are:
1Solid
2Dash
3Dot
4Dash dot
5Dash dot dot
6Dark
7Medium
8Light
If this parameter is omitted the setting is not changed.
bcolorThe border color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
bwtThe border weight (not currently implemented).
shadowWhether to apply a shadow to the object (not currently implemented).
aautoThe area setting. The possible values are:
0Custom
1Automatic
2None
If this parameter is omitted the setting is not changed.
apatternThe area pattern (not currently implemented).
aforeThe area foreground color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
abackThe area background color (not currently implemented).
invertWhether to invert the pattern for a negative value (not currently implemented).
applyWhether to apply changes to just this item or all similar items (not currently implemented).
new_fillWhether to use the new or old user interface color scheme (not currently implemented).


PATTERNS([lauto], [lstyle], [lcolor], [lwt], [mauto], [mstyle], [mfore], [mback], [apply], [smooth])

Changes the attributes of the selected chart series line.

lautoThe line setting. The possible values are:
0Custom
1Automatic
2None
If this parameter is omitted the setting is not changed.
lstyleThe line style. The possible values are:
1Solid
2Dash
3Dot
4Dash dot
5Dash dot dot
6Dark
7Medium
8Light
If this parameter is omitted the setting is not changed.
lcolorThe line color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
lwtThe line weight (not currently implemented).
mautoThe marker setting. The possible values are:
0Custom
1Automatic
2None
If this parameter is omitted the setting is not changed.
mstyleThe marker style. The possible values are:
1Square
2Diamond
3Triangle
4X
5Star
6Dot
7Dash
8Circle
9Plus
If this parameter is omitted the setting is not changed.
mforeThe marker foreground color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
mbackThe marker background color. The possible values are:
1 to 56One of the colors displayed in the Format Cells dialog Patterns page
If this parameter is omitted the setting is not changed.
applyWhether to apply changes to just this item or all similar items (not currently implemented).
smoothWhether to smooth picture markers (not currently implemented).


PROTECT.DOCUMENT([contents], [windows], [password], [objects], [scenarios])

Simulates the Format Sheet Protect menu option.

contentsWhether to protect the locked cell contents. The possible values are:
TRUEThe locked cell contents are protected
FALSEThe locked cell contents are unprotected
If this parameter is omitted it defaults to TRUE.
windowsWhether to protect windows from being moved or sized (not currently implemented). The possible values are:
TRUEThe windows are protected
FALSEThe windows are unprotected
If this parameter is omitted it defaults to FALSE.
passwordAn optional case-sensitive password to protect or unprotect the document.
objectsWhether to protect the locked objects (not currently implemented). The possible values are:
TRUEThe locked objects are protected
FALSEThe locked objects are unprotected
If this parameter is omitted it defaults to TRUE.
scenariosWhether to protect the scenarios (not currently implemented). The possible values are:
TRUEThe scenarios are protected
FALSEThe scenarios are unprotected
If this parameter is omitted it defaults to TRUE.


QUIT()

Simulates the File Exit menu option.



REFTEXT(reference, [a1])

Returns a string containing the specified reference.

referenceThe reference that is to be converted to text.
a1Specifies the style of the reference. The possible values are:
FALSER1C1 style
TRUEA1 style
If this parameter is omitted it defaults to FALSE.


RELREF(reference, rel_to_ref)

Returns a string containing an R1C1 reference that represents the offset of the first reference from the second.

referenceThe target reference.
rel_to_refThe base reference.


ROW.HEIGHT([height_num], [reference], [standard_height], [type_num])

Simulates the Format Row menu options.

height_numThe new height of the rows. The height is measured in points. This parameter is ignored if standard_height is TRUE or type_num is specified. If this parameter is omitted the height of the specified rows is not changed.
referenceThe rows whose height is to be changed. If this parameter is omitted it defaults to the current selection on the active worksheet.
standard_heightSpecifies whether the rows are to be set to the standard row height. The possible values are:
TRUEThe rows are set to the standard height.
FALSEThe rows are not set to the standard height.
If this parameter is omitted it defaults to FALSE.
type_numSpecifies whether the rows are to be hidden, unhidden or automatically sized. This parameter is ignored if standard_height is TRUE. The possible values are:
1The rows are hidden.
2The rows are unhidden.
3The rows are set to the height of the highest displayed value.
If this parameter is omitted the rows will not be hidden, unhidden or auto-sized.


SAVE()

Simulates the File Save menu option.



SAVE.AS([document_text], [type_num], [prot_pwd], [backup], [write_res_pwd], [read_only_rec])

Simulates the File Save As menu option.

document_textThe name for the saved file. If this parameter is omitted the current name will be used.
type_numThe type of file to save. The possible values are:
1Excel 97-2003 Workbook (xls)
3Text (Tab delimited) (txt)
6CSV (Comma delimited) (csv)
16Excel 2.0 Worksheet (xls)
17Excel 97-2003 Template (xlt)
20Text (Tab delimited) (txt)
23CSV (Comma delimited) (csv)
29Excel 3.0 Worksheet (xls)
33Excel 4.0 Worksheet (xls)
39Excel 95 Workbook (xls)
51Excel Workbook (xlsx)
52Excel Macro-Enabled Workbook (xlsm)
53Excel Macro-Enabled Template (xltm)
54Excel Template (xltx)
56Excel 97-2003 Workbook (xls)
If this parameter is omitted it defaults to the current file type.
prot_pwdThe password required to unprotect a file (not currently implemented).
backupWhether to make a backup of the file (not currently implemented).
write_res_pwdThe password required to open a read-only file for writing (not currently implemented).
read_only_recWhether to save the file as read-only recommended (not currently implemented).


SAVE.AS?([document_text], [type_num], [prot_pwd], [backup], [write_res_pwd], [read_only_rec])

Simulates the File Save As menu option, displaying the dialog box.

document_textThe name for the saved file. If this parameter is omitted the current name will be used.
type_numThe type of file to save. The possible values are:
1Excel 97-2003 Workbook (xls)
3Text (Tab delimited) (txt)
6CSV (Comma delimited) (csv)
16Excel 2.0 Worksheet (xls)
17Excel 97-2003 Template (xlt)
20Text (Tab delimited) (txt)
23CSV (Comma delimited) (csv)
29Excel 3.0 Worksheet (xls)
33Excel 4.0 Worksheet (xls)
39Excel 95 Workbook (xls)
51Excel Workbook (xlsx)
52Excel Macro-Enabled Workbook (xlsm)
53Excel Macro-Enabled Template (xltm)
54Excel Template (xltx)
56Excel 97-2003 Workbook (xls)
If this parameter is omitted it defaults to the current file type.
prot_pwdThe password required to unprotect a file (not currently implemented).
backupWhether to make a backup of the file (not currently implemented).
write_res_pwdThe password required to open a read-only file for writing (not currently implemented).
read_only_recWhether to save the file as read-only recommended (not currently implemented).


SAVE.COPY.AS([document_text])

Saves a copy of the current file with a different name.

document_textThe name for the saved file. If this parameter is omitted then the file is not saved.


SAVE.DIALOG([init_filename], [title], [button_text], [file_filter], [filter_index])

Returns a file name from the File Save As dialog box, or FALSE if Cancel is selected.

init_filenameThe suggested file name. If this parameter is omitted it defaults to the name of the active file.
titleReplacement text for the dialog box title. If this parameter is omitted the title is not changed.
button_textReplacement text for the Save button (not currently implemented).
file_filterOne or more file filters, separated by commas. If this parameter is omitted it defaults to "All Files (*.*), *.*".
filter_indexThe index of the initial file filter. If this parameter is omitted it defaults to 1.


SCALE([min_num], [max_num], [major], [minor], [cross], [logarithmic], [reverse], [max])

Changes the attributes of the selected chart value axis scale.

min_numThe minimum value. If this parameter is omitted the setting is not changed.
max_numThe maximum value. If this parameter is omitted the setting is not changed.
majorThe major step value. If this parameter is omitted the setting is not changed.
minorThe minor step value (not currently implemented).
crossThe value at which the opposite axis crosses this axis (not currently implemented).
logarithmicWhether this scale is linear or logarithmic. The possible values are:
FALSEThe scale is linear.
TRUEThe scale is logarithmic.
If this parameter is omitted the setting is not changed.
reverseWhether the scale values are show in reverse order (not currently implemented).
maxWhether the opposite axis crosses this axis at the maximum value (not currently implemented).


SCROLLBAR.PROPERTIES([value], [min], [max], [inc], [page], [link], [3d_shading])

Changes the properties of a scroll bar or spin control.

valueThe current value of the control. If this parameter is omitted the value is not changed.
minThe minimum value of the control. If this parameter is omitted the value is not changed.
maxThe maximum value of the control. If this parameter is omitted the value is not changed.
incThe change to the current value when the arrow button is clicked. If this parameter is omitted the value is not changed.
pageThe change to the current value when the area either side of the scroll bar thumb is clicked. If this parameter is omitted the value is not changed.
linkThe cell that is updated by the control. If this parameter is omitted the link is not changed.
3d_shadingWhether to draw the control with 3d shading. The possible values are:
FALSE2d
TRUE3d
If this parameter is omitted the setting is not changed.


SELECT([selection], [active_cell])

Sets the selection and cursor cell on the active worksheet.

selectionThe cells that are to be the selection. If this parameter is omitted the current selection is not changed.
active_cellThe cell that is to be the cursor cell. If this parameter is omitted it defaults to the top left cell in the selection.


SELECT([object_id], [replace])

Selects an object on the active worksheet.

object_idThe object to be selected. If this parameter is omitted the current selection is not changed.
replaceWhether to replace or add to the existing object selection (not currently implemented).


SELECT([item_text], [single_point])

Selects an object on the active chart.

item_textThe object to be selected. The possible values are:
"Chart"Entire chart
"Plot"Plot area
"Axis 1"Primary chart value axis
"Axis 2"Primary chart category axis
"Axis 3Secondary chart value axis or 3D chart series axis
"Axis 4"Secondary chart category axis
"Title"Chart title
"Text Axis 1"Primary chart value axis title
"Text Axis 2"Primary chart category axis title
"Text Axis 3"Primary chart series axis title
"Text n"nth floating text item
"Arrow n"nth arrow
"Gridline 1"Value axis major gridlines
"Gridline 2"Value axis minor gridlines
"Gridline 3"Category axis major gridlines
"Gridline 4"Category axis minor gridlines
"Gridline 5"Series axis major gridlines
"Gridline 6"Series axis minor gridlines
"Dropline 1"Primary chart droplines
"Dropline 2"Secondary chart droplines
"Hiloline 1"Primary chart hi-lo lines
"Hiloline 2"Secondary chart hi-lo lines
"UpBar1"Primary chart up bar
"UpBar2"Secondary chart up bar
"DownBar1"Primary chart down bar
"DownBar2"Secondary chart down bar
"Seriesline1"Primary chart series line
"Seriesline2"Secondary chart series line
"Floor"3D chart base
"Walls"3D chart back
"Corners"3D chart corners
"Sn"Series n
"SnPm"Series n point m data
"SnTm"Series n trend line m
"SnEm"Series n error bars m
"Text Sn"Series n text
"Text SnPm"Series n point m text
"Legend"Legend
"Legend Marker n"Legend marker n
"Legend Entry n"Legend entry n
If this parameter is omitted the current selection is not changed.
single_pointWhether to select a single point or the entire series (not currently implemented).


SELECT.END(direction_num)

Simulates the Ctrl+arrow key combination.

direction_numThe direction in which to move the cursor. The possible values are:
1Left
2Right
3Up
4Down


SELECT.LAST.CELL()

Simulates the Ctrl+End key combination.



SELECTION()

Returns a reference which represents the current selection on the active worksheet.



SEND.TO.BACK()

Changes the order of the objects on the active worksheet by moving the currently selected object to the back.



SET.CONTROL.VALUE([value])

Changes the current value of a control.

valueThe new value for the control. The possible values are:
Check box0Unchecked
1Checked
2Mixed
Option button0Unchecked
1Checked
Spin controlThe new value of the spin control
Scroll barThe new scroll position
List boxThe selected item (0 = no selection)
Combo boxThe selected item (0 = no selection)
If this parameter is omitted the setting is not changed.


SHOW.ACTIVE.CELL()

Scrolls the active cell into view.



SORT([orientation], [key1], [order1], [key2], [order2], [key3], [order3], [header], [custom], [case])

Simulates the Format Data Sort menu option.

orientationSpecifies whether to sort the rows or columns. The possible values are:
1Sort rows
2Sort columns
If this parameter is omitted it defaults to 1.
key1Specifies a reference to the first column or row to sort on. If this parameter is omitted it defaults to the first column or row in the selection.
order1Specifies whether to sort the data in ascending or descending order. The possible values are:
1Ascending
2Descending
If this parameter is omitted it defaults to 1.
key2Specifies a reference to the second column or row to sort on. If this parameter is omitted then the second key is not used.
order2Specifies whether to sort the data in ascending or descending order. The possible values are:
1Ascending
2Descending
If this parameter is omitted it defaults to 1.
key3Specifies a reference to the third column or row to sort on. If this parameter is omitted then the third key is not used.
order3Specifies whether to sort the data in ascending or descending order. The possible values are:
1Ascending
2Descending
If this parameter is omitted it defaults to 1.
headerSpecifies whether the selected cells include a header row or column. The possible values are:
0Determine the presence of a header automatically
1There is a header
2There is no header
If this parameter is omitted it defaults to 2.
customSpecifies whether to use a custom list for the first sort key. The possible values are:
1Use normal sort sequence
2Day short names
3Day long names
4Month short names
5Month long names
If this parameter is omitted it defaults to 1.
caseSpecifies whether the sort should be case-sensitive. The possible values are:
TRUEThe sort is case-sensitive
FALSEThe sort is not case-sensitive
If this parameter is omitted it defaults to FALSE.


STANDARD.WIDTH([standard_num])

Simulates the Format Column Standard width menu option.

standard_numThe new standard width. If this parameter is omitted then the standard width is not changed.


TEXT.BOX(add_text, [object_id], [start_num], [num_chars])

Changes the text of an object.

add_textThe text that is to be added.
object_idThe object identifier. If this parameter is omitted it defaults to the currently selected object on the active worksheet.
start_numThe start position in the existing text where the new text is to be inserted. If this parameter is omitted it defaults to 1.
num_charsThe length of existing text that is to be replaced. If this parameter is omitted it defaults to the length of the existing text.


TEXT.TO.COLUMNS([destination_ref], [data_type], [text_delim], [consecutive_delim], [tab], [semicolon], [comma], [space], [other], [other_char], [field_info])

Simulates the Format Data Text to Columns menu option.

destination_refSpecifies an alternative destination for the parsed data (not currently implemented).
data_typeSpecifies how the data is to be split. The possible values are:
1Delimited
2Fixed width
If this parameter is omitted it defaults to 1.
text_delimFor delimited data, specifies the text item delimiter. The possible values are:
1Double quote
2Single quote
3None
If this parameter is omitted it defaults to the value last specified from the menu option.
consecutive_delimFor delimited data, specifies whether to treat consecutive delimiters as one. The possible values are:
FALSETreat consecutive delimiters separately
TRUETreat consecutive delimiters as one
If this parameter is omitted it defaults to the value last specified from the menu option.
tabFor delimited data, specifies whether to treat the tab character as a delimiter. The possible values are:
FALSEDo not treat tab characters as delimiters
TRUETreat tab characters as delimiters
If this parameter is omitted it defaults to the value last specified from the menu option.
semicolonFor delimited data, specifies whether to treat the semicolon character as a delimiter. The possible values are:
FALSEDo not treat semicolon characters as delimiters
TRUETreat semicolon characters as delimiters
If this parameter is omitted it defaults to the value last specified from the menu option.
commaFor delimited data, specifies whether to treat the comma character as a delimiter. The possible values are:
FALSEDo not treat comma characters as delimiters
TRUETreat comma characters as delimiters
If this parameter is omitted it defaults to the value last specified from the menu option.
spaceFor delimited data, specifies whether to treat the space character as a delimiter. The possible values are:
FALSEDo not treat space characters as delimiters
TRUETreat space characters as delimiters
If this parameter is omitted it defaults to the value last specified from the menu option.
otherFor delimited data, specifies whether to treat the user-specified character as a delimiter. The possible values are:
FALSEDo not treat user-specified characters as delimiters
TRUETreat user-specified characters as delimiters
If this parameter is omitted it defaults to the value last specified from the menu option.
other_charFor delimited data, specifies the user-specified character to be treated as a delimiter. If this parameter is omitted it defaults to the value last specified from the menu option.
field_infoFor delimited data, an array specifying {column number, data format}, repeated as necessary. For fixed width data, an array specifying {start position, data format}, repeated as necessary. For the data format, the possible values are:
1General
2Text
3Date
9Skip
If this parameter is omitted the data format defaults to General.


TEXTREF(text, [a1])

Returns a reference corresponding to the specified text.

textThe string containing the reference.
a1Specifies the style of the reference. The possible values are:
FALSER1C1 style
TRUEA1 style
If this parameter is omitted it defaults to FALSE.


UNDO()

Simulates the Edit Undo menu option.



VLINE([num_rows])

Scrolls the worksheet vertically by the specified number of rows.

num_rowsThe number of rows to scroll. A negative number will scroll towards the top. If this parameter is omitted it defaults to 1.


VPAGE([num_windows])

Scrolls the worksheet vertically by the specified number of pages.

num_windowsThe number of pages to scroll. A negative number will scroll towards the top. If this parameter is omitted it defaults to 1.


VSCROLL(position, [row_logical])

Scrolls the worksheet vertically to the specified position.

positionThe proportional or absolute position to scroll to.
row_logicalWhether the position parameter represents a proportional or absolute row number. The possible values are:
FALSEThe position parameter is a number between 0 and 1 that represents how far down the worksheet to scroll to.
TRUEThe position parameter is a number between 1 and 65536 that represents the row number to scroll to.
If this parameter is omitted it defaults to FALSE.


WORKBOOK.ACTIVATE(sheet_name)

Specifies the sheet that is to be the active worksheet.

sheet_nameThe sheet that is to be the active worksheet.


WORKBOOK.DELETE([sheet_name])

Simulates the Edit Delete Sheet menu option.

sheet_nameThe sheet that is to be deleted. If this parameter is omitted it defaults to the currently active sheet.


WORKBOOK.HIDE([sheet_text], [very_hidden])

Simulates the Format Sheet Hide menu option.

sheet_textThe name of the sheet to hide. If this parameter is omitted it defaults to the currently active sheet.
very_hiddenWhether to show the name of the hidden sheet in the Unhide dialog box. The possible values are:
TRUEDo not show the sheet name
FALSEShow the sheet name
If this parameter is omitted it defaults to FALSE.


WORKBOOK.INSERT([type_num])

Simulates the Insert Worksheet, Chart and Macro sheet menu options.

type_numThe type of sheet that is to be added. The possible values are:
1A worksheet
2A chart
3A macro sheet
If this parameter is omitted it defaults to the type of the currently active sheet.


WORKBOOK.NAME(old_sheet_name, new_sheet_name)

Simulates the Format Sheet Rename menu option.

old_sheet_nameThe sheet that is to be renamed.
new_sheet_nameThe new name for the sheet.


WORKBOOK.NEXT()

Simulates the Ctrl+Page Down key combination.



WORKBOOK.PREV()

Simulates the Ctrl+Page Up key combination.



WORKBOOK.UNHIDE([sheet_text])

Simulates the Format Sheet Unhide menu option.

sheet_textThe name of the sheet to unhide. If this parameter is omitted it defaults to the first hidden sheet.


ZOOM([magnification])

Simulates the Format Sheet Zoom menu option.

magnificationThe magnification option. The possible values are:
numberSet the zoom level to the specified number. The number must be between 10 and 400.
TRUESet the zoom level so that the current selection fills the window.
FALSEReset the zoom level to 100%
If this parameter is omitted it defaults to TRUE.


DDE/external functions

CALL(module_text, procedure, type_text, [argument1, ...])

Returns a value from an external function.

module_textThe name of the DLL that contains the function.
procedureThe name of the function.
type_textA string specifying the result type and the argument types. Each type is represented by a single character. The possible character values are:
ALogical, passed by value
BDouble, passed by value
CNull-terminated string, passed by reference
DByte-counted string, passed by reference
EDouble, passed by reference
FNull-terminated string, passed by reference
GByte-counted string, passed by reference
HUnsigned short integer, passed by value
ISigned short integer, passed by value
JSigned long integer, passed by value
KArray, passed by reference
LLogical, passed by reference
MSigned short integer, passed by reference
NSigned long integer, passed by reference
OArray, passed by reference
POPER structure, passed by reference
RXLOPER structure, passed by reference
argument1, ...The arguments to pass to the function.


Web functions

ENCODEURL(text)

Returns text URL-encoded.

textThe text to be URL-encoded.


FILTERXML(xml,xpath)

Returns data extracted from xml using the specified xpath.

xmlThe xml string containing the data.
xpathThe xpath expression defining the data required.


WEBSERVICE(url)

Returns data from a web service.

urlThe URL of the web service.


Charts

To add a chart, use the Insert Chart menu option to add a new chart sheet. Then use the Format Chart menu option to display the dialog box that contains the following formatting options:

Type
Series
Titles
Axes
Legend


Chart type

The program currently supports the following chart types:

ColumnDisplays vertical columns whose heights correspond to the data values.
BarDisplays horizontal bars whose lengths correspond to the data values.
LineDisplays a line through points whose vertical positions correspond to the data values, the horizontal positions are evenly spaced.
PieDisplays a circle divided up into regions whose relative sizes correspond to the data values.
DoughnutLike the Pie chart but displays one or more series.
ScatterDisplays a line through points whose x and y coordinates are given by the data values.
AreaLike the line chart, but the area below the line is filled in, and the series values are stacked.
RadarDisplays a line through points whose distances from a central point correspond to the data values.


Chart series

This allows you to specify the data values to be used for each series. To add a new series, press the 'New...' button. To change an existing series, select it in the list box and then press the 'Edit...' button. To remove an existing series, select it in the list box and press the 'Delete' button.

Note that for Pie charts, only the first series will be displayed.

When entering or changing a series, you are prompted for the following information:

DataNameAn optional name that is used to identify the series in the list box. It defaults to 'Series1', etc.
Y valuesA reference to the cells that contain the data to be charted. You can specify more than one reference, separating them with commas, but they must all be from the same sheet. Alternatively you can specify this as an array of values.
X labelsFor Column, Bar, Pie, Line and Area charts, you can specify a reference or array of values that will be used to label the categories. As there is only one set of labels, this must be the same for all the series on one chart. For Scatter charts, this item is labelled 'X values' and you must enter a reference or array of values to be used as the x co-ordinates of the line.
PatternsLine typeAllows you to choose a solid, dashed or dotted line.
Line colorAllows you to specify the color of the line.
Marker typeFor Line, Scatter and Radar charts, allows you to choose a marker for the data points.
Marker colorsFor Line, Scatter and Radar charts, allows you to choose the foreground and background colors for the marker.
Area colorFor Column, Bar, Pie, Doughnut and Area charts, allows you to specify the colour that will be used to fill the interior.
LabelsLabel typeAllows you to display the x or y values next to the data points, and for Pie and Doughnut charts the percentages.


Chart titles

This allows you to specify the titles that will be used to label the chart itself and the x and y axes. Note that axis titles do not apply to Pie charts.


Chart axes

This allows you to specify the formatting of the x and y axes. The actual options that are available will depend on the currently selected chart type.

AxisSpecifies whether the axis line will be drawn.
GridlinesSpecifies whether the guidelines will be drawn.
TicksSpecifies whether the axis tick marks will be drawn.
ValuesSpecifies whether the axis values will be displayed.
LogarithmicSpecifies whether a linear or logarithmic scale is required.
MinimumSpecifies the minimum value to be used on the scale.
MaximumSpecifies the maximum value to be used on the scale.
StepSpecifies the interval between the values on the scale.


Chart legend

This allows you to specify whether and where to display the names that identify each series with its line and/or area color. For Pie charts, the information displayed will be the category names (from the 'X labels' entry in the series formatting dialog box), for other chart types it will be the Series names from the chart series list box.


Macros

Macros can be used to create user-defined functions to use in your spreadsheets, and user-defined commands to automate tasks.


User-defined functions

Macros are useful for user-defined functions in 2 situations:
1. You may have a complex function, which is composed of many nested functions, which is used in many places on your worksheet. A user-defined function would save you a lot of typing by specifying the actual calculation in only one place.
2. You may need to use an iterative or recursive calculation that has to be performed a certain number of times before a result is found.

These are the steps for entering a user-defined function:

1. Use the Insert Macro sheet menu option to insert a macro sheet in the workbook. Macros must be stored on macro sheets. You can have many macros on a single macro sheet.

2. Enter the macro function statements. A user-defined function macro will usually begin with one or more ARGUMENT functions to receive the parameters, and it must end with a RETURN function.

This example calculates the approximate integral of the function sin(x) between a lower and upper limit using the trapezium rule and dividing the area into 100 slices:

 

A

B

C

D

1 Integral: =ARGUMENT(,1,D1) lower:  
2   =ARGUMENT(,1,D2) upper:  
3   =SET.VALUE(D3,0) sum:  
4   =SET.VALUE(D4,SIN(D1)) y0:  
5   =SET.VALUE(D5,SIN(D2)) yn:  
6   =SET.VALUE(D6,1) i:  
7   =WHILE(D6<D7) steps: 100
8   =SET.VALUE(D8,D1+(D2-D1)*D6/D7) x:  
9   =SET.VALUE(D3,D3+SIN(D8))    
10   =SET.VALUE(D6,D6+1)    
11   =NEXT()    
12   =RETURN((D2-D1)/D7/2*(D4+D5+2*D3))    

3. Use the Insert Name Define menu option to add a name for the macro. For the example above, you could define a name called 'integral', which refers to cell: Macro1!$B$1. Note that the macro name in cell A1 is for our reference only, it is not used by the program.

4. Then on your worksheet (or even in another macro), use the defined name like an ordinary worksheet function name.
For the example above, if you enter 0 in cell A1 on your worksheet, and =PI() in cell A2 on your worksheet, and =integral(A1,A2) in cell A3, then cell A3 will display 1.9998355 (higher accuracy could be obtained with a larger number of steps but the calculation would take correspondingly longer).

Here is an example that uses recursion. Because macro sheets do not directly support recursion, I have implemented a simple stack to store the values at each level by concatenating them together in a string.
This function displays a number as text:

 

A

B

C

D

E

1 Say number: =ARGUMENT(,1,D1) input:    
2   =SET.VALUE(D2,"") output:    
3   =SET.VALUE(D1,INT(D1)) temp:    
4   =IF(D1<=0,RETURN(D2))      
5   =IF(D1>=1000000)      
6 stack input =SET.VALUE(E1,D1&"|"&E1)      
7 stack output =SET.VALUE(E2,D2&"|"&E2)      
8 get millions =SET.VALUE(D3,saynumber(D1/1000000))      
9 unstack output =SET.VALUE(D2,LEFT(E2,FIND("|",E2)-1))      
10   =SET.VALUE(E2,RIGHT(E2,LEN(E2)-FIND("|",E2)))      
11 unstack input =SET.VALUE(D1,LEFT(E1,FIND("|",E1)-1))      
12   =SET.VALUE(E1,RIGHT(E1,LEN(E1)-FIND("|",E1)))      
13 update output =SET.VALUE(D2,D2&D3&" million")      
14 update input =SET.VALUE(D1,MOD(D1,1000000))      
15 check if finished =IF(D1=0,RETURN(D2))      
16   =SET.VALUE(D2,D2&IF(D1<100," and "," "))      
17   =END.IF()      
18   =IF(D1>=1000)      
19 stack input =SET.VALUE(E1,D1&"|"&E1)      
20 stack output =SET.VALUE(E2,D2&"|"&E2)      
21 get thousands =SET.VALUE(D3,saynumber(D1/1000))      
22 unstack output =SET.VALUE(D2,LEFT(E2,FIND("|",E2)-1))      
23   =SET.VALUE(E2,RIGHT(E2,LEN(E2)-FIND("|",E2)))      
24 unstack input =SET.VALUE(D1,LEFT(E1,FIND("|",E1)-1))      
25   =SET.VALUE(E1,RIGHT(E1,LEN(E1)-FIND("|",E1)))      
26 update output =SET.VALUE(D2,D2&D3&" thousand")      
27 update input =SET.VALUE(D1,MOD(D1,1000))      
28 check if finished =IF(D1=0,RETURN(D2))      
29   =SET.VALUE(D2,D2&IF(D1<100," and "," "))      
30   =END.IF()      
31   =IF(D1>=100)      
32 stack input =SET.VALUE(E1,D1&"|"&E1)      
33 stack output =SET.VALUE(E2,D2&"|"&E2)      
34 get hundreds =SET.VALUE(D3,saynumber(D1/100))      
35 unstack output =SET.VALUE(D2,LEFT(E2,FIND("|",E2)-1))      
36   =SET.VALUE(E2,RIGHT(E2,LEN(E2)-FIND("|",E2)))      
37 unstack input =SET.VALUE(D1,LEFT(E1,FIND("|",E1)-1))      
38   =SET.VALUE(E1,RIGHT(E1,LEN(E1)-FIND("|",E1)))      
39 update output =SET.VALUE(D2,D2&D3&" hundred")      
40 update input =SET.VALUE(D1,MOD(D1,100))      
41 check if finished =IF(D1=0,RETURN(D2))      
42   =SET.VALUE(D2,D2&" and ")      
43   =END.IF()      
44   =IF(D1>=20)      
45   =SET.VALUE(D2,D2&CHOOSE(INT(D1/10)-1, "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"))      
46   =SET.VALUE(D1,MOD(D1,10))      
47   =IF(D1=0,RETURN(D2))      
48   =SET.VALUE(D2,D2&" ")      
49   =END.IF()      
50   =SET.VALUE(D2,D2&CHOOSE(D1, "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen"))      
51   =RETURN(D2)      

If we create a name ('SayNumber') for this function (Macro1!$B$1), we can enter 1234567890 in cell A1 on our worksheet, and =saynumber(A1) in cell A2 on our worksheet, and cell A2 will display:
one thousand two hundred and thirty four million five hundred and sixty seven thousand eight hundred and ninety


User-defined commands

Here is a command macro example.
This macro moves to the beginning of row 5 on the current sheet, inserts a new row, puts the current date into column A, formats it, and moves to column B ready for entry.

 

A

B

1 NewEntry: =SELECT(!$A$5)
2   =INSERT(3)
3   =FORMULA(TODAY())
4   =FORMAT.NUMBER("dd/mm/yy")
5   =SELECT(!$B$5)
6   =RETURN()

We create a name for this macro, e.g. "NewEntry", pointing to the reference "Macro1!$B$1", and assign control key "t" to it.
Then on our main sheet, we can press Ctrl+t and the macro will be run. Note that the macro name in cell A1 is for our reference only, it is not used by the program.

Note that the following control keys are reserved for program operation, and should not be assigned to your command macros:

1 Format Cells
c Edit Copy
d Edit Fill Down
e enter edit mode
f Edit Find
g Edit Goto
h Edit Replace
k Insert Hyperlink
n File New
o File Open
r Edit Fill Right
s File Save
v Edit Paste
x Edit Cut
z Edit Undo
; insert date
: insert time

Objects

You can add controls and drawing objects to a worksheet by using the Insert Control and Insert Drawing menu options. After selecting the menu option, drag the pointer across the worksheet to define the area that the control will occupy. A dialog box will then be displayed that allows you to specify the attributes of the object.

To edit a control or drawing object, press the Ctrl key (on the keyboard) and then click on the object. This will select the object. Then you can use the Edit Delete menu option or the Format Control or Format Drawing menu option, or you can drag the control to a different position or you can change the shape or size by dragging the border of the object. To deselect a selected object, press the Ctrl key and click on the object again, or select a different object, or click elsewhere on the worksheet. You can also use Tab or Shift+Tab to select the next or previous object.

The following control object types are supported:

LabelA text label.
Group boxA rectangle with an optional heading. Useful for grouping separate sets of option buttons together.
Command buttonA button that can be pressed. Useful for activating a macro.
Check boxA box that can be ticked or unticked. It can return a value of TRUE or FALSE to a linked cell to indicate its state.
Option buttonUsually one of a group of buttons that selects one of several items. Only one of the buttons in a group can be pressed at any one time. Use a group box object to define a button group. The group of buttons can return a value to a linked cell indicating the number of the button in the group that is currently pressed.
List boxDisplays a list of items from a range in the spreadsheet. It can return the number of the selected item to a linked cell.
Combo boxDisplays one of a list of items from a range in the spreadsheet. It can return the number of the selected item to a linked cell.
Scroll barA horizontal or vertical scroll bar. The orientation is determined by the shape of the rectangle when the control is first inserted. It can return the value of the current position to a linked cell.
Spin controlA vertical spin control. It can return the current value to a linked cell.

The following drawing object types are supported:

LineA line or arrow.
RectangleA rectangle that can be filled.
OvalAn oval that can be filled.
Text boxA rectangle that can contain text.

All of the objects can have a macro attached that will be run when the object is clicked on.