The Decision Functions

5

The Decision Functions

Objectives

Upon completion of this chapter you will be able to: ? construct a condition using the comparison operators =, >=, >, =20%, "Good", "Poor") will achieve this. The figure below shows the syntax for a formula using the IF function.

Figure 5.1

The Decision Functions 69

A condition has the form:

Expression-1 Comparison Operator Expression-2

Expression-1 and Expression-2 are any valid Excel expressions composed of cell references, constants and functions. Example of conditions are: A1>10, A1*2 >=

50, and A1 B1 2*C1. Essentially, an expression is a formula without the equal

sign. Thus to test if cell A3 has a value of 5 the condition is A3 =5. A condition is said to be either true or false.

The comparison operators1 are:

=

equal to

>

greater than

>= greater than or equal to

<

less than

0, A20, B2>A2/2), 3 , 6) Returns the value 3 if either A2 > 0 or B2 > A2/2. If neither condition is true, the value 6 is returned.

g) =IF(NOT(A2=0), TRUE, FALSE) This is the same as IF(A2=0, FALSE, TRUE) or, simply =NOT(A2=0).

h) =IF(NOT(OR(A1=1, A2=1)), 1, 0) This is a somewhat contrived example. It returns 1 if both A1 and A2 have a value that is not 1.

IF functions may be nested. This means that within one IF function, we may use another IF function for either or both returned values. Nesting up to seven levels is permitted provided the total number of characters in the cell does not exceed 256. Remember you may use spaces in a formula to make it more readable.

a) =IF(A1>10, IF(A1>100, "Big", "Medium"), "Small") For the moment, ignore the second (the inner) IF. It is clear that when the condition A1 >10 is false then the first IF returns "Small." What happens if the condition is true? The second IF comes into play. When A1 >100, the inner IF

The Decision Functions 71

returns "Big", otherwise it returns "Medium".

b) =IF(A1>10, IF(A1>50, "Big", "Medium") IF(A1=0)*(A1=pmin, 1 ,0) D6: =IF(B6=pmin,B6 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download