Excel is such a powerful spreadsheet program that I doubt anyone knows everything it can do. There are so many aspects to the program that it’s surprising that it can all be wrapped up in such a simple interface. I recently used Excel for work at home and found myself using the IF function to write multiple formulas.
Being able to write a simple IF statement in Excel is very important, especially if you are constantly dealing with a lot of numbers. This is one of those things that are not too complicated like macros, but not necessarily intuitive, such as summing a column of numbers.
IF statements in Excel
In this article, I will walk you through a quick guide to writing an IF statement in an Excel worksheet. First, you must understand the basis of all IF statements:
IF condition THEN true ELSE false
That’s all. A condition is usually a comparison of something. These are usually numbers or text. We’ll start with a simple comparison of numbers, then text, and then add operators like AND / OR, etc. So here’s our first example:
We have a very simple example. Two columns with two numbers, 25 and 35. If column C, we want to check if column A is 10, we will display the text “Yes”, otherwise the text “No”. A few notes:
— /
1. It is not necessary to put parentheses around the actual comparison, but it is very good practice, especially when the comparison becomes more complex.
2. To output text for a true or false value, you must use double quotes. If you don’t, it will think that you are trying to reference a named range in Excel.
Now let’s see what else you can do. In the comparison section, you can also do math. For example, you can do this if you like:
Here we say that if the value A2 multiplied by 10 is greater than 10, then print Yes, otherwise print No. Pretty simple huh? Now, suppose we wanted to display numbers instead of text. Here’s one example:
In this example, if the value in A2 is greater than 10, it will output the value multiplied by 2, otherwise it will be multiplied by 5. Pretty useless, but you get the idea. You can also use other cell values ??in the formula.
Here we say that if A2 is greater than 10, then multiply B2 by 10, otherwise multiply B2 by 5 and output this value to cell C2. Now let’s complicate this. What if we wanted to output Yes if both A2 and B2 are greater than 10?
Here’s what’s new! If we want A2 10 and B2 10, we must use the AND function inside the IF function. Sounds complicated, but not too bad. The AND function basically takes multiple comparison arguments (A2 10) and ANDs on them. All of them must be true for “Yes” to be shown. You can add three, four or more comparison arguments if you like. If you want to do OR, just replace OR with OR.
How about returning a value other than text or a calculated number. What if you want to return the value of a cell in another sheet? Well, you can do something like this:
As a true value, I will return the value of cell A1 in sheet 2. It’s pretty simple! So you can easily reference values ??from other sheets if you like. In the same light, you can also use a value from another sheet in a boolean comparison like Sheet2! A1 10. Let’s see what you can do! Let’s say you wanted to create an IF statement based on the type of value stored in a cell (number, text, or space). You can do something like this:
You can use ISTEXT and ISNUMBER for two other data types. These functions can be very handy when writing IF formulas. Note that you can basically use most of the functions in Excel inside an IF statement, so feel free to. For example, you can use functions like SUM, MAX, MIN, etc.
So far, we’ve only worked with one value in a boolean comparison, but let’s say we want to work with a group of cells. For example, if all numbers in the range of cells are greater than 10, print True, otherwise – False.
It’s as easy as using a colon as shown above, A2: A4. In this case, each value from A2 to A4 will be checked to see if it is greater than 10. If all of them, then the true value will be displayed. What about nested IF statements?
Here I say that if A2 is greater than 10, print the value, and if not, then do another IF comparison. IF B2 is greater than 10, print another value. You can nest IF statements at 64 levels, which means you can do quite complex things. You may never have to go that far, but sometimes it can be useful to go deeper several levels.
This is just a basic introduction to the IF statement, but hopefully it will get you started. Are you trying to write an IF statement to do something in Excel and you can’t figure it out? Leave a comment here with the problem and I will try to help you write the correct IF formula. Enjoy!
–