Microsoft Office Tutorials and References

In Depth Information

**Using Excel Functions**

Chapter 9

Working with Formulas and Functions

For example, suppose in cell C1 you want to find

out if the value in cell B5 is greater than the value

in cell B6. If B5 is larger than B6 (true), you want

to enter “Yes” in cell C1; if B5 is not larger than B6

(false), you want the answer “No” in cell C1. You

would enter the function as =IF(B5>B6,“Yes”,“No”).

Look at the steps needed to create an IF function

and its arguments:

In the example you see in Figure 9-16, the IF

function checks whether the total project days is less

than the goal of 125 days. If it is, Yes! is displayed;

if not, No is displayed.

1.
Type =IF( in the cell in which you want to

display the answer. Excel will immediately

identify the entry as a function and display a

tip box with the function syntax.

Figure 9-16

Using the IF function.

2.
Type the first argument including an

operator. This is the condition you want Excel to

evaluate.

Looking Up with Lookup Functions

The Lookup category contains functions designed

to save you time by finding related data. If you

work with large lists in Excel, you can use lookup

functions to retrieve individual records from those

lists quickly. Two commonly used lookup functions

are the VLOOKUP and the HLOOKUP.

3.
Type a comma to begin the second argument

and then type the result you want if the

evaluation is true. The true result can be a value,

a calculation, or text.

The V stands for vertical and the H stands for

horizontal. You use VLOOKUP when you need to search

through columns of information, and you use

HLOOKUP when you need to search through rows

of information.

Enclose Text in Quotes

If you want the result to be text, the result’s

argument must be enclosed in quotation

marks. The quotation marks will not be

displayed in the answer. No quotation marks are

needed if the result is numeric.

The VLOOKUP actual syntax is VLOOKUP(
lookup_

value,table_array,col_index_num
), but let’s see if I

can translate that into something a little simpler.

To understand this formula, take a look at the table

in Figure 9-17. You’ll use it to illustrate the

VLOOKUP function.

4.
Type a comma to begin the third argument

and type the result you want if the

evaluation is false. The false result can also be a

value, a calculation, or text.

The first argument
lookup_value
is really asking for

where Excel finds the value you want to match, so

let’s rename it lookup cell. In this example, you

want to know how many calls were made to a

specific city, which you will enter in cell E3, so the first

argument is in cell E3.

5.
Type the closing parenthesis and then press

the Enter key. Excel calculates and displays

the evaluation result.