 # Spreadsheet Design: Logical Tests 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

Project 1: Excel Intro Project 2: Excel Basics Project 3: Format & Arrange Project 4: Groups & Formulas Project 5: Design
Analysis What If... Logical Tests  Using IF Conditional Formatting
Sharing Data Summary
Quiz
Exercises 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 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 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!"