Section 3: References & Calculations

Makiel [Muh-Keel]

Last edited 490 days ago by Makiel [Muh-Keel].

3.2 Summary Statistics Functions

3.3 Financial Functions

3.4 Formula Auditing

Cells, References, and Calculations

Each worksheet in Excel is divided into cells that each have their own address. The address is the column letter and row number that corresponds to that particular cell.

When you click on the top left cell in a worksheet, you select cell "A1." Once a cell is selected, you can change what is stored as the value for that cell.

A cell’s value can be a specific number, a set of letters, or it can even reference other cells in the worksheet.

To perform calculations in Excel, we often reference the values stored in other cells in our worksheets. Consider the worksheet depicted in

Figure 3.1⁠

; the numbers 8 and 2 are stored in cells A1 and A2, respectively.

The sum of these two numbers is calculated using a formula in cell A3 by referencing both cells (A1 and A2).

The result of the formula "=A1+A2" is now stored as a value (in this case the number 10) in cell A3 that itself can be referenced by other cells.

⁠

Formulas

Formulas can also be reused if the same calculation is performed in a different cell location.

⁠

You can also copy+paste the calculation into other cells in the worksheet, causing the calculation to “transfer” over to any different cell. The different cell will reference the root cell that contains the syntax for the calculation.

⁠

As seen in the image above, Excel is smart enough to reuse the formula used in cell B6, into C6 after copying and pasting the calculation.

⁠

Warning: As seen in the image above, not using the $ sign to anchor one of the cells in your calculation will cause Excel to increment or move up, one cell.

A cell reference that remains constant when a formula is pasted into a new cell is called an absolute cell reference.

You’re gonna have to use a combination of relative cells and $ signs sometimes. For example, in the image above, the calculation C3+C1 is used to add the Exam Bonus to the exam score.

But there’s an issue, every time the calculation is done to a cell, it moves down the column to the next sequential cell.

By using the ‘$’ sign here > C3+C$1 , its constantly referring back to the Exam bonus cell, C1.

The relative reference to the exam score (cell C4) is automatically updated while the absolute reference to the bonus points remains constant.

Whatever character the $ is in front of will cause that character to become a constant.

In the case below, the 1 becomes the constant across the sheet.

⁠

The same goes for the letter character in this image.

⁠

Cells, References, and Calculations

Formulas

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.