Excel
Formulas & Functions
Val Singler
Formulas
5 Elements of a
Formula
– Operators
– Cell References
– Literal Values
– Worksheet Functions
and their Arguments
– Parentheses
Operators/Precedence
Operator Precedence
Parentheses
– Override Excel’s built-in order of precedence
– Control the order of the operations
– Expressions in parentheses always get evaluated
first
Relative & Absolute References
“Relative” Addressing
– A formula adjusts “relative” to its location in the
worksheet
Absolute Cell References
– Used to prevent a formula from adjusting to its
location
Mixed Cell References
– Placement of $ determines the portion of a cell
reference that will remain constant
Relative & Absolute References
Absolute and Mixed Cell References
$A1 Always refers to Column A, row will vary
A$1 Always refers to Row 1, column will vary
$A$1 Always refers to cell A1
Press F4 to make a cell reference absolute
or mixed
Ranges
Cells, Columns, Rows, Worksheets
Why Name a Range?
– Make Navigating Lists Easier
– Use in Formulas
Rules for Naming a Range
– Names must start with a letter or an underscore
– Don’t use spaces or hyphens; use underscore or periods
– Keep Names Short
Structure of Complex Formulas
Nested Parentheses
– Parentheses within parentheses
– Excel evaluates the deepest nested expression first and then
works its way out
– Every left nested parenthesis must have a matching right
parenthesis
(((((B2)))))
Tips for Editing a Formula
Before editing a formula
– Remove the equal (=) sign from the beginning of the
formula and press Enter
– Copy the formula to another cell
Use the Insert Function dialog box as a proofreading
and reference tool
Use Range Finder to locate parts of a formula
Formula & Function Problems
Common Problems
– Incorrect type of data for function arguments or missing
arguments
– Trying to nest more than seven (7) functions
– Parentheses not correctly balanced and paired
– When referring to another worksheet or workbook in a formula
or function, make sure any names are set off with quotation
marks
– Some functions will not work properly if you enter numbers
using format code (i.e. %) -- most functions require plain
numbers
Errors
Types of Errors
– #REF!
– #VALUE
– #NAME
– #DIV/0
– #N/A
– #NUM
– #NULL
Auditing Toolbar
What’s It For?
– Double-checking results
– Troubleshooting error codes
Where is it?
– ToolsFormula AuditingShow Formula Auditing Toolbar or
– ViewToolbarsFormula Auditing
Error Checking
Find error values on a large
worksheet
– Tools Error Checking or
– Click the Error Checking
button on the Formula
Auditing toolbar
– When the error is hard to find,
click the Show Calculation
Steps button
Evaluating and auditing
complex formulas
– Tools Formula Auditing
Evaluate Formula
Watch Window
Allows you to watch how a formula changes in
response to:
– Changes made to other parts of a worksheet
– Changes made in workbooks that supply information to a
worksheet
Functions
Insert Functions Dialog
Box
– Insert Function
– Click the down arrow
next to the AutoSum
button on the Standard
toolbar
– Click the Function (fx)
button next to the
Formula Window
Add-Ins
Analysis Toolpak
Where to Find the
Add-Ins
– ToolsAdd-Ins
– If the add-ins are
available, just check the
boxes and click Okay
Everyday Functions
TRIM and CLEAN
– Data clean up
LEFT, RIGHT, MID
– Extracting strings from a cell
UPPER, LOWER, PROPER
– Change case
CONCATENATION, &
– Combining cell contents, literal values, etc.
Everyday Functions
VLOOKUP
– Help track down specific information in a table based on the
contents of a cell used for comparison
NETWORKDAYS**
– Find the total number of workdays between two dates,
excluding holidays
WORKDAYS**
– Calculates a finish date
SUMIF/COUNTIF
– Sum or count when a specified
condition is met
Bonus Function Tricks
Using Conditional Formatting and the MOD
function for alternate row shading
Measuring and Displaying Elapsed Time
Avoiding Error Displays
(when you don’t want #DIV/0 or #REF! Errors to show)
Custom Functions
When none of Excel’s 300+ built-in functions
will do, create your own!
– A custom function allows you to encapsulate a
sequence of calculations so that you can perform
those calculations with a single formula
References
Bott, Ed and Leonhard, Woody, Special Edition Using Microsoft
Office XP, Que, 2001
Dodge, Mark and Stinson, Craig, Microsoft Excel Version 2002
Inside Out, Microsoft Press, 2001**
Frye, Curtis, Excel Annoyances, O’Reilly Media Inc., 2005
Walkenbach, John, John Walkenbach’s Favorite Excel Tips &
Tricks, Wiley Publishing Inc., 2005
Walkenbach, John, Microsoft Excel 2000 Formulas, M&T Books,
1999**
**Book is available on Books 24X7 (CSC Learning Place)
