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.
Tips
How-to
News
Videos
Stories
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.
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.
For more information about using conditional formatting, see Add, change, find, or clear conditional formats.
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!”, “”)
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”, “*”, “”)))
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!
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%, “”))
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.
Comments: (13) Collapse
The AND function is innecessary in that example
=IF(AND(B2+E2>=178), “Excellent!”, “”)
is the same as
IF(B2+E2>=178, “Excellent!”, “”)
=IF(B2>79, B2*1%, IF(B2>89%, B2*5%, ""))
should be
=IF(B2>79, B2*1%, IF(B2>89, B2*5%, ""))
the AND example would make more sense if it used more than one condition
as Juan Pablo González points out, a single condition makes it redundant
also using Structured References would make the examples easier to follow and maintain
I still think using VLOOKUP would be a better method than the IF function in the examples
if this is the intent then the second B2>89%, ie B2>.89 is strange since the Test Scores look like integer numbers thus at the low end scores {0, 1, 2 ...}, it would be simplier to just use B2>0
if it was meant to be B2>89 then this condition is redundant since it 79>89 and the B2*1% would have been used thus the B2*5% is never used
hmm wonders what the author was thinking
Please excuse me if this is a duplicate comment. There is no statement here that comments are moderated and mine didn't appear, so re-entering.
I'm trying to come up with a formula that will check if any of a specified range of cells are highlighted, and give the sum of all if so, otherwise 0.
Diego,
a) For a far superior article on the the IF function please refer to www.excelhero.com/.../i-heart-if.html
b) Seek outside help to publish high quality articles on this Blog.
c) People from the "office" team / the "SharePoint" team/ the "Lets put everything on the Cloud" team need to be specifically excluded.
d) Are there any Excel "geeks" left in MS ?
I hope you will help me or direct where to find the solution.
I am working on a project were I need to use calendar after the year 2100
Excel is not regonising Leap year after the year 2096.
Year 2100 does not show Feb/29. It jumps from Feb/28 to March/01.
Thanks
You can have up to 8 levels of IFs in Excel and sometimes that can become very confusing especially when you have long and complex formulas with many parenthesis that you have to open and close.
One way to make things clearer is to write each level of your many levels IFs in one new line and indent each one.
To get a new line when you are writing your formulas you press <ALT> <ENTER> and to indent it use multiples of 3 <Space>s (Sorry, Excel does not allow you to use s in the formula bar!).
It is not worth the trouble to indent the closing parenthesis because here you are not writing a procedure as can be done in VBA.
The following example is very simple but illustrates what was said above:
=
IF(A1=1,
1000,
IF (A1=2,
2000,
IF (A1=3,
3000,
IF (A1=4,
4000,
IF (A1=5,
5000,
IF (A1=6,
6000,
IF (A1=7,
7000,
IF (A1=8,
8000,
"Excel Limit: up to 8 levels of IFs
New line: <Alt><Enter>
Indent: 3 <Space>s per indent - Use <Ctrl><C> and <Ctrl><V>"
))))))))
It is better to write long formulas first as text (with an apostrophe before the ). Then copy it to its final location, remove the apostrophe, and debug it.
It is very easy to make mistakes when typing long formulas and you can lose a lot of time correcting them and answering to the Excel error messages!
Writing them as text can save you time to retype everything again if things go wrong. This way you only have to make a copy and start again…
i need to be able to use the =if in conjunction with the =sum formulas to check all sheets in a work book (that will have new sheets added periodically) to sum the number of people that passed a class (located in j29 on every sheet between the first and last) only if cell k1 says "true"......please help
Finally Good work
If a value is False, but I don't want False to be printed, but I would like 0.00 printed instead, how would I write my formula? Right now I have it -if(cell #="y",cell#).
I've created a doozy of an equation to return certain symbols or significant figures for data. It works perfectly except for the case where the data in A1 is an integer (like 9 instead of 9.01). It successfully rounds to the wanted significant figures if A1 contains a decimal value data point. Does anyone have a better significant figure generator (last clause in equation) that will help me out?
=IF(a1<=0,"ND",IF(and(a1>0,a1<0.0001),"<",if(AND(a1>=0.0001,a1<=0.0099),ROUND(a1,4),ROUND(a1,3-1-INT(LOG10(ABS(a1)))))))
My IF function does not work in Excel 2010. I'm sure it's just a setting somewhere. When I open previous spreadsheets I've done and look at the IF function, there is no commas between the arguments [=IF(a1=0 0 1)]. If I don't try and edit the cell, it works fine. The moment I edit the cell I get a "The formula you typed contains an error" message. Even if I try adding commas [=IF(a1=0,0,1)] I still get the same error. Is there any help out there?
wfa511com@hotmail.com