Excel If statement - Online Training Videos With Examples
When should you use the "IF" statement? - Examples
(For details on how to write and use the function see the two videos above)
Whenever you want to present some text according to a criteria: if the criteria is met then present this text, if the criteria isn’t met present that text.
Or if you want to calculate something, depending on a criteria: if the criteria is met then calculate this, else (the criteria isn't met) calculate that.
Examples: Using IF with texts:
If a vehicle price is higher than $40,000 then write “It is too expensive”, else write “It is affordable”.
If the number of students in the class is greater than 30, write “It's over the limit”, else write “It's still within the limits”.
Examples: Using IF with calculations:
If a worker’s shift was longer than 10 hours, his daily payment should be calculated by consideration of extra hours, else it will be calculated as regular hours.
If the deposit in the bank is greater than $100,000, then it deserves an interest of 4%, else it gets only an interest of 3%.
Advanced If statement usage:
Using multiple criteria with the “AND” and “OR” functions
Sometimes you need to check more than one criteria with the If statement. For example:
Only if the student has grades greater than 80 in Math, greater than 80 in English and greater than 80 in History, only then write “good student”, else write “Average Student”.
The way to implement such a case is using the “AND” function which will include all the multiple criteria inside of it.
This “AND” function, is then put inside the criteria section of the If statement.
Thus for the above example, the whole function could look something like this:
Note that the criteria part: AND(C1>80,D1>80,E1>80) will be met only if all three parts of it are met.
The function "OR" is used in a same manner, but its meaning is slightly different.
Instead of checking if all its parts is met (as is the case of the “AND” function), it checks if at least one of the criteria inside it is met. If it is, then the whole “OR” is considered met.
Hence, in the following formula:
It is enough that C1 is really greater than 80 , or that D1 is really greater than 80 or that E1 is really greater than 80 (or some combination of these three), to have the if function write “Good student”. Only if none of these cells (C1, D1 or E1) are greater than 80 the If function will result writing “Average Student”.