Skip to content Skip to navigation Skip to collection information

Connexions

You are here: Home » Content » Engineering Computation with Spreadsheets » Formulas

Navigation

Recently Viewed

This feature requires Javascript to be enabled.
 

Formulas

Module by: Serhat Beyenir. E-mail the author

Summary: Elements of a Formula

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.

Tip:

To display the syntax of all formulas in a sheet: Press "Ctrl+ë" (the ë symbol is located to the left of the number 1 on the keyboard).

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:

Definition 1: Relative reference
The reference is fully relative. When the formula is copied, the cell reference adjusts to its new location. Example: A1
Definition 2: Absolute reference
The reference is fully absolute. When the formula is copied, the cell reference does not change. Example: $A$1
Definition 3: 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
Definition 4: 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

Tip:

The "F4" keyboard shortcut has four states:
  • Absolute reference to the column and row, =$A$1
  • Relative reference (column) and Absolute reference (row), =A$1
  • Absolute reference (column) and Relative reference (row), =$A1
  • Relative reference to the column and row, =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

Collection Navigation

Content actions

Download:

Collection as:

PDF | EPUB (?)

What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

Downloading to a reading device

For detailed instructions on how to download this content's EPUB to your specific device, click the "(?)" link.

| More downloads ...

Module as:

PDF | EPUB (?)

What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

Downloading to a reading device

For detailed instructions on how to download this content's EPUB to your specific device, click the "(?)" link.

| More downloads ...

Add:

Collection to:

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks

Module to:

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks