Transcript Functions & Formulas

Lecture
Excel: Formulas and Functions
Formulas
• Specifies calculations to be performed
• Begins with an equal sign (=)
• Can refer to:
– cells by reference (A1)
– values (25%, 100, 0.50)
• Recalculates when values change
• Example: = A1 + B2
• Without the equal sign, it's just a label
Creating Formulas
= C9 times D9 times E9
= 450
Value
(calculated results)
Algebraic Order of Operations
•
•
•
•
•
•
Parentheses
Exponentiation
Multiplication
Division
Addition
Subtraction
()
^
*
/
+
-
(5+3)/2
5^2
5*2
5/2
5+2
5-2
4
25
10
2.5
7
3
Order of Operation
“Please Excuse My Dear Aunt Sally”
•
•
•
•
Parentheses
Exponents
Multiply and Divide
Add and Subtract
=10 + 2 * 3
=(10 + 2) * 3
=16
=36
Functions
• Built-in ways to manipulate data to
get an answer (sum, average, etc)
• Give it numbers (or text) and get an
answer
• Values you provide the function are
called "arguments"
• Parentheses enclose the argument list
– Cells or other expressions needed for the
calculations
Function Syntax
• Syntax of Excel Function
= FUNCTION(argument)
• Functions can be part of an expression
= FUNCTION(arg) * 75%
Ranges and Arguments
• Some functions take ranges
=SUM(A1:A10)
=MAX(A1:D50)
• Some functions take multiple
arguments
=ROUND(A1,2)
(rounds A1 to 2 decimal places)
Function Examples
=SUM(D14:D18)
=MAX(B6:B13)
=AVERAGE(A1:A8)
Function Name
Argument
Common Functions
• =SUM(cell range)
– Total of all cells listed as arguments
• =MIN(cell range)
– Smallest number within the cell range
named
• =AVERAGE(cell range)
– Total of all the cells divided by the total
number of cells listed
Function Syntax (cont)
• Functions can be combined
= FUNCTION(arg) + FUNCTION(arg)
• Functions can be nested
= FUNCTION(FUNCTION(arg))
List of Common Functions
• =AVERAGE( )
• =SUM ( )
• =MIN ( )
• =MAX ( )
• =NOW ( )
• =TODAY ( )
•
•
•
•
=COUNT ( )
=COUNTA ( )
=COUNTIF ( )
=COUNTBLANK ( )
• =ROUND ( )
• =ROUNDUP ( )
• =ROUNDDOWN ( )
Shortcut Functions/More
• Shortcut functions from Sum function
drop-down button
• More Functions by category
–
–
–
–
–
Statistical
Math & Trig
Financial
Database
etc
Relative and Absolute Addressing
• relative address
• absolute address
B4
$B$4
Anchors row or
column
Absolute References!!!
=B2*$B$8
Quick Way to Switch
• Type formula in with relative
addresses (=B4 * E5)
• Place cursor at reference you want
made absolute
• Press F4:
B4  $B$4  B$4  $B4  B4
• Mac users: Apple+t