Excel Formulas and Functions For Dummies

From Excel Formulas and Functions For Dummies by Ken Bluttman, Peter G. Aitken

Knowing Excel 2007's common, text, and array functions — along with keyboard shortcuts — will help you create formulas to easily manage your data. Writing formulas in Excel to produce accurate data means using the right operators, correct order of operations, and understanding references. Excel sends an error message if you run into a problem with a formula or function.

Common Functions of Excel

Microsoft Excel's general functions apply to all needs and are used by everyone. Here is a quick guide to common Excel functions and their purposes:

Function	Description
SUM	Calculates the sum of a group of values
AVERAGE	Calculates the mean of a group of values
COUNT	Counts the number of cells in a range that contain numbers
INT	Removes the decimal portion of a number, leaving just the integer portion
ROUND	Rounds a number to a specified number of decimal places or digit positions
IF	Tests for a true or false condition and then returns one value or another
NOW	Returns the system date and time
TODAY	Returns the system date, without the time
SUMIF	Calculates a sum from a group of values, but just of values that are included because a condition is met
COUNTIF	Counts the number of cells in a range that matched a criteria

Excel Text Functions

Text functions are mighty handy when you’re working with names, customer lists, addresses, or other data that is text based. Here is a list of selected Excel text functions and their purposes:

Function	What it does
LEFT	Extract one or more characters from the left side of a text string
RIGHT	Extract one or more characters from the right side of a text string
MID	Extract characters from the middle of a text string. You specify which character position to start from, and how many characters to include
CONCATENATE	Assemble two or more text strings into one
REPLACE	Replace part of a text string with other text
LOWER	Convert a text string to all lower case
UPPER	Convert a text string to all upper case
PROPER	Convert a text string to proper case
LEN	Returns the length (number of characters) of a text string

Order of Operations in Excel

Knowing the order that mathematical operations are performed (operator precedence) is key to writing formulas and getting correct results in Excel. Use the catch phrase, “Please excuse my dear Aunt Sally,” to remember the order of operations:

Parentheses
Exponents
Multiplication and Division
Addition and Subtraction

Excel Logic and Comparison Operators

Operators in Excel are symbols used to indicate the type of computation to occur between two (or more) values in a formula. This list shows the character of the operator (logical or comparison) and the function it performs:

Operator Syntax	Comment
=	Equals
>	Greater than
<	Less Than
<>	Not equal to
>=	Greater than or equal to
<=	Less than or equal to
AND	If all conditions are true, then true is returned, else false is returned
OR	If at least one condition is true, then true is returned, else false is returned

Understanding References in Excel

Some reference functions in Excel let you address cells, or groups of cells, absolutely or relatively. Specifying how the address is applied is done by preceding the row and/or column designators with a dollar sign ($):

Example	Comment
=A1	Complete relative reference
=$A1	The column is absolute, the row is relative
=A$1	The column is relative, the row is absolute
=$A$1	Complete absolute reference

Error Messages in Excel

When you’re having a hard time resolving a formula or function in Excel, a handful of errors can appear in a cell. Here are the types of errors and their meaning mean so you can correct the problem:

Error	Meaning
#DIV/0!	Trying to divide by 0
#N/A!	A formula or a function inside of a formula cannot find the referenced data.
#NAME?	Text in the formula is not recognized.
#NULL!	A space was used instead of a comma in formulas that reference multiple ranges. A comma is necessary to separate range references.
#NUM!	A formula has numeric data that is not valid for the type of operation.
#REF!	A reference is not valid.
#VALUE!	The wrong type of operand or function argument is used.

Excel Formulas and Functions Keyboard Shortcuts

Knowing Excel keyboard shortcuts and their actions will help you work quickly and efficiently with formulas and functions in the Microsoft spreadsheet program. Use these Excel keyboard shortcuts to perform specific tasks:

Press this . . .	To do this . . .
Ctrl + X	Cut the active cell or range to the clipboard
Ctrl + C	Copy the active cell or range to the clipboard
Ctrl + V	Paste from the clipboard
Ctrl + B	Apply bolding to or remove bolding from the active cell or range
Ctrl + U	Apply underlining to or remove underlining from the active cell or range
Ctrl + I	Applying italics to or remove italics from the active cell or range
Ctrl + Y	Redo the last action
Ctrl + Z	Undo the last action
Ctrl + 1	Display the Format Cells dialog box
F1	Display Help
F2	Edit a cell’s contents
Esc	End editing a cell’s contents
F4	Toggles relative, absolute, and mixed addressing for a cell reference while editing a formula
Ctrl + Shift + Enter	Turns a formula or function into an array formula or function

Array Functions in Excel

Excel array functions, like FREQUENCY, TREND and TRANSPOSE accept arrays as arguments and possibly return arrays of data. Remember these tips when using Excel array functions:

A range must first be selected. The size of the range is usually dependent on the size of one of the ranges used as an argument.
Enter the function once.
Complete the entry with the Ctrl + Shift + Enter.