02 Formulas & Functions Anatomy.xlsx

Introducing Formulas and Functions 1. Operators 2. Cell Refereces 3. Name Ranges 4. Excel Errors

Back

Operators Used in Formulas

Operator Precedence in Excel Formulas

Please Excuse My Dear Aunt Sally Parentheses e.g. [ ], { }, ( ) Exponents Multiplication, Division (Left to Right) Addition, Subtratction (Left to Right)

BODMAS

Brackets Orders Division, Multiplication (Left Addition, Subtratction (Left t

ulas

Formulas

BODMAS

ets s on, Multiplication (Left to Right) on, Subtratction (Left to Right)

Back

Using Relative, Absolute, without and Mixe Relative Cell References Item

Chair Desk Lamp

Quantity

Price

Total

$0.00 $0.00 $0.00

Absolute Cell References Value

Num

Without Cell Refere

Price

0 0 0

• Relative: The row and column references can change when you copy the formula to another c current row and column. By default, Excel creates relative cell references in formulas.

• Absolute: The row and column references don’t change when you copy the formula because t reference uses two dollar signs in its address: one for the column letter and one for the row num

• Mixed: Either the row or column reference is relative, and the other is absolute. Only one of th

ute, without and Mixed References Without Cell References Value

A

Mixed References

Answer 1 1.5 2 2.5

1

1.5

2

2.5

1 1.5 2 2.5

1.5 2.25 3 3.75

2 3 4 5

2.5 3.75 5 6.25

y the formula to another cell because the references are actually offsets from the ces in formulas.

opy the formula because the reference is to an actual cell address. An absolute er and one for the row number (for example, $A$5). is absolute. Only one of the address parts is absolute (for example, $A4 or A$4).

Back

Catering Invoice

Menu Item Tamales: Carnitas Tamales: Vegetable Empanadas: Nutella & Banana

Price Quantity Total $2.29 20 $45.80 $2.29 30 $68.70 $3.99 40 $159.60 Tax Total

Back

#DIV/0!

#NAME?

#N/A

#REF!

Err:502 Err:502

=LEFT(A2,-1)

Err:502

3 4 1 2 3

If the number of characters specified is negative, it returns an error