Spreadsheet Design:
Logical Tests

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

Some functions do not calculate values but instead do logical tests using logical comparisons like =, < , and > or the combinations <=, >=, <>. Such a test allows you to do one thing when the comparison is TRUE and something different when it is FALSE.

 The IF function is the logical test that is used the most. It has three arguments inside parentheses which are separated by commas:

  • the comparison statement

  • the cell value to use when the comparison is true

  • the cell value to use when the comparison is false.

The general form of an IF function is -

=IF(logical comparison, value if TRUE, value if FALSE)

A value can be a number, text within double quotes, a cell reference, a formula, or another logical test.


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: Design
    AnalysisTo subtopics
    What If...To subtopics
    Logical Tests To subtopics
    FootprintUsing IF
    FootprintConditional Formatting
    Sharing DataTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Search 
Glossary  
Appendix

Below is a table showing each logical operator, its meaning, and an example of an IF statement using the operator. You can use other functions as part of the test or values, including another IF statement. Yes, it can get quite complicated!

Logical Operator

Meaning

Example

=

Equal to

=IF(E8=C8,"Equal","Not equal")

When the two cells are equal, the word "Equal" is shown. When the two cells are not equal, the phrase "Not equal" shows.

<

Less than

=IF(F4<E4,E4-F4, F4-E4)

If F4 is less than E4, subtract F4 from E4. Otherwise do the subtraction the other way. This makes sure you have a positive number for the difference of the two numbers.

>

Greater than

=IF(C6>100,C6,100)

If C6 is greater than 100, show C6. Otherwise show 100.

<=

Less than or equal to

=IF(B5<=10,B5,"Maximum")

If B5 is less than or equal to 10, show B5. Otherwise show the word "Maximum".

>=

Greater than or equal to

=IF(MAX(B4:E8)>=SUM(B4:E8)/2,MAX(B4:E8), SUM(B4:E8)/2)

If the largest value in the range is larger than or equal to half of the sum of the range, then show the largest value. Otherwise show half the sum of the range. (These statements can get really messy looking!)

<>

Not equal to

=IF(B8<>D6,IF(B8<10,10,B8),D6)

If B8 is not equal to D6, check to see if B8 is less than 10. Show 10 if it is and B8 if it isn't. Otherwise show D6, which would be equal to B8 in this case.

Nesting

You can nest up to 7 If statements to create complex tests. For example, to show a letter grade in the cell beside the cell AverageScore, you could use 4 nested IF statements in a single formula:

=IF(AverageScore>89,"A",IF(AverageScore>79,"B",IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))

Instead of writing complicated expressions inside an IF statement, you can do each calculation in a separate cell and use only the cell references in the IF statement. Of course, if you do that, looking at the IF formula will not tell you much about what is really going on. Naming the cells would be useful in this kind of situation.

Conditional Formatting

New to 97 Starting with Excel 97, you can create conditional formatting, which uses a logical test to apply one format for a cell when the test is true and a different format when it is false. For example, you could format positive amounts with a green cell fill and negative amounts with a red fill. Or when a certain condition is met, you could show text instead of a number, like
"Over budget!"