Excel offers several logical functions for your help in developing your spreadsheet projects. We find the IF function to be one of the more useful functions in the kinds of projects we develop. The typical use of the IF function will follow the general format of:
=if(relationship,true,false)
| IF Function | Meaning: Is it true that... |
| =if(a1 = b1, true, false) | the contents of Cell a1 equal the contents of cell b1? |
| =if(b6 > 500, true, false) | the contents of Cell b6 is greater than 500? |
| =if(K6 = "Washington", true, false) | the contents of Cell K6 is the same as the text Washington? |
In each of these examples, you could look at the cells in the spreadsheet and determine whether each statement was true or false. The logic of the IF function is to ask Excel to analyze the relationship statement. If the statement is true, display one value. If the relationship is false, return the other.
In particular, the function =IF(A3<450, 5, 12) located in Cell B3 would direct Excel to look at the value in cell A3. If that number is less than 450, then Excel would display the value 5 in Cell B3. If that number is not less than 450, then Excel would display the value 12 in Cell B3.
The contents of Cell B3 is conditional. It depends on what value is in Cell A3.
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||
| In this case, Cell A3 is NOT less than 450. Cell B3 should contain 12 | In this case Cell A3 IS less than 450. Cell B3 should contain the value 5 |
The ability to cause Excel to dynamically change the contents of a cell based on some data entry in a cell is a powerful feature.
Before we look at some specific examples of the use of the IF function, let's explore some of the options you have for establishing a relationship.
| Relationship |
Meaning |
<
or > |
Less
Than, Greater Than |
= |
Equal |
<> |
Not
Equal |
<=
or >= |
Less
than or Equal, Greater Than or Equal |
Let's look at some of these functions in a spreadsheet. Column C contains the function and Column D contains the results.
Comment:
-
Row 2: The values in Cell A and Cell B are the same (true) so the function returns 45
-
Row 3: The values in Cell A and Cell B are not the same (false) so the function returns Not OK
-
Row 4: The value in Cell A4 is greater than 5000 (true) so the function returns Over
-
Row 5: -6 is less than -1 (true) so the function returns the value in Cell A5
Points to remember about the IF function format
- Text may be used, but should be inside quotation marks
- Arithmetic may be used in the IF function
- A response of "" (quotations with nothing inside) will return a blank cell
Let's take a look at a few examples to give you an idea of some ways to use the IF function:
1. Checking a balance
An
accounting project requires that a sum of numbers not exceed 200.
By entering a 'check' IF function in an adjacent cell, the flag of TOO Much will appear if some adjustments have to be made. If the Sum is within budget, the IF function will return a blank cell and there are no flags.
2. Checking a data entry
You would like to prompt the person entering data if they have made an error. The two codes will check if the Area code is 1 or 2-digits (Too Small) or 4 or more (Too large). If the data entered is 3 digits, the cells will be blank.
3. Grading a Test Question
The
formula in Cell B4 could be placed anywhere and provide feedback to the student.
There is often some difficulty in checking text. What are text in the set of correct answers (G Bush, George Bush, etc.) This IF statement checks for Bush and not bush or BUSH!
Because there could be so many legitimate answers, you might want to move to a multiple choice question.
The following function uses Excel's "nested" IF statements to determine a letter grade based on the point spread for converting an average to a letter grade. A discussion precedes the example.
Excel will allow you to group two or more IF statements in a single IF function. It is called nesting. The logic works like this. Suppose Cell M6 contains the average grade for a grading period. Also suppose the grading scheme works like: 90-100 = A, 80-89 = B, etc.
- First check: =IF(M6 >89,"A",continue checking) - Grade is 90-100-Give A and stop
- Second check: =IF(M6 >79,"B",continue checking) - Grade is 80-89-Give B and stop
- Third check: =IF(M6 >69,"C",continue checking) - Grade is 70-79-Give C and stop
- Fourth check: =IF(M6 >59,"D","F") - Grade is 60-69-Give D and stop or grade is 59 or less-Give F and stop
Follow the flow of logic.
If the grade is 90 - 100, the first check will return the A. If not, the checking will continue. The second check will determine if the grade is 80 or above and return the B. (Note the grade is NOT above 89 since you are still checking.) By the third check you know the grade is not 80 and up. You check if M6 is in the range of 70-79. If it is, return the C and if not, continue checking. Finally, if the grade is between 60-69 return a D and if not, return the F.
Now, let's put all of these IF statements into a single IF statement that could be entered in a single cell and determine the letter grade for the student on Row 6.
=IF(M6>89,"A",IF(M6>79,"B",IF(M6>69,"C",IF(M6>59,"D","F"))))
The logic for the "nested" IF statement is the same as the logic for the set of separate IF statements....but you can put all of the statements into a single cell!
The number of places where you can use the IF function is only limited by your creativity and patience in entering the function. Enjoy!