Teachers.Net
JoinLog in
Teachers.Net

Advanced Excel

By john Caulfield

  • Computer
  • Advanced
Activity Time
14 hours
Materials
Excel any Version
Concepts / Topics
Condirional Logic

An article explains Excel conditional logic through spreadsheet examples using IF, AND, OR, NOT, and nested IF formulas.

The lesson is available to download in Word from the website mentioned below.

Conditional Logic Statements in Excel

It is impossible to make much progress in Microsoft Excel without understanding IF statements. Imagine you want two different outcomes to a calculation, depending on whether a condition is met.

Set up the example

Assume the spreadsheet contains the following headings and values. For the purpose of this article, case is not significant.

RowA: NameB: SalaryC: AreaD: GradeE: Absence
2John150,000New YorkDirector0

A simple IF statement

If the following calculation were typed in cell F1:

=IF(B2>50000,"RICH","POOR")

Then John would join the rich club. Note the quotation marks, since the true and false outcomes are text. This is called a “simple IF,” since there are two possible outcomes.

In F1, type Donations. Let us assume for now that the rich can afford to pay more than the poor. In G2, type:

=IF(C2="RICH",B2*10%,20)

This calculation assumes that the rich are happy to pay 10% of their income (a calculation), whilst the poor just pay 20 (a value).

It is still a simple IF because there are just two outcomes based upon a single condition. The outcomes can be either a constant or a calculation.

Combine conditions with AND and OR

The condition can be more complex. Imagine there are two possible moods, happy or unhappy, but three requirements to make you happy:

  1. Money: more than 100,000.
  2. Location: New York.
  3. Grade: Director.

The function to achieve this is:

=AND(condition1, condition2, condition3)

The lesson describes support for up to 30 conditions. All conditions must be true for the function to return TRUE; otherwise, it returns FALSE.

=AND(B2>100000,D2="NEW YORK",C2="DIRECTOR")

The intended result in this example is TRUE because you are earning the right salary, you are in New York, and you are a director.

If OR were substituted for AND, only one condition would need to be true for the function to return TRUE:

=OR(B2>100000,D2="NEW YORK",C2="DIRECTOR")

Nest logical functions inside IF

However, TRUE and FALSE are not particularly friendly outcomes. An improvement would be to nest (enclose) AND in a conditional logic function:

=IF(AND(B2>100000,D2="NEW YORK",C2="DIRECTOR"),"HAPPY","NOT HAPPY")

Imagine you are in New York and get demoted to manager. The function as it currently stands would say you are unhappy. But after drowning your sorrows with a few Long Island iced teas, you decide that since you still have the money and the location, you are still happy.

What could we do? Why not nest an OR statement inside the AND?

=IF(AND(B2>100000,D2="NEW YORK",OR(C2="DIRECTOR",C2="MANAGER")),"HAPPY","NOT HAPPY")

Exclude a condition with NOT

In G1, type Swimming, and in H2, type heated, assuming you like to swim in a heated swimming pool. I don’t know about you, but the prospect of swimming in the river would not make me very happy.

How could we adjust the calculation?

=IF(AND(B2>100000,D2="NEW YORK",OR(C2="DIRECTOR",C2="MANAGER"),NOT(H2="RIVER")),"HAPPY","NOT HAPPY")

In other words, to be happy you must earn more than 100,000, be in New York, work as either a director or a manager, and not be swimming in the river.

Nest IF statements for more outcomes

However, even this is too simplistic. What if you were capable of more than two moods based on your salary? It is possible to nest IF statements inside each other:

=IF(B2>50000,IF(B2>100000,"HAPPY","CONTENT"),"UNHAPPY")

The lesson gives Excel’s limit as seven conditions, allowing eight possible outcomes.

This leaves one other level of complexity: what if you had more than eight possible moods? This forms part of the next lesson on using lookup and reference functions in Microsoft Excel.

Teachers.Net · https://teachers.net/lessons/3120.html