Microsoft Office Tutorials and References

In Depth Information

**Using Other Functions**

So what’s it worth to you in spending power, right

now, to make those $100 payments each month?

Try this formula: =PV(.04/12,5*12,–100). Well,

looks like all that money is worth $5,429.91

right now. But keep in mind that you only have

to part with $100 of it each month. That thought

should keep the pain level down, along with the

thought that after five years, you’ll have earned

almost $1,200! Now that’s worth a little pain.

To create a logical test or condition, you can use

any of the following operators:

= Equal

<> Not equal

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

Using Logical Functions

Logical functions are used to display text or a

value, or to perform some calculation,
only if

some condition is true.
If the condition is not

true, some alternate text or value is displayed, or

some other calculation is performed. For

example, you could tell Excel to compare the value in

cell G10 with the value in H10, and if G10 is

greater, to perform the calculation G10*.05. If

G10 is not greater than H10, you could tell Excel

to perform the calculation H10*.05 instead. The

most common Logical function is the IF function,

which has several variants.

For example, to test whether A2 is larger than

B2, use the condition A2>B2. Now, suppose a

teacher wants to use the IF function to determine

whether a student has failed her class. Assuming

the first student’s grade is in cell C4, she could

type =IF(C4>64,“Pass”,“Fail”) in cell D4. If the

first student’s grade is 65 or greater, then the

word Pass will appear in cell D4. Otherwise, the

word Fail appears. The teacher could then copy

this formula down column D to display either

Pass or Fail for each student.

Tip

IF

The syntax for the IF function is

=IF(Condition,ActionIfTrue,ActionIfFalse). The

first argument, Condition, is a logical test, which

is essentially a comparison. If the comparison is

true, the action listed as the second argument

is taken. If the comparison is not true, the action

listed as the third argument is taken.

You can nest one IF function within

another if you want to test for multiple

conditions. Suppose for example, that in

order to pass the course, the student must

get at least a 65% average on his/her

homework, and at least 72% on the final

test. Assuming that the homework average

is in cell B4, and the final test score is in

C4, the teacher could type the following

into cell D4 to indicate whether the first

student passed or failed, and why:

=IF(B4>64,IF(C4>71,”Pass”,”Failed

Final”),”Failed Homework”).