How to Use IF Function in Excel: Programming in Microsoft Office

Logical statements are a part of any programming language. You don’t need to know programming to use specific functions in Excel, but you do need to understand how the Excel IF statement works. The IF statement is also a part of other programming languages, so it’s easy to grasp if you already program in languages such as C++ or even Visual Basic. If you’re not used to programming languages, you just need to understand the logical flow of an IF statement to properly set up the function in your spreadsheets.

New to Excel? Get started at Udemy.com.

The Excel IF Statement Syntax

The IF statement has an easy syntax to remember. The following is the pseudo-code to better understand the function:

If(logical condition, if true this, if false this)

The basic structure of an IF statement is a logical condition or comparison. If the logical condition is evaluated to “true,” then the next statement is what the code should do. If the condition evaluates to “false,” then the second block of code is executed. You can also embed IF statements, but they are usually frowned upon, because they are too complex and make the code difficult to read. This is true for your Excel code as well, but it’s an option if you feel the need to embed functions.

In the above code example, the logical condition is the first parameter in your function. The second parameter is executed if the statement is true. If the logical statement is false, then the third parameter is executed instead.

Understand financial functions in Excel

You can use several types of logical statements in an IF function. You can compare two values such as evaluating if one value is greater or less than the other. You can also evaluate if two values are equal either numerically or as a string. A string is a list of characters, so you can evaluate if two spreadsheet fields have the same word or sentence.

Writing Your Own IF Functions in Excel

Now that you know how to write an IF statement, you must be able to integrate it into your spreadsheets. As you probably know, your spreadsheet is set up as rows and columns. Columns are letters and rows are numbers. The cross-point is a spreadsheet field. For your IF statements, you need to know your field name to enter it into your function’s parameters.

To set up your IF statement, you need to use three fields: the two fields you want to compare and then a field to display your results. Your displayed results can be a static string, or you can calculate the result based on your logical condition.

For instance, you might want to calculate commission based on a sale’s amount. If the sale’s amount is $5,000 or less, you want to give the sales person 5% in commission. If the sale’s amount is over $5,000, the commission is 6%. The following is how you would set up the IF statement:

If(A1 <= 5000, A1*.05, A1*.05)

In the above statement, the sale’s amount is located in the A1 field. If the A1 field has an amount of 5,000 or less, the IF statement multiplies .05 of that amount. If the sale’s amount is greater than 5,000, .06 is multiplied.

Learn how to create powerful reports in Excel

You can also display strings as your results. For instance, taking the above scenario, you could also display a static string just to know when the salesperson made a sale higher than $5,000. Take the following IF statement:

If(A1 <= 5000, “Under $5000 sale”, “Over $5,000 sale”)

The logical statement executes the same as the previous statement, except with this function a static string is displayed in the result’s field. In this case, the text “Under $5000 sale” is displayed if A1 has a value under 5,000. If A1 has a higher value than 5,000, the string “Over $5,000 sale” is displayed.

As you can see, the IF statement is a simple little coding function that provides some powerful dynamic content for your spreadsheets. You can also use the “Fill Down” function to quickly copy one typed IF statement to several rows at once. Excel is smart enough to know to match the next row’s values with the subsequent copied function.

After you figure out how to use this function, you’ll find that it’s a convenient way to make dynamic field content.

Learn how to use VBA for Excel today