Microsoft Excel 2007Objective 3: Creating and Modifying Formulas

In the Lesson you will learn how to:1. Reference data in formulas2. Summarize data using formulas3. Summarize data using subtotals4. Conditionally summarize data using a

formula5. Lookup data using a formula6. Use conditional logic in a formula7. Format or modify text using formulas, and8. Display and print formulas

Conditionally Summarize Data Using a Formula• Summarize data that meets certain conditions

Summarize Data That Meets Certain Conditions1. Verify the worksheet has a range to

search and numerical values to summarize

2. Click in a cell that will contain the conditional formula result

3. Type =, then type a conditional function using the slide following as a reference

Functions to Summarize Data Based on ConditionsConditional Function

Explanation and Practice

SUMIF Sum a set of values that meet one criteria

SUMIFS Sum a set of values that meet multiple criteria

COUNTIF Count a group of cells that meet one criteria

COUNTIFS Count a group of cells that meet multiple criteria

AVERAGEIF Average a set of values that meet one criteria

AVERAGEIFS Average a set of values that meet multiple criteria

Lookup Data Using a FormulaHLOOKUP and VLOOKUP

HLOOKUP & VLOOKUP These formulas will lookup values based

on criteria you specify Use HLOOKUP when your comparison

values are located in a row across the top of a table of data, and you want to look down a specified number of rows.

Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

To Use1. Click a cell where the formua will appear2. Click the Formulas tab then click the

Insert Function button3. In the Insert Function dialog box click

the Or select a category list arrow then click All

4. Double-click HLOOKUP or VLOOKUP from the list box and enter the necessary information

Use Conditional Logic in a FormulaCreating formulas using conditional functions

Logic FunctionsFunction

Explanation and Practice

IF Specifies a logical test to perform

AND Returns TRUE if all of its arguments are TRUE

OR Returns TRUE if any argument is TRUE

NOT Reverses the logic of its argument

IFERROR

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula