Excel Labs Flashcards
Function
- Starts with =
- Predefined/pre-built formulas
- Has arguments
- Already available in Excel
- Ex: =SUM(), =ROUND(), =MIN(), =MAX()
Formula
- Expression that calculates the value of a cell
- Ex: =4+3
A1=$A$2 + B2
Nested Function
=ROUND(AVERAGE(E6:P6),-1)
Relative Addressing
- The default setting in Excel
- Copies a formula from one cell to another cell and converts the cell addresses in the formula by adjusting them to the address of the cell to which they were moved.
- Ex: C4 (cell) =C1+C2+C3
Copied to D4
D4=D1+D2+D3
Absolute Addressing
- Copies a formula from once cell to another cell but does not convert (adjust) the cell addresses in the formula to the address of the cell to which they were moved.
- Uses TWO (2) $$ Dollar signs
- Ex: C4=$C$1+$C$2+$C$3
D4=$C$1+$C$2+$C$3 - Everything with a dollar sign before it will stay the same
Mixed Addressing
- Uses ONE (1) $ Dollar Sign
- $Letter: Keeps column the same
- $Number: Keeps the row the same
- Used to create multiplication tables and other tables
- Values of the formula can be found in the initial rows and columns
- Ex: A1 = $A$2 + B2
F25 =$A$2+G26
25 + 1 = 26, B2 changes to G26 (Because B is one after A and G is one after F)
$A$2 stays the same
Cell Addressing
- Absolute address: Column and row kept constant.
- > $A$1
- Relative address: Column and row vary
- > A1
- Mixed address: Column A is kept constant, the row varies
- > $A1
- Mixed address: Column varies, the row is kept constant
- > A$1
Mixed Addressing Example
A1=$A2+B2
F25=$A26 + G26
$A stays the same, 2 changes to 26 (25 + 1) and B2 changes to G26 (A to B is one letter, F to G)
A1=A$2+B2
F25=F$2+G26
$2 stays the same, A changes to F and B2 changes to G26
Interest Rate Formula
Amount * (1 + interest rate) ^ n (number of years)
Precedence Rules (Arithmetic hierarchy)
Parenthesis and exponentiation (left to right)
Multiplication and division (left to right)
Addition and subtraction (left to right)
Use blank references when you want different formulas to refer to the same cell
Absolute
Blank references are seldom (rarely) used other than when creating tables of calculated values such as a multiplication table in which the values of the formula or function can be found in the initial rows and columns
Mixed
True or false: When you set up a worksheet, you should use cell references in formulas whenever possible, rather than constant values like (0.05)
True
In Excel, the intersection of a row and a column is known as
A cell
