2.1 Formulas

Elements of a formula

Formulas are instructions that perform calculations on the worksheet. Formulas can be very simple or extremely complex. A formula begins with an equals sign (=) followed by one or more values and functions to calculate. The values can be entered directly into the formula, but it is more effective to enter the values into cells on the worksheet and make references to those cells in the formula.

A formula can consist of five elements:

Elements of a formula

• Numerical values or text-strings (such as 1.2, or HeatLosses.
• Cell references (including named cells and ranges).
• Operators.
• Worksheet functions (e.g. SUM or AVERAGE) and their arguments.
• Parentheses to control the sequence in which expressions within a formula are evaluated.

Cell and range references

Most formulas make a reference to one or more cells by using the cell or range address or name. Cell references come in four styles; the dollar sign differentiates them:

Relative reference

The reference is fully relative. When the formula is copied, the cell reference adjusts to its new location. Example: A1

Absolute reference

The reference is fully absolute. When the formula is copied, the cell reference does not change. Example: $A$1

Row absolute reference

The reference is partially absolute. When the formula is copied, the column part adjusts, but the row part does not change. Example: A$1

Column absolute reference

The reference is partially absolute. When the formula is copied, the row part adjusts, but the column part does not change. Example: $A1

Referencing other worksheets or workbooks

References to cells and ranges do not need to appear in the same sheet as the formula. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Here is an example of a formula that uses a cell reference in a different worksheet (Sheet3): =Sheet3!A1+1

You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point like this: =[Maintenance.xls]Sheet3!A1+1

If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example: =\[Maintenance Records.xls]Sheet1í!A1+A1

If the linked workbook is closed, you must add the complete path to the workbook reference. For example: =íC:\ExcelCourse\[Maintenance Records.xls]Sheet1í!A1+A1