EXCEL 2
FORMULAS & FUNCTIONS
Input
A collection of information
Data typed into the
spreadsheet
Output
Worksheet Results
Three types of information
can be typed into a
spreadsheet cell
Labels
Values
Formulas
Labels
Text
entries
Identify
values in columns and rows
Left-aligned
Type
an apostrophe () before a
number to treat the number like a
label.
Examples:
2007
402-6900
Values
Numbers
To
be used in calculations
Right-aligned
####
size the cell larger
#### appears if the number is too
larger for the size of the cell
Formulas
Used
to perform calculations
Begin
with = sign
Type
the cell address that contain
the values you want to calculate
Examples of formulas:
=A5+A6+A7+A8
The values in cells A5, A6, A7 and A8 are
added together
Arithmetic Operators
Symbols
that direct Excel to
perform mathematical
Arithmeti
Definition
Exampl
Meaning
calculations
c
e of
Operator
usage
Addition
=B3+C3
Add the value in B3 and
the value in C3
Subtract
=F12-22
Subtract 22 from the
contents of cell F12
Multiplication =A3*B3
Multiply the value in cell A3
by the value in cell B3
Division
=C3/C6
Divide the contents of cell
C3 by the contents of cell
C6
Exponentiati
on
=C12^6
Raise the value in cell C12
to the sixth power
Order of Operation
The
order in which calculations in a
formula are performed
Excel
follows the same order of
operations that you use in Algebra
Moving
from left to right in formulas,
the order of operations is as follows:
1.
2.
3.
4.
Parenthesis
Exponents
Multiplication and division
Addition and subtraction
Cell References
Always
use cell references in formulas.
A cell reference is the column letter
and the row number (ex. B2)
Why?
By using cell references in the formula,
you can use the powerful recalculation
feature in Excel
If
you change the contents of a cell
that is included in a formula, the
worksheet will automatically
recalculate it
Functions
Built in formulas
Formulas and Functions begin with
Use the Formulas Menu, fx on the
formulas bar, or AUTOSUM arrow
Example
of a function:
=AVERAGE(B13:D13)
Use a range of cells (B13:D13)
Colon means Excel will average cells B13
through D13
Common Functions
SUM
Calculates the sum of a
range of cells
MAX
Displays the largest value
in a range of cells
MIN
Displays the smallest
value in a range of cells
COUNT
Calculates the number of
values in a range of cells
AVERAGE
Calculates the average of
values in a range of cells
AUTOSUM
Build
in sum function
Most commonly used function
AUTOSUM adds the values above
the active cell first (default)
If no values are above the cell, it
sums to the left of the active cell
If Excel doesnt select the correct
range, you may select the range
you want
Cell Range
Two
or more cells
A group of adjacent cells
(B3:C12) includes all of the cells
from B3 through C12
Ranges can be named
Cell References
Relative
copied
adjusts to its new location when
Absolute
Do not change when moved or
copied to a new cell
Mixed
contains both relative and absolute
references
Symbol
used to make an absolute cell
reference? $
F4
key will automatically put in a $ in your
formula
Printing Formulas
Print
formulas using
Ctrl + ` (the ` is found next to the
#1 on the keyboard)
Print
to Fit on 1 page
Formatting
will be lost when
printing formula page
EXCEL 2
FORMULAS & FUNCTIONS
