Excel is often seen and used as a simple spreadsheet application to keep track of facts and figures, add figures together or do other straightforward arithmetic functions, but excel has evolved from its simple spreadsheet days to a complex program that allows all sorts of programming to be included in the spreadsheet itself. These functions can make your life a lot easier where it comes to analyzing the data within the spreadsheet. The IF THEN statement in Excel is a perfect example of how a little programming in Excel can go a long way to making your life so much easier.
This tutorial is written with the beginner in mind but to understand and follow the tutorial, you need to know how to add data to cells in excel as well as performing basic spreadsheet functions. Udemy offer a basic excel course which will teach you the basics you need to know to get the most out of this tutorial.
How an IF statement works in programming
If you have an idea of how the if statement works in programming in general then this will help you to understand how the if statement works in excel. Programming is just a series of instructions which you give to the computer to perform a set of predefined functions. In the case of the if statement, you are telling the program, in this case excel, that you want it to check a value you give it against a specific condition and then it must perform a function based on the answer.
Functions in programming are defined using syntax – which is just a way of telling the programmer how to construct the function so that the program knows what you want it to do. For a more advanced course in excel to help you get the most out of your spreadsheets, udemy offer Use Excel Like A Pro which will give you a solid foundation within two hours of learning.
Syntax of the IF statement in excel
The syntax of the IF statement in excel is expressed as follows:
IF( condition, [value_if_true], [value_if_false] )
When you type “IF” in a cell in excel you are telling excel that it must get ready to perform the instructions that follow the statement. You need to specify the condition for the IF statement in the first part of the bracket and then also specify what must happen in the condition is true or what must happen in the condition is false.
You need to note that the if function will be entered into a cell in your worksheet, in the same way that you would add any other calculation or function so you need to start with an equal sign.
A real example of using the if statement
So lets create a real example of how you would use the if function in a spreadsheet. Let us assume we work for a publisher and we are working on a spreadsheet that keeps track of our book sales.
Now that we have our data we can use the if statement to analyse our book sales. Lets assume that the boss would like to pay a bonus to all the authors who have sold at least 500 books. You could go down the list and manually select those authors, but if you have a list with thousands of authors this would become very time consuming. And this is exactly where the if statement can make your life so much easier.
By using the if function in excel, you can create a small “program” that will help you sort through the data to easily select the authors who are entitled to the bonus. To create the if statement all you need to do is write the condition in terms which excel will understand and then tell excel what to do if the condition is false or what to do if the condition is true. In this case our condition is that the sales under our book sales column must be greater than 500. So the code for the condition would look like this:
IF (value in C column > 500;)
This would tell excel to search for authors who sold more than five hundred books. Now we must tell the program what to do if the author has sold more than 500 as well as what to do if they have sold less than 500. So we add these parameters to our statement as follows:
IF (value in C column > 500; "Bonus Due"; "No Bonus")
You need to add the code in a way that excel understands. To do so, you need to add the function to a cell within the spreadsheet and excel will analyse the statement and place the answer in the cell you selected. This is what the function would look like within your spreadsheet:
You may notice that instead of just a greater than sign, we used greater or equal to in our formula. You can in fact use any of the comparative operators for this function. If you are not sure what comparative operators are or how to use them in your spreadsheet, then why not take a look at Excel 2013 The Basics course which will teach you about the basic functions in excel and how to use them.
. Here is a list of the operators you can use:
· greater than(>)
· equal to(=)
· less than (<)
· greater or equal to (>=)
· less than or equal to (<=)
· not equal to (<>)
If you enter the following statement into the cell and then copy and paste the formula for each author, you will quickly and easily see which authors are due for a bonus. This is what the resulting output will look like:
This is a really simple example of how to use the If statement in excel, but you can use the statement to perform far more complex calculations for you too. You could specify a column for the minimum sales amount and also specify the bonus percentage and have the formula work out what bonus each author should be paid.
You can even include one IF statement within another IF statement to create truly complex formulas and calculations. For an advanced course in excel which will show you how to do this and a lot more you can take a look at the Advanced Excel Training – Online Excel Course offered by udemy.