You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s post is brought to you by Frederique Klitgaard. Frederique is a writer on Office.com
It’s easy to understand why the IF function appears in the top 10 list of popular Excel functions. It’s a versatile function that can be used for all sorts of tasks.
For example, this function can save you a lot of time when you need to grade a stack of papers. It quickly turns a long list of test scores into grades without having to do any of the math. It can also instantly determine who passed or failed.
I don’t ever grade papers but I’ll use this example to show you the effectiveness of this function. Let’s start by having a look at the syntax that this function uses to determine that a condition is true or false.
The first argument, logical_test, is the required value or expression that will be evaluated to true or false. Expressions use comparison operators, such as equal to (=) or greater than (>), for example A10=100. For a list of comparison operators, see Calculation operators and precedence.
The other two arguments, value_if_true and value_if_false, are optional arguments (as indicated by the square brackets in the syntax). When these arguments are used in the formula, they’ll return the value that you want to be displayed when the logical_test argument evaluates to true or false.
I’ll apply this syntax to the following list of test scores.
To determine who passed or failed the test, I want the formula to return the following results:
Test score
Result
Greater than or equal to 70
Passed
Less than 70
Failed
In the first cell of the Result column, cell C2, I’ll enter the following formula:
To turn the list of test scores into grades, I can go a step further and embed additional IF functions as formula arguments, often referred to as nesting. You can nest up to 64 IF functions as value_if_true and value_if_false arguments.
I want the formula to return the grades as follows:
Grade
Greater than 89
A
From 80 to 89
B
From 70 to 79
C
From 60 to 69
D
Less than 60
F
Based on these conditions, the nested formula I’ll enter in the first cell of the Grade column, cell D2, will then look like this:
Notice that I’ve nested three other IF functions to calculate the grades. The last argument doesn’t need the IF function; it automatically displays an F for numbers that have not been accounted for in the nested IF functions. I’ve used the IF function four times, so I’ll need to include four ending parentheses in the formula.
Because I used a table format, the formula is automatically applied to all test scores. If that were not the case, however, I would just fill the formula down to the other cells in the column to get my list of grades.
That’s all there is to it. I’m ready to get my rubber stamp and grade away!
I hope that the examples I used will help you become familiar with this function. For additional information and other examples, please see our function reference article, IF function.
Comments: (12) Collapse
Nice explanation of the IF() function. I believe you should mention that the limit of 64 levels of nesting only applies to Excel 2007 and newer. Prior versions of Excel (which are still used by tens of millions of people) have a limit of just 7 levels.
IF() is useful without question, but there are often much better strategies to employ, as noted in my article "I Herart IF()":
www.excelhero.com/.../i-heart-if.html
Your example of using the IF() function for grades is actually the classic example of what Approximate Match is used for in the [range_lookup] parameter of the VLookup() function, which would be a MUCH better solution for grades, especially if one included additional thresholds like A- and B+.
I detail this in my VLookup() video at:
www.excelhero.com/.../which-function-to-use---part-1.html
Regards,
Daniel Ferry
excelhero.com
Daniel,
Aren't you being extremely polite. This article should have being titled
"A great example of When not to use IF" - Tragic to see such post on a MS site
=MID("FFFFFFDCBAA",INT(B2/10)+1,1) works nicely too.
Here's another, less fragile way
=MID("ABCDF",10-MAX(MIN(INT(B2/10),9),5),1)
Do you have the source for this please? I'd be really interested, it would make a nice focus for an Excel class.
I'm afraid I'm with the other comments on the appropriateness here, exam grades are the example I use when teaching when not to use IF statements!
Daniel, thank you for pointing out that earlier versions of Excel have a limit of 7 nesting levels. And your blog about this function on excelhero.com is great!
Sounds like I could have used a better example. Using MATCH and MID are interesting alternatives.
IF is on our top 10 list because we get a lot of queries for this function. This indicates that there is an enormous interest in using IF. I'd love to hear about best practices and better examples.
Very interesting. I love Excel and am always willing to learn more.
how to use microsoft excel to calculate the program? can teach me?
A good place to start is the Office training site:
http://office.microsoft.com/en-us/support/training-FX101782702.aspx
You’ll find an assortment of training courses there, such as this one: Get to know Excel 2010: Create formulas
http://office.microsoft.com/en-us/excel-help/get-to-know-excel-2010-create-formulas-RZ101862712.aspx
You’ll also find training courses for earlier versions of Excel.
Hi i facing isuee in excel, in formula =if.
there are 2 colums with date a and b there are blank cell in the coloums, i want to fill the banks with the dates in the b coloum but the critira is if a and b is blank take the date from b i.e above date will come in the blank cell for coloum b, i added a coloum c and used =if(a2=b2,b1," ") how do i do it. please suggest.
Hi jaideo,
You could use the actual dates you want to insert instead. For example:
=IF(A2=B2, "9/16/2010", "9/15/2010")
After you fill the formula down in the C column, you can copy the results and paste them as values in cell A2 (Home tab, Clipboard group, Paste arrow, Paste Special, Values).
This is beneficial for selecting multiple variable attributes in a table. I was used to a different logical variable for computing items and this is extremely helpful.
Thank you
Comments: (loading) Collapse