Last Updated on
The IF function is probably one of the most well-known formulas for Excel, as it allows for data points to be compared between a value and what you are expecting.
IF statements are powerful because you can have two results. The first result is going to inform you of whether your comparison is true. The second result will tell you whether your comparison is false.
Here are the basics of IF functions and how you can use them in Excel.
Example Of IF Function
In column 1 we have a list of Musical Artists, and in column 2 we have Total Album Sales. For reference, we would consider any album sales over 100,000 good, and any album sales over 200,000 excellent.
Step
If Function
If we begin to write “=if(“ into cell C5, you’ll see that it now says logical_test which would represent the Total Album Sales in the second column.
Step
Use Comparison Symbols
We then select cell B5 and use the “>” key with “100,000” so it will look like this “=if(B5>1000,000.”
What this says is is the value of cell B5 greater than 100,000?
Step
Use A Reference Point
To make this process even easier, you can have cells that already have this value, and then simply use this cell as a reference point. For example, we already have the value 100,000 in cell C2, so we select this instead of writing 100,000.
Step
Use The F4 Key
You can also use the F4 key which will fix the column and row. So the formula will look like this:
=if(B5>$C$2
If it is greater than 100,000, which in our example it is, then we need to follow up with our formula so that the cell will complete an action.
Step
Use Quotation Marks
We put a comma after the value of 1000,000 and then write “Good”. You must use quotation marks when working out of Microsoft Excel, otherwise use a predetermined cell instead.
Our example now looks like this, with the Good selection being used via cell D2:
=if(B5>$C$2,$D$2,
To add to this, if you want the cell to do nothing if our Total Album Sales value is less than 100,000 we can simply put in a double quotation mark, whilst closing the bracket at the end of the formula.
This will look like this:
=if(B5>$C$2,$D$2,””)
What will happen is that in our third column, if the value of an album sales exceeds 100,000 you will see the word Good appear in those columns, and for any albums that didn’t surpass the 100,000 mark, the cell will remain blank.
You can then use this formula to determine whatever data point you are looking to highlight.
Step
Add Different Labels
In our example, instead of looking for album sales over 100,000, we can look for album sales under 50,000 and make the IF function list them as Bad.
Or we could add different labels such as exceptional if an album went over 200,000 total sales.
Common Issues
There are a couple of common errors that will occur using the IF function. One example is that if you have a “0” value in the cell, this means that there was no argument or selection.
Or perhaps you see “#NAME?” in the cell. This represents that a formula has been misspelled, and as such you must make sure you not only use punctuation marks but also you spell your values correctly.
Final Thoughts
There are many more ways it can be used, but these are the basics. Which should hopefully guide you in the right direction.
