The IF function
The IF function needs 3 parameters
- The test
- The result if the test is TRUE
- The result if the test is FALSE
The IF function compares data and returns 2 values (AND ONLY 2) : TRUE or FALSE
If you want to display text inside a cell, you must write the result of the True and False parameter between double quote.
It’s also with the symbol double-quote that you can express an empty string. If you omit the third parameter and that the result of your test is false, Excel will display FALSE inside your cell. So, to avoid this, it is better to write "" instead of nothing.
For numbers or formulas, you must write your second and third parameters without double-quote.
The first parameter of the IF function is a logical test. A logical test compares 2 data. You can have these possibilities:
Imagine the case of the result of a football match. You want to display Win or Lose when the team in column A has a better result than the team in column D
For the cell E2, you will write
Fill the following spreadsheet (directly inside this document)
But, as you know, it is also possible to have a draw. So, you must manage not 2 but 3 states with your IF function.
To solve this, you must include another IF function inside your first IF function. In your first function, you replace the first False parameter by your second IF function. So the cell E2 becomes:
Fill the following spreadsheet
Exercise : Return a grade
In column E, you have the average result for each students for an exam. In column F, write a function which returns if students Pass or Fail if the average mark is greater or equal than 10.
Change your function to display
- Fail (when the average is below 10)
- Pass (when the average is between 10 and 12)
- Good (when the average is between 12 and 14)
- Very Good (when the average is between 14 and 16)
- Excellent (when the average is beyond 16)
You can also use the VLOOKUP function (value approximate) to avoid writing many IF functions.
Exercise : Management of the stocks
You manage inventory and orders of a company. You can not honor orders if you do not have the full amount requested.
So you create an IF function that will check that you have the quantity in your stock. If your stock is lower, you can not deliver the amount you have.
For example you can write the following function:
For the second part of the exercise, you initiate an order with your suppliers if you could not fulfill the entire order. You can write the formula in different ways for an equivalent result
Exercise : Equity portfolio
You have a stock portfolio. In column C you have the purchase price and column D the last price. Write the result in column E if you win money or in column F if you are losing. The result should also take into account the number of shares you own.
Then you make the sum of columns E and F and then in cell E9, you make the difference in cell E8 and F8 to see if you gain or loss.
(Solution in the other tab in the workbook)