Today I’m going to talk about IF formulas. IF functions are logical functions, meaning that any formula you write with an IF function will return one value if a condition is true and another value if the condition is false.
Let’s jump directly to our example.
We have this table that we used in previous examples. It contains the city, the population and the country. We would like to run an IF formula in column D, to see if whether or not the country in column C is Scotland or not. If it’s Scotland, the formula should return Yes, if it’s not Scotland the formula should return No (you can download the example file from here).
So we go to the formula bar, type = then if then open a bracket.
We have 3 arguments in the IF function:
1- First argument: Logical test
This is the logic, condition or criteria that we want to test. In our example, we want to test if the country in column C is Scotland. The formula will be:
And note that Scotland is between speech marks “” because it’s text.
2- Second argument: Value if True
This is what I would like to return if the test is true. So if the country is Scotland, I want the formula to return the word “Yes”. So I simply type “Yes”, again between speech marks.
3- Third argument: Value if False
This is what I would like to return if the test is false. So if the country is not Scotland, I want the formula to return the word “No”. So again I simply type “No”.
Now the formula is ready and I can drag it across the rows.
Now let’s try another example with numbers. We would like to run a test to see if the population per city is greater than a specific number, say 500,000.
In a new column (Column E), we will be putting our criteria which is >500K.
So if this criteria is true, we would like to return the population of the city, if it’s false, we’d return the text “Under 500K”.
So the formula will look like this:
Note that in the first argument (the logical test) and in the second argument (value if true) we didn’t put the numbers between speech marks “” because numbers are recognized by Excel directly and don’t need speech marks. Only text is being put between speech marks and that’s what we have done in the last argument (value if false).
Now when we drag the formula across the rows, we can easily identify which cities have a population over 500K and which don’t.
IF function are one of the most commonly used functions in Excel, and they are widely used for validations. Try practicing it with your own data, and feel free to post your questions here in the comments if you have any.