Settings International Punctuation.Rules of mathematics and syntax are applied when you enter or modify formulas and functions in spreadsheets. Using arguments, dates, and arrays in functions allow you to extend your calculations. As well, setting cell names and addresses simplifies entering formula and function expressions.
When you create formulas that reference cells you can refer to the cells by their cell address. This address is the intersection of the column letter and the row number, such as A15 or B6. When referring to a cell on a different spreadsheet, first specify the name of the spreadsheet; for example, C:A15. You can also specify a block of cells in this way by creating a cell address range, such as A5..D16.
When you reference data in formulas, functions, or macros, you can refer to the cell by relative cell address, absolute cell address, or cell name.
When you set a relative cell address, the formula calculates the data from cells relative to its current location in the spreadsheet. When you change the address of a cell that contains a formula, the formula calculates the address relative to its new location. For example, if you create a formula in cell A3 to add cells A1 and A2, and then you move the formula to cell D3, the formula adjusts to add cells D1 and D2.
By default, cell formulas calculate relative cell addresses.
When you set an absolute cell address in a formula, the formula always references the same cells regardless of their location on the spreadsheet. For example, if you create a formula in cell A3 to add cells A1 and A2, and then you move the formula to cell D3 on the same spreadsheet, the formula still calculates cells A1 and A2.
You can set an absolute address by typing a dollar sign before the part of the cell address you want to make absolute. For example,
typing $A$1 makes both coordinates of address A1 absolute typing $A1 locks the address into column A, but lets the row coordinate change typing A$1 locks the address into row 1, but lets the column coordinate change typing $A:A$1 locks the address into sheet A and row 1, but lets the column coordinate changeIn Edit mode, press F4 when a cell address is highlighted in the input line to make a cell absolute. Press F4 repeatedly to cycle through the eight absolute combinations.
Cell reference starting referenceYou can also type the cell address directly into the formula cell. Depending on the cell selection you want to name, you must type the selection in a specific way.
To specify Range of cells Address of top-left cell, followed by one or two periods and the address of the bottom right cellExample: C3..D6 (refers to cells C3, D3, C4, D4, C5, D5, C6, and D6) F4.F11 (refers to cells F4 to F11)
Cells on another sheet Sheet name and a colon, then the cell reference Example: B:B4 Cell in a different notebook Filename in brackets, the sheet name, then the cell reference Example: [SALES]C:B5 Named cells Example: EXPENSES Cells in different places Separate each selection with a comma Example: A2..A5,B7,D5..E12 3D selection The sheet references first, followed by the cell coordinates Example: A..D:A2..B5 (refers to cells on sheets A, B, C, and D)Note that you can enter the addresses of any two cells in opposite corners in any order. The coordinates are rewritten to list the top-left cell followed by the bottom-right cell. As well, if you rename a sheet, you can use the new name on its tab in the cell reference or the sheet letter. And, if you group sheets, you can refer to them by their group name instead of by their individual sheet names.
The result of a formula depends on the order in which arithmetic operations are performed. Each operator has a precedence, and the formula performs the operations in order of precedence. Operations with equal precedence are performed from left to right. For example, multiplication has higher precedence than addition, therefore the equation +5 + 1 * 3 equals 8, not 18.
The following table lists operators and the precedence assigned to each. Operators with the highest precedence (7) are performed first.
Precedence Parentheses ( ) Negative, positive (-, +) Multiplication, division (*, /) Subtraction, addition (-, +) Greater than or equal (>=) Less than or equal (<=) Less than, greater than (<, >) Equal, not equal (=, <>) Logical NOT (#NOT#) Logical AND (#AND#) Logical OR (#OR#) Text operator (&)You can override operator precedence using parentheses. Enclose in parentheses the part of a formula to calculate first. When parentheses are nested inside other parentheses, the innermost part is calculated first.
+4 * 2 + 3 = 11 +4 * (2 + 3) = 20 (4 * 2) + (3 + 5) * 4 = 40 ((4 * 2) + (3 + 5)) * 4 = 64When moving formulas and referenced cells, you should be aware of the following rules:
If you move both a formula and the cells it references, the references do adjust. For example, if cell A2 contains the formula +A1+1, and you move A2 to B1, the formula still reads +A1+1. But if you move both A1 and A2, the formula reads +B1+1.
If you move a formula to another notebook without moving the cells it references, the two notebooks link so that the formula still references the cells in the original notebook. If you move the formula back to the first notebook, the links are removed.
If you move a cell without moving formulas that refer to it, formulas update to refer to the cell’s new location, even if you specified the reference as absolute. For example, if you move A:B4 into A:B6 of a notebook named TAX, the formula’s reference changes to [TAX]A:B6.
If you move a cell from within a range without moving the formulas that refer to the cell, the formulas no longer refer to the moved cell. However, if you move one of the coordinate cells (the upper-left and lower-right cells), the references to the cells expand or contract to reflect the new location. For example, if you define BILLS as cells B1..B7, and then move cell B7 to B12, BILLS changes to B1..B12.
If you move a coordinate cell into another notebook, references to the cell are not adjusted.If you move an entire named or referenced selection of cells, the cell name or the reference in affected formulas is updated.
If you move data into a coordinate cell of a referenced area or into a single referenced cell, the formula containing the reference becomes invalid. References in formulas to the single cell or selection of cells are replaced with ERR, and cells that contain those formulas display ERR. If you name a selection of these cells, the coordinates in the cell names list display as ERR.
Spreadsheet functions contain:
the name of the spreadsheet function (such as @SUM or @AVG) arguments (the values, cells, or text strings on which operations are performed) commas that separate multiple arguments parentheses around the argumentsA spreadsheet function must follow these syntax rules:
A leading at sign (@), plus sign (+), or equal sign (=) must be typed at the beginning of the formula.
The @function can be typed in either uppercase or lowercase letters.Multiple arguments must be separated with a semicolon or the argument separator specified by clicking Tools Settings International Punctuation.
Optional arguments must be typed within angle brackets, <>. If you specify an optional argument, you must also specify all preceding optional arguments.
A space must not be typed between the at sign (@) and the function name.Some spreadsheet functions accept a combination or choice of types for a single argument. For example, @SUM accepts cells in combination with numeric values:
@SUM(B10..C25,50) totals numeric entries in cells B10..C25 plus 50.
You can separate arguments with a semi-colon, or you can specify a setting for separating arguments in Tools Settings International Punctuation. In the example above, a comma separates arguments. If the message “Not enough arguments” appears after entering a spreadsheet function, use a semi-colon between arguments.
A numeric value can be used as a spreadsheet function argument in any of these for
ms:
