Back
Excel

Q & A: Things you really want to know about Excel IF function

image

 

Today’s post is brought to you by Frederique Klitgaard. Frederique is a writer on Office.com (Note: This is a previously published post, which we are publishing again because it answers many people’s questions.) 

 

To follow up on my previous post about the IF function, in which I showed you a very simple application of this formula, I want to address some questions about the use of this function that come up regularly.

As pointed out to me, the grading example isn’t the best one for the IF function, but I think it still helps illustrate the answers to the following questions.

You asked: Can the formula results be displayed in different colors?

Yes, you can do this by using conditional formatting. To distinguish between the different formula results in my worksheet, I created a conditional formatting rule that applies a different color to cells containing specific text.

image

For more information about using conditional formatting, see Add, change, find, or clear conditional formats.

You asked: Can the IF function be combined with other functions such as AND and OR?

Yes, to match multiple conditions, you can include the AND and/or OR functions so that you can specify additional criteria that result in values, whether true, or false. For example, if I include test scores for tests throughout the quarter, I can include the AND function to determine who has completed the quarter with flying colors, using the following formula:

IF(AND(B2+E2>=178), “Excellent!”, “”)

image

You asked: Can the IF function be used with text entries instead of values?

Yes, you can use text in all arguments of this function, as long as you include quotes around it. In the examples above, the formulas have returned text-based results, but you can also use text in the logical_test argument. For example, to use a star system in addition to grades, I can use the following formula:

IF(D2=”A”, “***”, IF(D2=”B”, “**”, IF(D2=”C”, “*”, “”)))

image

Note that two of the cells were left blank. By adding two quotes without any text in between (in the last argument of this formula), I specified that cells should be blank when the values don’t meet the criteria. And yes, I centered the stars after entering the formula, just for looks!

You asked: Can the IF function calculate percentages?

Yes, you can include calculations in the formula that result in percentages. For example, to use a point system, I could use the following formula:

=IF(B2>79, B2*1%, IF(B2>89%, B2*5%, “”))

image

Because commas are used as argument separators in a formula, be careful not to use them as decimal separators in the values you want to calculate.

For additional information about this function, please see our function reference article, IF function.