Microsoft Word 2003
Excel Formulas and
Functions
Math and more
You can use formulas to:
Solve mathematical problems
Complete statistical and financial
functions
Return numerical and text values based
on other cells
Add and format text in cells
Formula
Mathematical Equation
All formulas begin with an equal (=)
sign
Data that is stored in the worksheet and
that needs to be used in a formula is
referenced using the cells address
=A1+A2/(A3-A4)
An example of a simple
formula
Functions
A predefined formula
Formula: =A1+A2+A3+A4+A5
Function: =SUM(A1:A5)
An example of a more
complex formula
What makes a formula?
=(3*$A$2*SUM(A3:A9))^1/3
Begin with =
Constants
Cell References
Operators
Functions
Constants
Values entered directly into a formula
Simple
Accurate
Inflexible
2, 3, 12, 14.32
References
Relative (Column Row)
A1, C18, ZZ65536
Absolute ($Column$Row)
$A$1, $C$18,$ZZ$65536
Mixed (Fixed Column or Row)
$A1, C$18
Named Ranges
Int_Rate, Grade_Scale
Operators
Arithmetic
- (negative)
^ (Exponent) *
Comparison
= < > <= >= <>
Text
&
Reference
: (Colon)
_ (Space)
, (Comma)
Operator Order
Formulas are read from Left to Right
1. ( )
2. Negation
3. %
4. ^
5. * or /
6. + or
7. &
8. = < > <= >= <>
Error Messages (Cell
Reference)
#########
Column not wide enough
#VALUE!
Wrong Argument or Reference
#DIV/0!
Dividing by zero
#Name?
Excel doesnt recognize text in formula
#N/A!
Missing data or wrong value for lookup
function
#REF
Cell reference not valid (Deleted cell,
pasted over)
#NUM!
Invalid numeric value
#NULL!
Specified cells do not intersect
How to Copy Formulas?
AutoFill is a command you can use
when you want to copy the same
formula across a range of adjacent cells.
AutoFill will automatically change cell
addresses depending on where the new
formula is being copied.
An example of a
Simple
Formula
and a
Comple
x formul
a
Open Excel worksheet and
enter the data below:
SalesRep
Month
Jones Jan
100
Jones Jan
225
Rogers
Jan
400
Rogers
Jan
150
Rogers
Jan
250
Franklin
Jan
800
Franklin
Feb
200
Jones Feb
350
Franklin
Feb
1200
Rogers
Feb
900
Franklin
Feb
750
Jones Feb
800
Sum:
Average:
Count:
Amount
Add values
Within a certain range:
=SUM(C2:C13)
For an entire range:
=SUM(C:C)
column.
Note: Insert this function in another
Count values
Count values in a range and it the entire
range:
=COUNT(C2:C13)
=COUNT(C:C)
Conditional Counting:
=COUNTIF(A2:A13, Jones) or
=COUNTIF(A2:A13, =Jones) equal
=COUNTIF(A2:A13, <> Jones) not equal
Cont
Count the Number of Sales Greater than a
Target Value:
=COUNTIF(C2:C13, >&D1)
Note: the concatenation operator (&) is used to
join the grater than symbol (>) with the cell
reference.
Count the Number of Sales Greater than the
Sales Average:
=COUNTIF(C2:C13, >&AVERAGE(C2:C13))
Conditional Summing
Formula that adds all the sales in January:
=SUMIF(B2:B13, =Jan, C2:C13)
Add up sales made by other sales
representative:
=SUMIF(A2:A13, <> Rogers, C2:C13)
Add upRange
the sales greaterSum
than
a certain
range
Criteria
value:
=SUMIF(C2:C13, >500,C2:C13)
Using the IF Function
Display a value that depends on criteria you
set
Returns a value if one condition is true and
returns another value if the condition is false
=IF(logical_test,value_if_true,value_if_fa
lse)
PMT function
Function used to calculate a loan
payment amount using principal,
interest rate and number of payment
periods.
=PMT(rate, nper,pv)
=PMT( .09/12,4*12,24000)
Calculating time period
Each day, month, and year is given a
particular numerical value or serial number by
your computer.
Your computer will work from 1900 date
system or 1904 date system
1900 date system: Jan 1, 1900 Dec 31, 9999
1904 date system: Jan 2, 1904 Dec31, 9999
To check go to Tools Options Calculation
