Department of Technology

TECH 394 - Tech Computer Applications

E-mail Me

Syllabus

Schedule

Handouts

Assignments

All My Webs

Dept Home

• If(Logical_Condition, Value_If_True, Value_If_False)
• And(Logical1, Logical 2, ...)
• Or(Logical1, Logical 2, ...)
• Not(Logical)

All of these fucnctions require the user to provide a logical condition - one that can be evaluated as either true or false. For instance, the statement {A2>=3}can be evaluated by looking in cell A2 and determining if the value is greater than or equal to 3. If the number 6 is stored in A2, then the condition is true, becuase 6 is greater than 3. If the number 2 is stored there, the condition is false. If cell A2 contains the text string "Hi!", the condition will be false, because Excel evaluates text to have a value = 0, and 0 <3.

The If( ) function:

If(Logical_Condition, Value_If_True, Value_If_False)

This function provides an If-Then-Else structure that is evaluted each time Excel recalculates. Each time it is evaluated, Excel considers the logical condition and returns one value if the condition is true at that time, and a different value if the condition is false. The structure as shown requires three arguments, seperated by commas. The Value_If_True can be a number, a text string, a calculation, or a function - anything that Excel can evalute and decide what to return. For example:

If(A2>=3,16,"Wow")

will yield one of two values. If cell A2 contains a number greater than or equal to 3 (or any equation or function that yields a result that large), the function returns the value 16. If A2 contains or evaluates to a number less than three, the text string "Wow!" will appear.

Note that If( ) functions can be nested to evalet a number of conditions. Say that cell B4 contains the amount of sales attributed to an employee, and her bonus this year depends on that amount. If her sales totaled at least $100,000, she earns a 3% bonus. If her sales totaled more than $250,000 she earns a 5% bonus, but if her sales are less than $100,000 she earns no bonus. The function to determine her bonus amount would be:

If(B4>250000,B4*0.05,If(B4>=100,000,B4*0.03,0))

Note two things about this example. First, there must be sufficient right parenthises at the end of the function to close all of the left paraentises used. Second, the function is evaluated from left to right. Excel will first try to determine if the contents of B4 are greater than 250000. If so, the bonus is calculated, and the second IF( ) function is ignored. As written, if B4 contained the number 167,000, this function would test to see if 167000>250000. This is false, so it skips to the second test - if 167000 >100000. This is true, so the function determines the Value_If_True: 167000*0.03 = 5010. The entire function is evaluted to yield 5010.

If the function were written with the smaller value first, it would not calculate correctly:

If(B4>100000,B4*0.03,If(B4>=250,000,B4*0.05,0))

If B4 contained the value 400000, the first test would check to see if 400000>100000. This is true, so the rest of the test is skipped, and the function returns 400000*0.03 = 12000. In fact, this employee deserves a $20,000 bonus, but the function returns an improper value. Note that this does not cause Excel to flag an error - no error occured, but the equation yields bad results.

The And( ) function

And(Logical1, Logical 2, ...)

This function returns True only if all the logaical condition listed are true, but returns False if any of the conditions are false. The function:

And(A2>3, B4>100000)

looks in two cells and evalutes both conditions, again from left to right. If A2 contains the number 2, the first condition is False, so Excel stops evaluating the function, and returns False. If A2 contains the number 6, the first condition is true, so Excel continues evaluating. If B4 contains 75000, that condition is false, and Excel returns False. If instead B4 contains 115000, the second condition is true. Since both conditions are true, Excel evalutes the And( ) condition as True.

The Or( ) function

Or(Logical1, Logical 2, ...)

This function returns True if any of the logaical condition listed are true, but returns False only if all of the conditions are false. The function:

Or(A2>3, B4>100000)

would be interpreted as follows:

A2 = 6, B4 = 75000: The first condition is true, so the Or( ) function is True.

A2 = 3, B4 = 115000: The first condition is false (3=3, it is note greater than 3), but the second condition is true, so the function is True.

A2 = 1, B4 = 25000: Both conditions are false, so the function is False.

The Not( ) function

Not(Logical_Condition)

Changes the evaluated conditon from true to false or vice versa.

Not(A2>3)

evaluates as follows:

A2 = 6: 6>3, so the condition is true, the Not( ) function returns False.

A2 = 1: 1<3, so the condition is false, the Not( ) function returns True.

Again, functions can be nested. Consider how you might create a function to determine the bonuses to award as above, but only for people with more than 3 years of service (stored in A2):

If(And(A2>3,B4>250000),B4*0.05,If(And(A2>3,B4>100000),B4*0.03,0))