Spread32 Help
(C) 2001 Bye Design Ltd
BasicsA 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 65536 rows and 256 columns of these. The rows are number from 1 to 65536, 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 IV, which is the 256th 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.
| New | Opens a new blank workbook. |
| Open | Displays the File Open dialog box for you to choose a spreadsheet file to open. |
| Recent | Displays a list of the 9 most recently used (i.e. opened or saved) files as a shortcut for opening them. |
| Close | Closes the current workbook, after prompting you to save any changes. |
| Save | Saves 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 as | Displays the File Save dialog box for you to choose a name and location for saving the current workbook. |
| Window | Displays a list of the currently open workbooks and allows you to select a different one as the active workbook. |
| Prints the currently displayed sheet. | |
| Exit | This shuts down the program, after prompting you to save any changed workbooks. |
| Undo | This 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. | ||||||||||
| Cut | This 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. | ||||||||||
| Copy | This 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. | ||||||||||
| Paste | This option takes the contents of the clipboard and puts it into the currently selected cells. | ||||||||||
| Paste Special | When 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 |
| ||||||||||
| Clear |
| ||||||||||
| Delete | If 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 Sheet | Delete 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 Sheet | Allows you to move or copy the current sheet to a different position in the file. | ||||||||||
| Find | Allows 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. | ||||||||||
| Replace | Allows you to search for text in cell formulas and optionally replace one or all occurrences. | ||||||||||
| Go To | Allows 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. |
| Cells | If 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. | ||||||||||||||||||
| Rows | Inserts one or more new rows where the selection is. | ||||||||||||||||||
| Columns | Inserts one or more new columns where the selection is. | ||||||||||||||||||
| Worksheet | Inserts a blank worksheet at the end of the spreadsheet file. | ||||||||||||||||||
| Chart |
| ||||||||||||||||||
| Macro | Inserts a blank macro sheet at the end of the spreadsheet file. Macro sheets are used for storing custom (user-defined) functions and commands. | ||||||||||||||||||
| Function | Displays a dialog box from which you can select a function to be inserted into the current cell formula. | ||||||||||||||||||
| Name |
| ||||||||||||||||||
| Comment | This 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 |
| ||||||||||||||||||
| Drawing |
|
| Cells | Displays 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 |
| ||||||||||||||||||
| Column |
| ||||||||||||||||||
| Sheet |
| ||||||||||||||||||
| File | Allows you to select whether the Vertical scrollbar, Horizontal scrollbar and Sheet tabs are to be displayed for sheets in the current file. | ||||||||||||||||||
| Program | Allows you to select whether the Toolbar, Formula bar and Status bar are to be displayed by the program. | ||||||||||||||||||
| Conditional | Allows you to select alternative formatting for cells depending on conditions. | ||||||||||||||||||
| Freeze | On 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' which allows you to free the rows and columns again. | ||||||||||||||||||
| Data |
| ||||||||||||||||||
| Chart | On 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. | ||||||||||||||||||
| Control | Displays a dialog box that allows you to set the attributes of the currently selected Control object. | ||||||||||||||||||
| Drawing | Displays a dialog box that allows you to set the attributes of the currently selected Drawing object. |
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+2 | Will 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.
[ ] indicates optional parameters
DAVERAGE(database_range, field, criteria_range)[ ] indicates optional parameters
DATE(year, month, day)[ ] indicates optional parameters
BESSELI(x, n)[ ] indicates optional parameters
ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])[ ] indicates optional parameters
CELL(info_type, [reference])[ ] indicates optional parameters
AND(logical1, [logical2, ...])[ ] indicates optional parameters
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])[ ] indicates optional parameters
ABS(number)[ ] indicates optional parameters
AVEDEV(number1, [number2, ...])[ ] indicates optional parameters
CHAR(number)[ ] indicates optional parameters
ARGUMENT([name_text], [type], [reference])[ ] indicates optional parameters
ACTIVATE([window_text], [pane_num])[ ] indicates optional parameters
CALL(module_text, procedure, type_text, [argument1, ...])DAVERAGE(database_range, field, criteria_range)
Returns the average of the values of a specified field in a database range.
| database_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the average. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The 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_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The 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_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the value that you want to retrieve. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the maximum. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the minimum. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values that you want to multiply together. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the standard deviation. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the standard deviation. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the sum. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the variance. |
| criteria_range | A 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_range | A reference to the cells that contain the database (including the column headings). It is usually easiest to use a defined name for this. |
| field | The column number or column heading name of the values of which you want to find the variance. |
| criteria_range | A 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. |
Returns the date value corresponding to a specified year, month and day. Note that only dates from 1900 onwards can be used.
| year | The year number. If you enter a number less than 1900 then 1900 will be added to the value. |
| month | The month number. Preferably between 1 and 12. |
| day | The day number. Preferably between 1 and 31. |
Returns the difference between two dates in the units that you specify.
| date1 | The 'from' date. | ||||||||||||
| date2 | The 'to' date. This should be greater than or equal to the 'from' date. | ||||||||||||
| units | The units in which you want the difference expressed. The possible values are: | ||||||||||||
|
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_text | The 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. |
Returns the day number (between 1 and 31) from the specified date value.
| serial_number | The date value from which you want to extract the day number. |
DAYS360(start_date, end_date, [method])
Returns the number of days between two dates using a 360-day year (30 day months).
| start_date | The 'from' date. | ||||
| end_date | The 'to' date. | ||||
| method | Indicates 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: | ||||
| |||||
| If this parameter is omitted it defaults to FALSE. |
Returns the date that is the start date plus or minus a number of months.
| start_date | The 'from' date. |
| months | The number of months to be added or subtracted. |
Returns the date that is the last day of the month calculated from the start date plus or minus a number of months.
| start_date | The 'from' date. |
| months | The number of months to be added or subtracted. |
Returns the hour number (between 0 and 23) from the specified date/time value.
| serial_number | The date/time value from which you want to extract the hour number. |
Returns the minute number (between 0 and 59) from the specified date/time value.
| serial_number | The date/time value from which you want to extract the minute number. |
Returns the month number (between 1 and 12) from the specified date/time value.
| serial_number | The 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_date | The 'from' date. |
| end_date | The 'to' date. |
| holidays | A date or array of dates or a reference to a list of dates that are to be treated as non-working days. |
Returns the serial number corresponding to the current date and time.
Returns the second number (between 0 and 59) from the specified date/time value.
| serial_number | The date/time value from which you want to extract the second number. |
Returns the serial number corresponding to a specified hour, minute and second.
| hour | The hour number. Preferably between 0 and 23. |
| minute | The minute number. Preferably between 0 and 59. |
| second | The second number. Preferably between 0 and 59. |
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_text | The 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. |
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_number | The date/time value from which you want the day of the week. | ||||||
| return_type | The range of values returned and what they represent. The possible values are: | ||||||
| |||||||
| 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_number | The date/time value from which you want the week number. | ||||||
| return_type | The method used to calculate the week number. The possible values are: | ||||||
| |||||||
| 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_date | The 'from' date. |
| number_days | The number of working days to add or subtract. |
| holidays | A date or array of dates or a reference to a list of dates that are to be treated as non-working days. |
Returns the year number (greater than or equal to 1900) from the specified date/time value.
| serial_number | The 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_date | The 'from' date. | ||||||||||
| end_date | The 'to' date. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 0. |
Returns the value of the modified Bessel function.
| x | The value at which you want to evaluate the function. |
| n | The order of the Bessel function. |
Returns the value of the Bessel function.
| x | The value at which you want to evaluate the function. |
| n | The order of the Bessel function. |
Returns the value of the modified Bessel function.
| x | The value at which you want to evaluate the function. |
| n | The order of the Bessel function. |
Returns the value of the Bessel function.
| x | The value at which you want to evaluate the function. |
| n | The order of the Bessel function. |
Returns the decimal equivalent of a binary number.
| number | The binary number to be converted. It can be a number or a string up to 10 (binary) digits long. |
Returns a string that is the hexadecimal equivalent of a binary number.
| number | The binary number to be converted. It can be a number or a string up to 10 (binary) digits long. |
| places | The 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. |
Returns a string that is the octal equivalent of a binary number.
| number | The binary number to be converted. It can be a number or a string up to 10 (binary) digits long. |
| places | The 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. |
COMPLEX(real_num, imag_num, [suffix])
Returns a string representing an complex number made up of the specified real and imaginary parts.
| real_num | The real part of the complex number. |
| imag_num | The imaginary part of the complex number. |
| suffix | The 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.
| number | The number to be converted. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| from_unit | The units to convert from. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| to_unit | The 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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Distance | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Time | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Pressure | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Force | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Energy | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Power | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Magnetism | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Temperature | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Liquid measure | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Returns a string that is the binary equivalent of a decimal number.
| number | The decimal number to be converted. |
| places | The 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. |
Returns a string that is the hexadecimal equivalent of a decimal number.
| number | The decimal number to be converted. |
| places | The 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. |
Returns a string that is the octal equivalent of a decimal number.
| number | The decimal number to be converted. |
| places | The 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. |
Returns 1 if the numbers are equal, otherwise 0.
| number1 | The first of the numbers to be compared. |
| number2 | The 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_limit | The lower limit of the integral. |
| upper_limit | The upper limit of the integral. If this parameter is omitted integration is done between 0 and lower_limit. |
Returns the integral of the error function between a specified limit and infinity.
| x | The lower limit of the integral. |
Returns the double factorial of a number.
| number | The number of which you want the double factorial. |
Returns 1 if number is greater than or equal to step, otherwise 0.
| number | The first of the numbers to be compared. |
| step | The second of the numbers to be compared. If this parameter is omitted it defaults to zero. |
Returns a string that is the binary equivalent of a hexadecimal number.
| number | The hexadecimal number to be converted. |
| places | The 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. |
Returns a number that is the decimal equivalent of a hexadecimal number.
| number | The hexadecimal number to be converted. |
Returns a string that is the octal equivalent of a hexadecimal number.
| number | The hexadecimal number to be converted. |
| places | The 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. |
Returns a number that is the modulus of a complex number.
| inumber | The complex number of which you want the modulus. |
Returns a number that is the imaginary part of a complex number.
| inumber | The complex number of which you want the imaginary part. |
Returns a number that is the angle (in radians) represented by a complex number.
| inumber | The complex number of which you want the argument. |
Returns a complex number that is the conjugate of the specified complex number.
| inumber | The complex number of which you want the conjugate. |
Returns a complex number that is the cosine of the specified complex number.
| inumber | The complex number of which you want the cosine. |
Returns a complex number that is the quotient of the specified complex numbers.
| inumber1 | The dividend complex number. |
| inumber2 | The divisor complex number. |
Returns a complex number that is the exponential of the specified complex number.
| inumber | The complex number of which you want the exponential. |
Returns a complex number that is the natural logarithm of the specified complex number.
| inumber | The complex number of which you want the natural logarithm. |
Returns a complex number that is the base 10 logarithm of the specified complex number.
| inumber | The complex number of which you want the base 10 logarithm. |
Returns a complex number that is the base 2 logarithm of the specified complex number.
| inumber | The complex number of which you want the base 2 logarithm. |
Returns a complex number that is the specified complex number raised to a power.
| inumber | The complex number that you want to raise to a power. |
| power | The 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. |
Returns a number that is the real part of a complex number.
| inumber | The complex number of which you want the real part. |
Returns a complex number that is the sine of the specified complex number.
| inumber | The complex number of which you want the sine. |
Returns a complex number that is the square root of the specified complex number.
| inumber | The complex number of which you want the square root. |
Returns a complex number that is the difference of the specified complex numbers.
| inumber1 | The minuend complex number. |
| inumber2 | The 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. |
Returns a string that is the binary equivalent of an octal number.
| number | The octal number to be converted. |
| places | The 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. |
Returns a number that is the decimal equivalent of an octal number.
| number | The octal number to be converted. |
Returns a string that is the hexadecimal equivalent of an octal number.
| number | The octal number to be converted. |
| places | The 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. |
ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])
Returns accrued interest for a security that pays periodic interest.
| issue_date | The issue date. | ||||||||||
| first_interest_date | The first interest date. | ||||||||||
| settlement_date | The settlement date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| par | The par value. | ||||||||||
| frequency | The number of interest payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The issue date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| par | The par value. If this parameter is omitted it defaults to 1000. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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.
| cost | The cost. | ||||||||||
| purchase_date | The purchase date. | ||||||||||
| first_period_date | The end date of the first period. | ||||||||||
| salvage | The salvage value. | ||||||||||
| period | The period for which you want to calculate the depreciation. | ||||||||||
| rate | The depreciation rate. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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.
| cost | The cost. | ||||||||||
| purchase_date | The purchase date. | ||||||||||
| first_period_date | The end date of the first period. | ||||||||||
| salvage | The salvage value. | ||||||||||
| period | The period for which you want to calculate the depreciation. | ||||||||||
| rate | The depreciation rate. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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.
| rate | The interest rate. | ||||
| nper | The total number of periods. | ||||
| pv | The present value. | ||||
| start_period | The first period number for which to calculate interest. | ||||
| end_period | The last period number for which to calculate interest. | ||||
| type | The timing of the payment. The possible values are: | ||||
|
CUMPRINC(rate, nper, pv, start_period, end_period, type)
Returns the cumulative principal paid on a loan in the specified periods.
| rate | The interest rate. | ||||
| nper | The total number of periods. | ||||
| pv | The present value. | ||||
| start_period | The first period number for which to calculate interest. | ||||
| end_period | The last period number for which to calculate interest. | ||||
| type | The timing of the payment. The possible values are: | ||||
|
DB(cost, salvage, life, period, [month])
Returns the depreciation in a specified period using the fixed declining balance method.
| cost | The cost. |
| salvage | The salvage value. |
| life | The total number of periods. |
| period | The period number for which to calculate depreciation. |
| month | The 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.
| cost | The cost. |
| salvage | The salvage value. |
| life | The total number of periods. |
| period | The period number for which to calculate depreciation. |
| factor | The 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| pr | The price per $100 value. | ||||||||||
| redemption | The redemption per $100 value. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_dollar | The value expressed as a fraction. |
| fraction | The fraction denominator. |
DOLLARFR(decimal_dollar, fraction)
Returns the fraction equivalent of a dollar price expressed as a decimal.
| decimal_dollar | The value expressed as a decimal. |
| fraction | The fraction denominator. |
DURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
Returns the Macauley duration for a value of $100.
| settlement_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| coupon | The interest rate. | ||||||||||
| yield | The annual yield rate. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 0. |
Returns the effective annual interest rate.
| nominal_rate | The nominal annual interest rate. |
| npery | The number of compound interest payments per year. |
FV(rate, term, [payment], [pv], [type])
Returns the future value of an investment at a fixed rate.
| rate | The interest rate per period. | ||||
| term | The total number of periods. | ||||
| payment | The payment amount each period. If this parameter is omitted it is assumed to be zero. | ||||
| pv | The present value. If this parameter is omitted it is assumed to be zero. | ||||
| type | The timing of the payment. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to 0. |
FVSCHEDULE(principal, schedule_range)
Returns the future value of an investment at a variable rate.
| principal | The initial value of the investment. |
| schedule_range | The 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| investment_amount | The initial value. | ||||||||||
| redemption_amount | The final value. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 0. |
IPMT(rate, period, term, pv, [fv], [type])
Returns the interest payment for a given period.
| rate | The interest rate per period. | ||||
| period | The period for which you want the interest amount. | ||||
| term | The total number of periods. | ||||
| pv | The present value. | ||||
| fv | The future value. If this parameter is omitted it is assumed to be zero. | ||||
| type | The timing of the payment. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to 0. |
Returns the internal rate of return.
| values_range | The list (array or reference) of payment and income values. |
| guess | The estimated rate of return. If this parameter is omitted it defaults to 0.1. |
Returns the interest payment for a given period.
| rate | The interest rate per period. |
| period | The period for which you want the interest amount. |
| term | The total number of periods. |
| pv | The present value. |
MDURATION(settlement_date, maturity_date, coupon, yield, frequency, [basis])
Returns the modified Macauley duration for a value of $100.
| settlement_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| coupon | The interest rate. | ||||||||||
| yield | The annual yield rate. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 0. |
MIRR(values_range, finance_rate, reinvest_rate)
Returns the modified internal rate of return.
| values_range | The list (array or reference) of payment and income values. |
| finance_rate | The interest rate on the payment values. |
| reinvest_rate | The interest rate on the income values. |
Returns the nominal annual interest rate.
| effect_rate | The effective annual interest rate. |
| npery | The number of compound interest payments per year. |
NPER(rate, payment, pv, [fv], [type])
Returns the number of periods required for an investment.
| rate | The interest rate per period. | ||||
| payment | The payment amount per period. | ||||
| pv | The present value. | ||||
| fv | The future value. If this parameter is omitted it defaults to 0. | ||||
| type | The timing of the payment. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to 0. |
NPV(rate, value1, [value2, ...])
Returns the net present value of an investment.
| rate | The 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| issue_date | The issue date. | ||||||||||
| first_coupon_date | The first coupon date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| yield | The annual yield rate. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| issue_date | The issue date. | ||||||||||
| first_coupon_date | The first coupon date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| price | The price per $100. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| last_coupon_date | The last coupon date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| yield | The annual yield rate. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| last_coupon_date | The last coupon date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| price | The price per $100. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 0. |
PMT(rate, term, [pv], [fv], [type])
Returns the payment amount for a loan.
| rate | The interest rate per period. | ||||
| term | The total number of periods. | ||||
| pv | The present value of the loan. If this parameter is omitted it defaults to 0. | ||||
| fv | The future value of the loan. If this parameter is omitted it defaults to 0. | ||||
| type | The timing of the payment. The possible values are: | ||||
| |||||
| 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.
| rate | The interest rate per period. | ||||
| period | The period for which you want the payment amount. | ||||
| term | The total number of periods. | ||||
| pv | The present value of the loan. | ||||
| fv | The future value of the loan. If this parameter is omitted it defaults to 0. | ||||
| type | The timing of the payment. The possible values are: | ||||
| |||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| yield | The annual yield rate. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| discount | The discount rate. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| issue_date | The issue date. | ||||||||||
| rate | The interest rate. | ||||||||||
| yield | The annual yield rate. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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.
| rate | The interest rate per period. | ||||
| term | The total number of periods. | ||||
| payment | The payment amount each period. If this parameter is omitted it is assumed to be zero. | ||||
| fv | The future value. If this parameter is omitted it is assumed to be zero. | ||||
| type | The timing of the payment. The possible values are: | ||||
| |||||
| 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.
| term | The total number of periods. | ||||
| payment | The payment amount each period. | ||||
| pv | The present value. | ||||
| fv | The future value. If this parameter is omitted it is assumed to be zero. | ||||
| type | The timing of the payment. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to 0. | |||||
| guess | The 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| investment | The investment amount. | ||||||||||
| discount | The discount rate. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 0. |
Returns the straight line depreciation.
| cost | The cost. |
| salvage | The salvage value. |
| life | The total number of periods. |
SYD(cost, salvage, life, period)
Returns the sum of years depreciation.
| cost | The cost. |
| salvage | The salvage value. |
| life | The total number of periods. |
| period | The period for which you want the depreciation. |
TBILLEQ(settlement_date, maturity_date, discount)
Returns the bond-equivalent yield for a treasury bill.
| settlement_date | The settlement date. |
| maturity_date | The maturity date. |
| discount | The discount rate. |
TBILLPRICE(settlement_date, maturity_date, discount)
Returns the price per $100 for a treasury bill.
| settlement_date | The settlement date. |
| maturity_date | The maturity date. |
| discount | The discount rate. |
TBILLYIELD(settlement_date, maturity_date, price)
Returns the yield for a treasury bill.
| settlement_date | The settlement date. |
| maturity_date | The maturity date. |
| price | The 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.
| cost | The cost. | ||||
| salvage | The salvage value. | ||||
| life | The total number of periods. | ||||
| start_period | The first period number for which to calculate depreciation. | ||||
| end_period | The last period number for which to calculate depreciation. | ||||
| factor | The rate at which the balance declines. If this parameter is omitted it defaults to 2. | ||||
| no_switch | Specifies whether to switch to straight-line depreciation when the straight-line depreciation is greater than the declining balance depreciation. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to FALSE. |
XIRR(values_range, dates_range, [guess])
Returns the internal rate of return.
| values_range | The list (array or reference) of payment and income values. |
| dates_range | The list (array or reference) of the dates of the payment and income values. |
| guess | The 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.
| rate | The discount rate per period. |
| values_range | The list (array or reference) of payment and income values. |
| dates_range | The 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| rate | The annual coupon rate. | ||||||||||
| price | The price per $100. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| frequency | The number of coupon payments per year. Should be 1, 2 or 4. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| price | The price per $100. | ||||||||||
| redemption | The redemption value per $100. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| 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_date | The settlement date. | ||||||||||
| maturity_date | The maturity date. | ||||||||||
| issue_date | The issue date. | ||||||||||
| rate | The interest rate. | ||||||||||
| price | The price per $100. | ||||||||||
| basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 0. |
Returns information about a worksheet cell.
| info_type | The type of information you want returned. The possible values are: | ||||||||||||||||||||||||
| |||||||||||||||||||||||||
| reference | The cell about which you want information. If this parameter is omitted it defaults to the cell containing the formula. |
Returns the number of blank cells in a range.
| range | The reference of the cells you want to check. |
Returns a number corresponding to the type of the error.
| error_val | The error value, or cell containing the error value, that you want to check. | ||||||||||||||||
| The returned values are: | |||||||||||||||||
|
Returns information about the current spreadsheet.
| type_text | The type of information you want returned. The possible values are: | ||||||||||||||||||||
|
Returns TRUE if the value is a reference to an empty cell, otherwise FALSE.
| value | The cell whose value you want to check. |
Returns TRUE if the value is an error other than #N/A, otherwise FALSE.
| value | The expression whose result you want to check. |
Returns TRUE if the value is an error, otherwise FALSE.
| value | The expression whose result you want to check. |
Returns TRUE if the number is even, otherwise FALSE.
| number | The number you want to check. |
Returns TRUE if the value is a logical value, otherwise FALSE.
| value | The expression whose result you want to check. |
Returns TRUE if the value is the error #N/A, otherwise FALSE.
| value | The expression whose result you want to check. |
Returns TRUE if the value is not a string, otherwise FALSE.
| value | The expression whose result you want to check. |
Returns TRUE if the value is a number, otherwise FALSE.
| value | The expression whose result you want to check. |
Returns TRUE if the number is odd, otherwise FALSE.
| number | The number you want to check. |
Returns TRUE if the value is a reference, otherwise FALSE.
| value | The expression whose result you want to check. |
Returns TRUE if the value is a string, otherwise FALSE.
| value | The expression whose result you want to check. |
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.
| value | The value you want as a number. |
Returns the error value #N/A.
Returns a number corresponding to the type of the value.
| value | The value, or cell containing the value, that you want to check. | ||||||||||
| The returned values are: | |||||||||||
|
AND(logical1, [logical2, ...])
Returns TRUE if all of the input values are TRUE.
| logical1, ... | The values that you want to check. |
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_test | An expression that results in TRUE or FALSE. |
| value_if_true | The value to be returned of the logical test is TRUE. If this parameter is omitted it defaults to 0. |
| value_if_false | The value to be returned of the logical test is FALSE. If this parameter is omitted it defaults to FALSE. |
Returns TRUE if the input expression evaluates to FALSE, and vice versa.
| logical | The logical expression for which you want the opposite value. |
Returns TRUE if any of the input values are TRUE.
| logical1, ... | The values that you want to check. |
Returns the logical value TRUE.
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Returns a string containing the specified cell address.
| row_num | The row number of the cell. | ||||||||
| column_num | The column number of the cell. | ||||||||
| abs_num | A number representing whether the row or column are to be absolute or relative. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to 1. | |||||||||
| a1 | Specifies the style of the reference. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to TRUE. | |||||||||
| sheet_text | Optional sheet name with which to prefix the reference. |
Returns the number of areas contained in the reference.
| reference | The reference whose areas you want to count. |
CHOOSE(index_num, value1, [value2, ...])
Returns one of several values depending on the index.
| index_num | The 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. |
Returns the column number of the reference.
| reference | The reference whose column number you want. If this parameter is omitted it defaults to the cell containing the function. |
Returns the number of columns in the reference.
| range | The reference whose columns you want to count. |
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_value | The value to be found in the table. | ||||
| table_range | A reference containing the table cells. | ||||
| row_index_num | The offset of the value to be returned, where 1 is the top row of the table. | ||||
| range_lookup | Whether to find an approximate or exact match. The possible values are: | ||||
| |||||
| 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_location | A text expression that evaluates to the form "filename" or "[filename]reference". |
| friendly_name | The 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.
| reference | The array or reference of which you want the subset. |
| row_num | The number of the row to return. If this parameter is omitted all rows will be returned. |
| col_num | The number of the column to return. If this parameter is omitted all columns will be returned. |
| area_num | The number of the area to return when the reference contains more than one area. If this parameter is omitted it defaults to 1. |
Returns a reference from the specified text.
| ref_text | A text expression that evaluates to the name of a cell or range of cells. | ||||
| a1 | Specifies the style of the reference. The possible values are: | ||||
| |||||
| 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_value | The value to be found in the table. |
| table_range | A reference containing the table cells. |
| result_range | The 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_value | The value to be found in the table. | ||||||
| lookup_range | A reference containing the table cells. | ||||||
| match_type | Whether to find an approximate or exact match. The possible values are: | ||||||
| |||||||
| If this parameter is omitted it defaults to 1. |
OFFSET(reference, rows, cols, [height], [width])
Returns a new reference based on the specified reference.
| reference | The reference to be used as a starting point. |
| rows | The number of rows to move the reference up (negative) or down (positive). |
| cols | The number of columns to move the reference left (negative) or right (positive). |
| height | The height of the new reference. If this parameter is omitted it defaults to the height of the old reference. |
| width | The width of the new reference. If this parameter is omitted it defaults to the width of the old reference. |
Returns the row number of the reference.
| reference | The reference whose row number you want. If this parameter is omitted it defaults to the cell containing the function. |
Returns the number of rows in the reference.
| range | The reference whose rows you want to count. |
Returns the transposition of the specified array or reference.
| array | The 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_value | The value to be found in the table. | ||||
| table_range | A reference containing the table cells. | ||||
| col_index_num | The offset of the value to be returned, where 1 is the left column of the table. | ||||
| range_lookup | Whether to find an approximate or exact match. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. |
Returns the absolute value of a number.
| number | The number for which you want the absolute value. |
Returns the angle in radians corresponding to the arccosine of a number.
| number | The number for which you want the arccosine. |
Returns the angle in radians corresponding to the inverse hyperbolic cosine of a number.
| number | The number for which you want the inverse hyperbolic cosine. |
Returns the angle in radians corresponding to the arcsine of a number.
| number | The number for which you want the arcsine. |
Returns the angle in radians corresponding to the inverse hyperbolic sine of a number.
| number | The number for which you want the inverse hyperbolic sine. |
Returns the angle in radians corresponding to the arctangent of a number.
| number | The number for which you want the arctangent. |
Returns the angle in radians corresponding to a pair of co-ordinates.
| x_num | The x co-ordinate for which you want the angle. |
| y_num | The y co-ordinate for which you want the angle. |
Returns the angle in radians corresponding to the inverse hyperbolic tangent of a number.
| number | The number for which you want the inverse hyperbolic tangent. |
Returns the number rounded up (away from zero) to the next multiple of significance.
| number | The number which you want rounded. |
| significance | The units you want to use for rounding. |
Returns the number of combinations in which a number of items can be chosen from a total number.
| number | The total number of items. |
| number_chosen | The number of items chosen. |
Returns the cosine of an angle.
| number | The angle for which you want the cosine. It must be specified in radians. |
Returns the hyperbolic cosine of an angle.
| number | The angle for which you want the hyperbolic cosine. It must be specified in radians. |
Returns the number of cells in a specified range that meet the specified criteria.
| range | A reference to the cells you want to count. |
| criteria | A string containing a comparison expression. |
Converts an angle from radians to degrees.
| angle | The angle that you want to convert. |
Returns the number rounded up to the next even number.
| number | The number which you want rounded. |
Returns the e raised to the power number.
| number | The power to which you want to raise e. |
Returns the factorial of a number.
| number | The number of which you want the factorial. |
Returns the number rounded down (towards zero) to the next multiple of significance.
| number | The number which you want rounded. |
| significance | The units you want to use for rounding. |
Returns the greatest common divisor of a set of numbers.
| number1, ... | The numbers of which you want the greatest common divisor. |
Returns the number rounded down to the next integer.
| number | The number which you want rounded. |
Returns the lowest common multiple of a set of numbers.
| number1, ... | The numbers of which you want the lowest common multiple. |
Returns the natural logarithm of a number.
| number | The number for which you want the natural logarithm. |
Returns the logarithm of a number in a specified base.
| number | The number for which you want the logarithm. |
| base | The base in which you want the logarithm. If this parameter is omitted it defaults to 10. |
Returns the base 10 logarithm of a number.
| number | The number for which you want the base 10 logarithm. |
Returns the determinant of a matrix.
| range | An array or reference to cells containing the matrix. |
Returns the inverse of a matrix.
| array | An array or reference to cells containing the matrix. |
Returns the product of two matrices.
| array1 | An array or reference to cells containing the first matrix. |
| array2 | An array or reference to cells containing the second matrix. |
Returns the remainder of a division.
| number | The dividend. |
| divisor | The divisor. |
Returns the number rounded to a multiple.
| number | The number that you want rounded. |
| multiple | The 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. |
Returns the number rounded up to the next odd number.
| number | The number which you want rounded. |
Returns the value of Pi.
Returns the number raised to the power.
| number | The number which you want raised to a power. |
| power | The 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.
| numerator | The dividend. |
| denominator | The divisor. |
Converts an angle from degrees to radians.
| angle | The angle that you want to convert. |
Returns a random number between 0 and 1.
RANDBETWEEN(smallest, largest)
Returns a random integer in the range you specify.
| smallest | The lower limit of the range. |
| largest | The upper limit of the range. |
Returns a string representing a number converted to roman numerals.
| number | The number that you want to convert. | ||||||||||||||
| form | Specifies the compactness of the representation. The possible values are: | ||||||||||||||
| |||||||||||||||
| If this parameter is omitted it defaults to 0. |
Returns a number rounded to the number of decimal places specified.
| number | The number that you want rounded. |
| num_digits | The 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.
| number | The number that you want rounded. |
| num_digits | The number of decimal places to round to (may be negative). If this parameter is omitted it defaults to 0. |
Returns a number rounded up to the number of decimal places specified.
| number | The number that you want rounded. |
| num_digits | The number of decimal places to round to (may be negative). If this parameter is omitted it defaults to 0. |
SERIESSUM(x, n, m, coefficients)
Returns the value of a polynomial.
| x | The number to be raised to a power. |
| n | The power of the first term in the series. |
| m | The difference between successive powers in the series. |
| coefficients | An array or reference to cells containing the coefficients. |
Returns the sign of a number; 1 for positive, -1 for negative and 0 for zero.
| number | The number of which you want the sign. |
Returns the sine of an angle.
| number | The angle for which you want the sine. It must be specified in radians. |
Returns the hyperbolic sine of an angle.
| number | The angle for which you want the hyperbolic sine. It must be specified in radians. |
Returns the square root of a number.
| number | The number of which you want the square root. |
Returns the square root of a number after it is multiplied by Pi.
| number | The 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_num | The subtotal function that you want. The possible values are: | ||||||||||||||||||||||
| |||||||||||||||||||||||
| ref1, ... | The ranges of cells that you want subtotalled. |
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.
| range | A reference to the cells you want to check. |
| criteria | A string containing a comparison expression. |
| sum_range | A 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. |
SUMPRODUCT(range1, [range2, ...])
Returns the sum of the products of the cells in one or more ranges.
| range1, ... | 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. |
Returns the sum of the differences of the squares of the numbers in the ranges.
| x_range | The first range of numbers. |
| y_range | The second range of numbers. |
Returns the sum of the sums of the squares of the numbers in the ranges.
| x_range | The first range of numbers. |
| y_range | The second range of numbers. |
Returns the sum of the squares of the differences of the numbers in the ranges.
| x_range | The first range of numbers. |
| y_range | The second range of numbers. |
Returns the tangent of an angle.
| number | The angle for which you want the tangent. It must be specified in radians. |
Returns the hyperbolic tangent of an angle.
| number | The angle for which you want the hyperbolic tangent. It must be specified in radians. |
Returns a number truncated to the number of decimal places specified.
| number | The number that you want truncated. |
| num_digits | The number of decimal places to truncate to (may be negative). |
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. |
BETADIST(x, alpha, beta, [A], [B])
Returns the cumulative beta distribution probability.
| x | The value at which you want to evaluate the function. |
| alpha | The alpha value. |
| beta | The beta value. |
| A | The lower limit. If this parameter is omitted it defaults to 0. |
| B | The 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.
| probability | The cumulative beta distribution probability for which you want the value. |
| alpha | The alpha value. |
| beta | The beta value. |
| A | The lower limit. If this parameter is omitted it defaults to 0. |
| B | The upper limit. If this parameter is omitted it defaults to 1. |
BINOMDIST(number_successes, trials, probability, cumulative)
Returns the binomial distribution probability.
| number_successes | The number of trials that are successful. | ||||
| trials | The total number of trials. | ||||
| probability | The probability of a single trial being successful. | ||||
| cumulative | Specifies whether to return the cumulative probability or not. The possible values are: | ||||
|
Returns the chi-squared distribution probability.
| x | The value at which you want to evaluate the function. |
| degrees_freedom | The number of degrees of freedom. |
CHIINV(probability, degrees_freedom)
Returns the value associated with the specified chi-squared distribution probability.
| probability | The probability for which you want the value. |
| degrees_freedom | The number of degrees of freedom. |
CHITEST(actual_range, expected_range)
Returns the probability result of the chi-squared test.
| actual_range | An array or reference to cells containing the empirical results. |
| expected_range | An array or reference to cells containing the theoretical results. |
CONFIDENCE(alpha, standard_deviation, size)
Returns the confidence interval for a population mean.
| alpha | The significance level. |
| standard_deviation | The population standard deviation. |
| size | The sample size. |
Returns the correlation coefficient of two ranges.
| range1 | The first range to be compared. |
| range2 | The second range to be compared. |
Returns the count of numbers in a list.
| value1, ... | The items whose numbers are to be counted. |
Returns the count of values in a list.
| value1, ... | The items whose values are to be counted. |
Returns the covariance of two ranges.
| range1 | The first range to be compared. |
| range2 | The 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.
| trials | The total number of trials. |
| probability_s | The probability of a single trial being successful. |
| alpha | The 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. |
EXPONDIST(x, lambda, cumulative)
Returns the exponential distribution probability.
| x | The value at which you want to evaluate the function. | ||||
| lambda | The lambda value. | ||||
| cumulative | Specifies whether to return the cumulative probability or not. The possible values are: | ||||
|
FDIST(x, degrees_freedom1, degrees_freedom2)
Returns the F distribution probability.
| x | The value at which you want to evaluate the function. |
| degrees_freedom1 | The degrees of freedom of the first set. |
| degrees_freedom2 | The degrees of freedom of the second set. |
FINV(probability, degrees_freedom1, degrees_freedom2)
Returns the value associated with the specified F distribution probability.
| probability | The probability for which you want the value. |
| degrees_freedom1 | The degrees of freedom of the first set. |
| degrees_freedom2 | The degrees of freedom of the second set. |
Returns the Fisher transformation.
| x | The value at which to evaluate the function. |
Returns the inverse Fisher transformation.
| y | The value at which to evaluate the function. |
Returns the expected value of y for a given x value for a line passing through a specified set of points.
| x | The x value at which to evaluate the function. |
| y_range | The y values that are already known. |
| x_range | The x values that are already known. |
FREQUENCY(data_array, bins_array)
Returns the counts of items in specified numeric categories.
| data_array | An array or reference to a range of cells containing values to be counted. |
| bins_array | An array or reference to a range of cells containing the upper limits for each category. |
Returns the probability result of the F test.
| range1 | The first range to be compared. |
| range2 | The second range to be compared. |
GAMMADIST(x, alpha, beta, cumulative)
Returns the gamma distribution probability.
| x | The value at which you want to evaluate the function. | ||||
| alpha | The alpha value. | ||||
| beta | The beta value. | ||||
| cumulative | Specifies whether to return the cumulative probability or not. The possible values are: | ||||
|
GAMMAINV(probability, alpha, beta)
Returns the value associated with the specified gamma distribution probability.
| probability | The probability for which you want the value. |
| alpha | The alpha value. |
| beta | The beta value. |
Returns the natural logarithm of the gamma function evaluated at x.
| x | The 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_ys | The y values that are already known. | ||||
| known_xs | The 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_xs | The new x values for which y values are required. If this parameter is omitted it defaults to the known xs. | ||||
| const | Specifies whether the line must pass through the origin. The possible values are: | ||||
| |||||
| 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. |
HYPGEOMDIST(sample_s, number_sample, population_s, number_population)
Returns the hypergeometric distribution probability.
| sample_s | The number of sample trials that are successful. |
| number_sample | The total number of trials in the sample. |
| population_s | The number of population trials that are successful. |
| number_population | The total number of trials in the population. |
Returns the expected value of y when x is zero for a line passing though a specified set of points.
| y_range | The y values that are already known. |
| x_range | The x values that are already known. |
Returns the kurtosis of a set of numbers.
| number1, ... | The numbers of which you want the kurtosis. |
Returns the kth largest number in a set of numbers.
| range | An array or reference to cells containing numbers of which you want the kth largest. |
| k | The 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_ys | The y values that are already known. | ||||
| known_xs | One 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. | ||||
| const | Specifies whether the line must pass through the origin. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. | |||||
| stats | Specifies 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: | ||||
| |||||
| 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_ys | The y values that are already known. | ||||
| known_xs | One 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. | ||||
| const | Specifies whether the line must pass through the origin. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. | |||||
| stats | Specifies 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: | ||||
| |||||
| 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.
| probability | The probability for which you want the value. |
| mean | The mean of the natural logarithms of the values. |
| standard_dev | The standard deviation of the natural logarithms of the values. |
LOGNORMDIST(x, mean, standard_dev)
Returns the cumulative lognormal distribution probability.
| x | The value at which you want to evaluate the function. |
| mean | The mean of the natural logarithms of the values. |
| standard_dev | The standard deviation of the natural logarithms of the values. |
Returns the maximum of a set of numbers.
| number1, ... | The numbers of which you want the maximum. |
Returns the maximum of a set of values.
| value1, ... | The values of which you want the maximum. |
MEDIAN(number1, [number2, ...])
Returns the median of a set of numbers.
| number1, ... | The numbers of which you want the median. |
Returns the minimum of a set of numbers.
| number1, ... | The numbers of which you want the minimum. |
Returns the minimum of a set of values.
| value1, ... | The values of which you want the minimum. |
Returns the mode of a set of numbers.
| number1, ... | The numbers of which you want the mode. |
NEGBINOMDIST(number_f, number_s, probability_s)
Returns the negative binomial distribution probability.
| number_f | The number of trials that fail. |
| number_s | The threshold number of trials that are successful. |
| probability_s | The probability of a single trial being successful. |
NORMDIST(x, mean, standard_dev, cumulative)
Returns the normal distribution probability.
| x | The value at which you want to evaluate the function. | ||||
| mean | The mean of the values. | ||||
| standard_dev | The standard deviation of the values. | ||||
| cumulative | Specifies whether to return the cumulative probability or not. The possible values are: | ||||
|
NORMINV(probability, mean, standard_dev)
Returns the value associated with the specified cumulative normal distribution probability.
| probability | The probability for which you want the value. |
| mean | The mean of the values. |
| standard_dev | The standard deviation of the values. |
Returns the cumulative standard normal distribution probability.
| z | The value at which you want to evaluate the function. |
Returns the value associated with the specified cumulative standard normal distribution probability.
| probability | The probability for which you want the value. |
Returns the Pearson correlation coefficient.
| range1 | The first range to be compared. |
| range2 | The second range to be compared. |
Returns the kth percentile of a set of values.
| range | An array or reference to cells containing the values. |
| k | The percentile value. |
PERCENTRANK(range, x, [significance])
Returns the percentile of a value in a set of values.
| range | An array or reference to cells containing the values. |
| x | The value of which you want the percentile. |
| significance | The number of decimal places required in the result. If this parameter is omitted it defaults to 3. |
Returns the number of permutations in which a number of items can be chosen from a total number.
| number | The total number of items. |
| number_chosen | The number of items chosen. |
Returns the Poisson distribution probability.
| x | The value at which you want to evaluate the function. | ||||
| mean | The mean of the values. | ||||
| cumulative | Specifies whether to return the cumulative probability or not. The possible values are: | ||||
|
PROB(x_range, prob_range, lower_limit, [upper_limit])
Returns the probability that numbers in a set are between the specified limits.
| x_range | An array or reference to cells containing the numbers. |
| prob_range | An array or reference to cells containing the probabilities associated with each number. These values must add up to 1. |
| lower_limit | The lower limit of the test. |
| upper_limit | The upper limit of the test. If this value is omitted it defaults to the value specified for the lower limit. |
Returns the specified quartile of a set of numbers.
| range | An array or reference to cells containing the numbers. | ||||||||||
| quart | Specifies which quartile to return. The possible values are: | ||||||||||
|
Returns the rank of a number in a set of numbers.
| number | The number of which you want the rank. | ||||
| range | An array or reference to cells containing the values. | ||||
| order | Specifies whether the list is treated as being in ascending or descending order of value. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to 0. |
Returns the square of the Pearson correlation coefficient.
| y_range | The first range to be compared. |
| x_range | The second range to be compared. |
Returns the skewness of a set of numbers.
| number1, ... | The numbers of which you want the skewness. |
Returns the slope of a line passing through a specified set of points.
| y_range | The y values that are already known. |
| x_range | The x values that are already known. |
Returns the kth smallest number in a set of numbers.
| range | An array or reference to cells containing numbers of which you want the kth smallest. |
| k | The 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.
| x | The value that you want to standardize. |
| mean | The mean of the values. |
| standard_dev | The 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. |
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. |
Returns the standard error of the y values of a line passing through a specified set of points.
| y_range | The y values that are already known. |
| x_range | The x values that are already known. |
TDIST(x, degrees_freedom, tails)
Returns the Student's T distribution probability.
| x | The value at which you want to evaluate the function. |
| degrees_freedom | The degrees of freedom. |
| tails | Specifies 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.
| probability | The probability for which you want the value. |
| degrees_freedom | The 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_ys | The y values that are already known. | ||||
| known_xs | The 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_xs | The new x values for which y values are required. If this parameter is omitted it defaults to the known xs. | ||||
| const | Specifies whether the line must pass through the origin. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. |
Returns the mean of a set of numbers with the extreme values removed.
| range | An array or reference to cells containing the numbers. |
| percent | The percentage of the numbers to exclude from the calculation. |
TTEST(range1, range2, tails, type)
Returns the probability result of the Student's T test.
| range1 | The first range to be compared. | ||||||
| range2 | The second range to be compared. | ||||||
| tails | Specifies the tails to include in the distribution. Should be 1 or 2. | ||||||
| type | Specifies which type of test is required. The possible values are: | ||||||
|
Returns the variance (based on a population sample) of a set of numbers.
| number1, ... | The numbers of which you want the variance. |
Returns the variance (based on a population sample) of a set of values.
| value1, ... | The values of which you want the variance. |
Returns the variance (based on the entire population) of a set of numbers.
| number1, ... | The numbers of which you want the variance. |
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.
| x | The value at which you want to evaluate the function. | ||||
| alpha | The alpha value. | ||||
| beta | The beta value. | ||||
| cumulative | Specifies whether to return the cumulative probability or not. The possible values are: | ||||
|
Returns the probability result of the z test.
| range | An array or reference to cells containing the data against which x is to be tested. |
| x | The value to be tested. |
| sigma | The population standard deviation. If this parameter is omitted it defaults to the sample standard deviation of the data. |
Returns the character corresponding to the specified position in the character set.
| number | The number of the character that you want. |
Returns a string corresponding to the input string with any unprintable characters removed.
| text | The text from which you want to remove unprintable characters. |
Returns the position in the character set of the first character in the string.
| text | The character of which you want the character code. |
CONCATENATE(text1, [text2, ...])
Returns a string consisting of the input strings concatenated together.
| text1, ... | The strings that you want to concatenate. |
Returns a string consisting of the input number rounded to the specified number of decimal places and converted to currency format.
| number | The amount that you want to format. |
| decimals | The number of decimal places required. If this parameter is omitted it defaults to 2. |
Returns TRUE if the input strings are exactly the same, otherwise FALSE.
| text1 | The first string to be compared. |
| text2 | The 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_text | The text you want to find. |
| within_text | The text to be searched. |
| start_num | The 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.
| number | The number that you want to format. | ||||
| decimals | The number of decimal places required. If this parameter is omitted it defaults to 2. | ||||
| no_commas | Specifies whether to suppress the thousands separator in the formatted number. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to FALSE. |
Returns a string consisting of the specified number of characters from the beginning of the input string.
| text | The string of which you want the beginning. |
| num_chars | The number of characters to extract from the string. If this parameter is omitted it defaults to 1. |
Returns the length of the input string.
| text | The string of which you want the length. |
Returns a string which is the input string with all of the characters converted to lower case.
| text | The 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.
| text | The string of which you want the extract. |
| start_num | The starting position of characters to extract from the string. |
| num_chars | The number of characters to extract from the string. |
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.
| text | The 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_text | The string you want to convert. |
| start_num | The starting position of the text to be removed. |
| num_chars | The number of characters to be removed. |
| new_text | The text to be inserted into the string. |
Returns a string which is the input string repeated a specified number of times.
| text | The string you want to repeat. |
| number_times | The number of times to repeat the string. |
Returns a string consisting of the specified number of characters from the end of the input string.
| text | The string of which you want the end. |
| num_chars | The 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_text | The text you want to find. |
| within_text | The text to be searched. |
| start_num | The 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.
| text | The string you want to convert. |
| old_text | The string to be removed from the original text. |
| new_text | The string to be inserted in the original text. |
| instance_num | Specifies which occurrence of old_text is to be replaced. If this parameter is omitted then all occurrences are replaced. |
Returns a string which is the input value if it is text, otherwise an empty string.
| value | The value you want if it is a string. |
Returns a string consisting of the input number formatted using the specified format string.
| value | The number that you want to format. |
| format_text | The format string to use. |
Returns a string consisting of the input string with any extra spaces removed.
| text | The string that you want to format. |
Returns a string which is the input string with all of the characters converted to upper case.
| text | The string that you want to convert. |
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.
| text | The string that you want to convert to a number. |
ARGUMENT([name_text], [type], [reference])
Defines an argument for a custom function.
| name_text | The defined name that will be assigned to the argument. If this parameter is omitted then no name will be assigned. | ||||||||||||
| type | The 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): | ||||||||||||
| |||||||||||||
| If this parameter is omitted it defaults to 7. | |||||||||||||
| reference | The cell reference where the argument value will be stored. If this parameter is omitted then the argument value will not be stored. |
Ends the processing of a WHILE-NEXT loop. The macro will continue with the statement after the NEXT function.
Introduces the block of statements to be processed when the corresponding IF or ELSE.IF condition is false.
Specifies another logical test that conditions a block of statements when the corresponding IF or ELSE.IF condition is false.
| logical_test | An expression that results in TRUE or FALSE. |
Specifies the end of the block of statements conditioned by the corresponding IF or ELSE.IF.
Forces the macro to continue with the statement at the reference.
| reference | The reference of the cell where macro execution is to continue. |
Specifies a logical test that conditions the execution of a block of statements.
| logical_test | An expression that results in TRUE or FALSE. |
Specifies the end of the block of statements contained in a WHILE-NEXT loop. The logical test in the corresponding WHILE statement will be done again, and if it is true then the block of statements in the loop will be executed again, otherwise execution will continue with the statement after this NEXT.
Forces the macro to end, and if it is a custom function then to return the specified value.
| value | For custom functions, the value to be returned. |
Assigns a defined name to the specified value.
| name_text | The defined name to use. |
| value | The value to be associated with the name. If this parameter is omitted then the name is deleted. |
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.
| reference | The cells where the values are to be stored. |
| values | The values to be stored. |
Forces the macro to wait until the date/time specified by the serial number.
| serial_number | The date/time when the macro is to resume. If this parameter is omitted the macro does not wait. |
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_test | An expression that results in TRUE or FALSE. |
ACTIVATE([window_text], [pane_num])
Specifies the workbook, sheet and/or pane that is to be active.
| window_text | The workbook and/or sheet name. If this parameter is omitted then the active window is not changed. | ||||||||
| pane_num | For a split window, specifies which pane is to be active. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it then the active pane is not changed. |
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_text | The text to be displayed in the message box. | ||||||
| type_num | The type of message box to be displayed. The possible values are: | ||||||
| |||||||
| If this parameter is omitted it defaults to 2. | |||||||
| help_ref | A reference to a Help topic (not currently implemented). |
Causes the computer to beep.
| tone_num | A 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. |
Causes the current worksheet to be recalculated.
Causes all worksheets to be recalculated.
Returns information about the caller of the macro.
| Macro called from: | Function returns: |
| user-defined function | reference of cell containing function call |
| user-defined function in array formula | reference of array formula range |
| attached to control or drawing object | object identifier |
| manually (control key combination) | error value #REF! |
Simulates the Edit Clear menu option.
| type_num | The type of information to clear from the current selection on the active worksheet. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to 3. |
COLUMN.WIDTH([width_num], [reference], [standard], [type_num], [standard_num])
Simulates the Format Column menu options.
| width_num | The 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. | ||||||
| reference | The columns whose width is to be changed. If this parameter is omitted it defaults to the current selection on the active worksheet. | ||||||
| standard | Specifies whether the columns are to be set to the standard column width. The possible values are: | ||||||
| |||||||
| If this parameter is omitted it defaults to FALSE. | |||||||
| type_num | Specifies whether the columns are to be hidden, unhidden or automatically sized. This parameter is ignored if standard is TRUE. The possible values are: | ||||||
| |||||||
| If this parameter is omitted the columns will not be hidden, unhidden or auto-sized. | |||||||
| standard_num | Specifies 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_reference | The 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_reference | The cells where the copied information is to be pasted. If this parameter is omitted then the cells are not pasted. |
CUT([from_reference], [to_reference])
Simulates the Edit Cut menu option.
| from_reference | The 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_reference | The cells where the cut information is to be pasted. If this parameter is omitted then the cells are not pasted. |
Simulates the Format Data Form menu option.
DATA.SERIES([rowcol], [type_num], [date_num], [step_value], [stop_value], [trend])
Simulates the Edit Fill Series menu option.
| rowcol | Indicates whether the series is in rows or columns. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults depending on the shape of the current selection. | |||||||||
| type_num | The way in which the series values change. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to 1. | |||||||||
| date_num | The way in which the date values change. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to 1. | |||||||||
| step_value | The number used to add to or multiply by the series value. If this parameter is omitted it defaults to 1. | ||||||||
| stop_value | The value at which to stop filling the series. If this parameter is omitted then the entire selection is filled. | ||||||||
| trend | Indicates whether to fill the series using trend values calculated from existing data. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to FALSE. |
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.
| reference | The reference to the cells whose values you want. |
Simulates the Edit Delete menu option.
| shift_num | The way in which the cell deletion is to be processed. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to 2. |
Returns the result of evaluating a formula.
| formula_text | The formula to be evaluated. |
Simulates the Edit Fill Down menu option.
Simulates the Edit Fill Left menu option.
Simulates the Edit Fill Right menu option.
Simulates the Edit Fill Up menu option.
FONT.PROPERTIES([font], [font_style], [size], [strikethrough], [superscript], [subscript], [outline], [shadow], [underline], [color], [normal], [background], [start_char], [char_count])
Formats the cells in the selection on the current worksheet using the specified font attributes.
| font | The name of the font to be used. | ||||||||||
| font_style | The style to be used. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted the style is not changed. | |||||||||||
| size | The point size to be used. If this parameter is omitted the size is not changed. | ||||||||||
| strikethrough | Sets the strikethrough attribute. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted the strikethrough attribute is not changed. | |||||||||||
| superscript | Not currently implemented. | ||||||||||
| subscript | Not currently implemented. | ||||||||||
| outline | Not currently implemented. | ||||||||||
| shadow | Not currently implemented. | ||||||||||
| underline | The underline style to be used. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted the underline style is not changed. | |||||||||||
| color | The color to be used. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted the color is not changed. | |||||||||||
| normal | Not currently implemented. | ||||||||||
| background | Not currently implemented. | ||||||||||
| start_char | Not currently implemented. | ||||||||||
| char_count | Not currently implemented. |
Formats the cells in the selection on the current worksheet using the specified format.
| format_text | The number format to be used. |
FORMULA(formula_text, [reference])
Enters the specified formula in the specified reference.
| formula_text | The formula to be inserted. |
| reference | The reference where formula to be inserted. If this parameter is omitted it defaults to the cursor position on the active worksheet. |
FORMULA.GOTO([reference], [corner])
Simulates the Edit Goto menu option.
| reference | The 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. | ||||
| corner | Specifies whether the top-left corner of the selection is to be placed in the top-left corner of the window. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to FALSE. |
GET.OBJECT(type_num, [object_id], [start_num], [count_num], [item_index])
Returns information about the specified object.
| type_num | The type of information to return. The possible values are: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| object_id | The object identifier. If this parameter is omitted it defaults to the currently selected object on the active worksheet. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| start_num | The text substring start position for type_num 12. If this parameter is omitted it defaults to 1. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| count_num | The text substring length for type_num 12. If this parameter is omitted it defaults to 255. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| item_index | This parameter is not currently used. |
GOAL.SEEK(target_cell, target_value, variable_cell)
Simulates the Format Data Goal seek menu option.
| target_cell | The cell that is required to be a certain value. It must be a single cell containing a formula that returns a numeric result. |
| target_value | The value that the target cell needs to reach. |
| variable_cell | The cell whose value can be changed. It must be a single cell that either contains a number or is empty. |
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_text | The message that you want displayed in the dialog box. | ||||||||||||||
| type_num | The 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): | ||||||||||||||
| |||||||||||||||
| If this parameter is omitted it defaults to 2. | |||||||||||||||
| title_text | The title to be used for the dialog box. If this parameter is omitted it defaults to "Input". | ||||||||||||||
| default | The 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_pos | The 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 supported) | ||||||||||||||
| y_pos | The 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 supported) | ||||||||||||||
| help_ref | The 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 supported) |
Simulates the Insert Cells menu option.
| shift_num | The way in which the cell insertion is to be processed. The possible values are: | ||||||||
| |||||||||
| If this parameter is omitted it defaults to 2. |
ON.SHEET([sheet_text], [macro_text], [activate_logical])
Specifies a macro to be run when a sheet is activated or deactivated.
| sheet_text | The 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_text | The defined name that refers to the macro that is to be run. If this parameter is omitted then no macro will be run. | ||||
| activate_logical | Specifies whether the macro is to be run when the sheet is activated or deactivated. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. |
Simulates the Edit Paste menu option.
| to_reference | The 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_num | The type of information to be pasted. The possible values are: | ||||||||||||
| |||||||||||||
| If this parameter is omitted it defaults to 1. | |||||||||||||
| operation_num | The way in which the data being pasted is merged with existing data. The possible values are: | ||||||||||||
| |||||||||||||
| If this parameter is omitted it defaults to 1. | |||||||||||||
| skip_blanks | Whether to ignore blank cells in the data being pasted. The possible values are: | ||||||||||||
| |||||||||||||
| If this parameter is omitted it defaults to FALSE. | |||||||||||||
| transpose | Whether to transpose the data being pasted. The possible values are: | ||||||||||||
| |||||||||||||
| If this parameter is omitted it defaults to FALSE. |
PROTECT.DOCUMENT([contents], [windows], [password], [objects], [scenarios])
Simulates the Format Sheet Protect menu option.
| contents | Whether to protect the locked cell contents. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. | |||||
| windows | Whether to protect windows from being moved or sized (not currently implemented). The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to FALSE. | |||||
| password | An optional case-sensitive password to protect or unprotect the document (not currently implemented). | ||||
| objects | Whether to protect the locked objects (not currently implemented). The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. | |||||
| scenarios | Whether to protect the scenarios (not currently implemented). The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to TRUE. |
Returns a string containing the specified reference.
| reference | The reference that is to be converted to text. | ||||
| a1 | Specifies the style of the reference. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to FALSE. |
ROW.HEIGHT([height_num], [reference], [standard_height], [type_num])
Simulates the Format Row menu options.
| height_num | The 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. | ||||||
| reference | The rows whose height is to be changed. If this parameter is omitted it defaults to the current selection on the active worksheet. | ||||||
| standard_height | Specifies whether the rows are to be set to the standard row height. The possible values are: | ||||||
| |||||||
| If this parameter is omitted it defaults to FALSE. | |||||||
| type_num | Specifies whether the rows are to be hidden, unhidden or automatically sized. This parameter is ignored if standard_height is TRUE. The possible values are: | ||||||
| |||||||
| If this parameter is omitted the rows will not be hidden, unhidden or auto-sized. |
SELECT([selection], [active_cell])
Sets the selection and cursor cell on the active worksheet.
| selection | The cells that are to be the selection. If this parameter is omitted the current selection is not changed. |
| active_cell | The cell that is to be the cursor cell. If this parameter is omitted it defaults to the top left cell in the selection. |
Simulates the Ctrl+arrow key combination.
| direction_num | The direction in which to move the cursor. The possible values are: | ||||||||
|
Simulates the Ctrl+End key combination.
Returns a reference which represents the current selection on the active worksheet.
SORT([orientation], [key1], [order1], [key2], [order2], [key3], [order3], [header], [custom], [case])
Simulates the Format Data Sort menu option.
| orientation | Specifies whether to sort the rows or columns. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 1. | |||||||||||
| key1 | Specifies 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. | ||||||||||
| order1 | Specifies whether to sort the data in ascending or descending order. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 1. | |||||||||||
| key2 | Specifies a reference to the second column or row to sort on. If this parameter is omitted then the second key is not used. | ||||||||||
| order2 | Specifies whether to sort the data in ascending or descending order. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 1. | |||||||||||
| key3 | Specifies a reference to the third column or row to sort on. If this parameter is omitted then the third key is not used. | ||||||||||
| order3 | Specifies whether to sort the data in ascending or descending order. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 1. | |||||||||||
| header | Specifies whether the selected cells include a header row or column. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 2. | |||||||||||
| custom | Specifies whether to use a custom list for the first sort key. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to 1. | |||||||||||
| case | Specifies whether the sort should be case-sensitive. The possible values are: | ||||||||||
| |||||||||||
| If this parameter is omitted it defaults to FALSE. |
Returns a reference corresponding to the specified text.
| text | The string containing the reference. | ||||
| a1 | Specifies the style of the reference. The possible values are: | ||||
| |||||
| If this parameter is omitted it defaults to FALSE. |
Specifies the sheet that is to be the active worksheet.
| sheet_name | The sheet that is to be the active worksheet. |
Deletes the specified sheet from the workbook.
| sheet_name | The sheet that is to be deleted. If this parameter is omitted it defaults to the currently active sheet. |
Adds a new sheet to the workbook.
| type_num | The type of sheet that is to be added. The possible values are: | ||||||
| |||||||
| If this parameter is omitted it defaults to the type of the currently active sheet. |
WORKBOOK.NAME(old_sheet_name, new_sheet_name)
Renames the specified sheet in the workbook.
| old_sheet_name | The sheet that is to be renamed. |
| new_sheet_name | The new name for the sheet. |
CALL(module_text, procedure, type_text, [argument1, ...])
Returns a value from an external function.
| module_text | The name of the DLL that contains the function. | ||||||||||||||||||||||||||||||||||
| procedure | The name of the function. | ||||||||||||||||||||||||||||||||||
| type_text | A string specifying the result type and the argument types. Each type is represented by a single character. The possible character values are: | ||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||
| argument1, ... | The arguments to pass to the function. |
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:
TypeThe program currently supports the following chart types:
| Column | Displays vertical columns whose heights correspond to the data values. |
| Bar | Displays horizontal bars whose lengths correspond to the data values. |
| Line | Displays a line through points whose vertical positions correspond to the data values, the horizontal positions are evenly spaced. |
| Pie | Displays a circle divided up into regions whose relative sizes correspond to the data values. |
| Doughnut | Like the Pie chart but displays one or more series. |
| Scatter | Displays a line through points whose x and y coordinates are given by the data values. |
| Area | Like the line chart, but the area below the line is filled in, and the series values are stacked. |
| Radar | Displays a line through points whose distances from a central point correspond to the data values. |
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:
| Data | Name | An optional name that is used to identify the series in the list box. It defaults to 'Series1', etc. |
| Y values | A 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 labels | For 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. | |
| Patterns | Line type | Allows you to choose a solid, dashed or dotted line. |
| Line color | Allows you to specify the color of the line. | |
| Marker type | For Line, Scatter and Radar charts, allows you to choose a marker for the data points. | |
| Marker colors | For Line, Scatter and Radar charts, allows you to choose the foreground and background colors for the marker. | |
| Area color | For Column, Bar, Pie, Doughnut and Area charts, allows you to specify the colour that will be used to fill the interior. | |
| Labels | Label type | Allows you to display the x or y values next to the data points, and for Pie and Doughnut charts the percentages. |
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.
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.
| Axis | Specifies whether the axis line will be drawn. |
| Gridlines | Specifies whether the guidelines will be drawn. |
| Ticks | Specifies whether the axis tick marks will be drawn. |
| Values | Specifies whether the axis values will be displayed. |
| Logarithmic | Specifies whether a linear or logarithmic scale is required. |
| Minimum | Specifies the minimum value to be used on the scale. |
| Maximum | Specifies the maximum value to be used on the scale. |
| Step | Specifies the interval between the values on the scale. |
This allows you to specify the 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 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 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 | |
| n | File New | |
| o | File Open | |
| p | File Print | |
| r | Edit Fill Right | |
| s | File Save | |
| v | Edit Paste | |
| x | Edit Cut | |
| z | Edit Undo | |
| ; | insert date | |
| : | insert time |
Other control keys may be reserved in future versions of the program.
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:
| Label | A text label. |
| Group box | A rectangle with an optional heading. Useful for grouping separate sets of option buttons together. |
| Command button | A button that can be pressed. Useful for activating a macro. |
| Check box | A 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 button | Usually 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 box | Displays a list of items from a range in the spreadsheet. It can return the number of the selected item to a linked cell. |
| Combo box | Displays 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 bar | A 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 control | A vertical spin control. It can return the current value to a linked cell. |
The following drawing object types are supported:
| Line | A line or arrow. |
| Rectangle | A rectangle that can be filled. |
| Oval | An oval that can be filled. |
| Text box | A rectangle that can contain text. |
All of the objects can have a macro attached that will be run when the object is clicked on.