Search Not Just Numbers

Thursday 21 October 2010

EXCEL TIP: The IF Statement made simple

One of the functions I use most often in Excel is the IF statement. This function is very powerful and to many can seem very complicated, whereas to others it is deceptively simple and its power can be underestimated.

The basic IF statement


The basic format of an IF statement is as follows:

=IF(if this is true,return this,otherwise return this)


Example:

=IF(A2>3,"Greater than three","Not greater than three")

returns the text Greater than three if the number in cell A2 is greater than 3 and Not greater than three, if it is not.

The first argument can be any expression that can be true or false, usually using =,>,<,<=,>= or <>.

Other Examples:

=IF(A3="X","Yes","No")
=IF(SUM(A2:A10)>B2,"Over Budget","Within Budget")
=IF(A2<0,0,A2)

Combining conditions


All of the above examples include only one condition, but it is possible to combine numerous conditions using the AND and OR functions.

Theses functions are formatted as follows:

=AND(Condition 1, Condition 2, Condition 3.....Condition n)
=OR(Condition 1, Condition 2, Condition 3.....Condition n)

The AND function returns TRUE if ALL of the individual conditions are true.

The OR function returns TRUE if ANY of the individual conditions are true.

They can be used in IF statements as follows:

=IF(AND(A2<=200,A2>=100),"In Range","Out if Range")
=IF(OR(A2="X",B2="X",C2="X"),"Contains X","Doesn't Contain X")

More complex decisions (Nested IF Statements)


If the decision required is more complicated, you can have IF statements within IF statements - this is called Nesting.

Example:

=IF(A2=0,"NIL",IF(A2>0,"POSITIVE","NEGATIVE"))

If A2 is 0, this will return the word NIL, however if A is not 0 the third argument is another IF statement that will return the word POSITIVE if A2 is greater than zero, otherwise it will return NEGATIVE.

And that is the IF statement. Don't forget you can still take the easy route and get your spreadsheet built for you at Spreadsheets by Email.


Click here for our our exclusive offer on Online Excel Training 

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

46 comments:

  1. Thanks for sharing us informative ideas.

    ReplyDelete
  2. Finally, an understandable explanation of the IF statement. Thank you very much!

    ReplyDelete
  3. Julie, pleased you found it useful.

    ReplyDelete
  4. The IF function is my go-to as well. It is the first function I turn to to approach any formula. Probably not the best process but that goes to show how big of an impact it has had.

    Good work covering the basics.

    -Grahm
    Excel Statements

    ReplyDelete
  5. One of the clearest (and shortest) overviews on this topic...thank you.

    Mia B.
    excel statement

    ReplyDelete
  6. What if the formula is

    =AVERAGE(IF(R9:BB9>0,6*R7:BB7;R7:BB7))

    Than if R9:BB9 is larger than 0,6xR7:BB7 than it is R7 and otherwise it is BB7?

    ReplyDelete
    Replies
    1. What I think you're trying to do is calculate the average of a range of cells, but use a factor of 6 for each cell in that range where the corresponding value in another range is positive
      If so, the above was very close. You just need to replace the semicolon with a comma and enter the formula as an ARRAY
      Using Arrays is a very powerful technique, much too big to tackle here, but here you just need to type the formula correctly and use Ctrl-Shift-Enter instead of enter
      NB this formula here will NOT ignore empty cells if the condition is met, but average them as if they were zeroes. If the condition is not met then they will be ignored - you could overcome this by using 1*R7:BB7 as the action if false

      rgds, jabche

      Delete
    2. jabche

      I think the posted formula is a non-English localisation so the comma (,)becomes a decimal point (.) and the semi-colon separator (;) becomes the comma (,) as you suggested.

      One thing that is possible as an alternative to CSE data entry is to place the formula in the 'Refers to' box of a new Name (e.g. filtered_average) using Name Manager.

      Then typing
      = filtered_average
      anywhere in the workbook outputs the result.

      Peter

      Delete
    3. Peter,

      if that were true then the first comma would have been a semi-colon too

      or perhaps that one was the misytpe

      jabche

      Delete
  7. Having issues with IF statements for words. Example:
    =IF(B11=nylon, 5, 0)

    Why doesn't this work? Is there a work around? Thanks.

    ReplyDelete
    Replies
    1. When referring to text directly in any Excel formula (rather than by referencing a cell or range containing the text), the text must be enclosed in quotes, e.g.

      =IF (B11="nylon", 5, 0)

      Delete
    2. When referring to text directly in any Excel formula (rather than by referencing a cell or range containing the text), the text must be enclosed in quotes, e.g.

      =IF (B11="nylon", 5, 0)

      Delete
    3. or use a Boolean solution:

      =5*(B11="nylon")

      jabche

      Delete
  8. Hi ya, CAN ANYONE HELP PLEASE?!?

    I have a basic statement that isn’t working:
    =IF(G21=$M$12, “Yes”, “No”) This is in cell CI21
    M12 is an absolute cell while the column G reference will need to change within each row

    G21 is populated by a VB .Value = “”
    M12 is a Validation list

    All cells are set to ‘General’ – the Show Formulas is off – and the Automatic calculation is on

    But it still doesn’t update when the value of M12 is altered from the list?

    I have tried typing long hand with more brackets, copying another IF that does update and amending the cell references, switching the order to M12=G21, creating the formula in a ‘nearer’ cell (incase it was a capacity issue) and checked that all three cells are comunicating with =SUM(G21-M12)… which does work and display the result. I’ve also tried physically selecting the cells rather than typing their references in the formula —- but nothing has worked.

    PLEASE HELP!!

    ReplyDelete
    Replies
    1. The first assumption would be that if your formula tells you the two cells are not the same then it is right. A number may have a small rounding error that prevents a match even when the numbers display the same. Text may have a trailing space or non-printing character. Another possibility is that one of the 'numbers' may actually be a number whilst the other is digits formatted as text.

      By the way, $G$21 strikes me as somewhat small for a validation 'list'.
      Peter

      Delete
    2. make sure calculation isn't set to manual (this has caught me out several times)
      press F9 to force a recalculation

      Jim

      Delete
  9. Dear Alix

    I'm not an expert in VBA, but some other readers are.

    My guess though is that the code is where the problem is. Do you not need to force a recalculation if the data is entered by code.

    Some other readers might have more information for you (please comment if you do!)

    ReplyDelete
  10. =IF(A2=0,"NIL",IF(A2>0,"POSITIVE","NEGATIVE"))

    If A2 is 0, this will return the word NIL, however if A is not 0 the third argument is another IF statement that will return the word POSITIVE if A2 is greater than zero, otherwise it will return NEGATIVE.

    as per the above example,
    my Query is if the cell is 0 the value be Nil, but if my cell is empty, it is also considering as 0 and place the value as Nil,
    i Required, if the cell is empty the result to be filled as N/A,
    Kindly suggest..

    ReplyDelete
    Replies
    1. Just use the same logic again, leaving your formula as a third argument in another IF:

      =IF (A2="","N/A",IF(A2=0,"NIL",IF(A2>0,"POSITIVE","NEGATIVE")))

      Delete
    2. instead of nesting IFs, I'd enter:

      =IF(ISNUMBER(A2),CHOOSE(SIGN(A2)+2,"negative","nil","positive"),"n/a")

      which will return n/a if A2 is anything but a number (there's never a single "right" answer)

      Jim

      Delete
  11. Hi Glen,

    How do I use IF statement if I have several conditions like below:

    Example:

    Cell A2 - number with 2 decimal places (e.g 2.46)

    I want to categorize the value in column A (A2 to ~) into next cell/column by the below conditions:

    Bin1 = 1.75 ~ 2.01
    Bin2 = 2.01 ~ 2.18
    Bin3 = 2.18 ~ 2.37
    Bin4 = 2.37 ~ 2.57
    Bin5 = 2.57 ~ 2.71

    How do I create an IF statement that contains all 5 conditions? I believe there is a way.

    Regards,
    @tikun

    ReplyDelete
    Replies
    1. @tikun
      You can use Nested IF statements - see this earlier post:

      http://www.notjustnumbers.co.uk/2015/06/excel-tip-nested-if-statements.html

      However, a better solution would be to use a lookup table, as per this post:

      http://www.notjustnumbers.co.uk/2012/10/excel-tip-look-up-commission-or-tax.html

      Delete
    2. Thanks for the feedback Glen.

      Sorry I haven't had the chance to read all of your posts earlier but I am surely will do. :)

      Delete
  12. Here's one for you!

    My order book shows sales orders AND sales returns as positive numbers.

    How would I construct an IF statement that says "if(a2="sales return",make the number in d2 a negative, leave as is)?

    Or is there another way...?

    Thanks in advance :)

    ReplyDelete
    Replies
    1. You need to leave d2 alone and use it in the formula in another column, say column E. So in E2, you would use the formula:

      =IF(a2="sales return",-d2,d2)

      Then use column E as your values.

      Delete
  13. Any advice for me. Seems like formula is only catching IFNA
    =IFNA(IF(AND(B2<0,C2<0),B2-C2)&IF(AND(B2<0,C2>0),B2+C2)&IF(AND(B2>0,C2<0),B2+C2)&IF(AND(B2>0,C2>0),B2-C2),B2)

    ReplyDelete
    Replies
    1. Check your brackets. Your IFNA doesn't close until your final bracket (hence this is just one IFNA function). The rest of your function is just the condition for your IFNA function and it will return B2 if that whole function returns NA.

      Delete
  14. What if it's "-1,900", how can I put that in a IF function?

    THIS IS THE EXACT QUESTION I NEED ANSWERED:

    If the units sold is 5,000 or more above the regional target, the employee gets the value in M9. If the units sold is 1,000 or more above the regional target, the employee gets the value in M8. If the units sold is 500 or more above the target, the employee gets the value in M7, otherwise they get $0.

    ReplyDelete
    Replies
    1. I would recommend calculating the amount above regional target in a separate cell (say N2), then if you want to use the IF statement it would be something like:

      IF(N2>=5000,M9,IF(N2>=1000,M8,IF(N2>=500,M7,0)))

      However, the following poat fives a neater approach to thua kind of problem:

      http://www.notjustnumbers.co.uk/2012/10/excel-tip-look-up-commission-or-tax.html

      Delete
  15. Hi,

    I was hoping to get some help with my formula below:

    My cell E32 is interchangeable, could be 50, 45, 32, 0, e.t.c. I want the returned value to be in two (2) decimal place if condition is met in the formula. instead of just displaying 1, 2, 3, 4,5 in my formula it should give me exact value in 1 or 2 decimal places. i tried to add .0 to the formula but each time it keeps disappearing.

    =IF(AND(E32>0,E32<2.2),1,IF(AND(E32>2.2,E32<4.4),2,IF(AND(E32>4.4,E32<6.6),3,IF(AND(E32>6.6,E32<8.8),4,IF(AND(E32>8.8,E32<=11),5,"Out of Range")))))

    Thanks

    ReplyDelete
    Replies
    1. If I understand correctly, your issue is not with the formula but with number format of the cell. Right-click on the cell containing the formula and select format cell, then on the Number Format tab choose Number and select the number of decimal places you want. This determines how the number is displayed. 1 and 1.0 are the same number, just different eays of displaying it, so you adding .0 to the formula didn't change anything.

      Delete
  16. How do I do an IF statement with words and numbers? My question is asking me to create a statement where I need to look at the difference between two columns (ex: E2-B2,E3-B3) and make a statement that will display the sentence "Increase by $_._" if there was an increase (positive) or "Decreased by $_._" of there was a decrease (negative) ??????

    ReplyDelete
    Replies
    1. Try:
      =IF(E2-B2>=0,"Increased by ","Decreased by ")&TEXT(ABS(E2-B2),"$#,##0.00")

      The & character allows you to add text strings together.
      The TEXT function converts a number to a string in a specified number format.
      The ABS function returns the absolute value of a number, ignoring the +/-.

      Take a look at my post on using data in sentences for more details:
      http://www.notjustnumbers.co.uk/2012/11/excel-tip-using-data-in-sentences.html

      Delete
  17. How do I create a statement that will display the sentence "increased by $_._" if there was an increase in a price (ex: E2-F2, E3-F3), or "Decreased by $_._" if there was a decrease in the price ?????

    ReplyDelete
  18. I have one for you. I am looking for a formula that can stop when it reaches a negative number in the row and return the value in row 1 of the column it turned negative in. Ie, if I have X carrots and I want to figure out when I will run out of carrots, knowing the weekly demand for the carrots... (terrible example but hopefully you understand). I'm using if(sum(b2:c2)>a2,c1) then I'm filtering out the date returned from the equation and changing to if(sum(b2:d2)>a2,d1) and so on with the filtering. This gets tiring after a while especially when you are looking out weekly over the course of 12 months:( In this example row 1 is a date and the columns start with the carrots I have and continue with the demand for said carrots... Any help appreciated!

    ReplyDelete
    Replies
    1. Try this. You will need a helper row though.
      We'll assume your data covers 52 columns, i.e. column B to column BA.
      In cell B3 insert the formula:
      =A3+B2
      and copy along to column BA (make sure there is nothing in cell A3)
      We can then use INDEX and MATCH to solve you problem:
      =INDEX($B$1:$BA$1,MATCH($A$2,$B$3:$BA$3,1)+1)

      The MATCH function identifies the last column in our cumulative range that is still below cell A2. We add one to this to be the column which exceeds it. The INDEX then returns the corresponding value from row 1.

      Delete
  19. I am using the IF function as such: =+IF(R3="0", Q3). However, I only want the positive values in column Q to be reflected.

    ReplyDelete
  20. I have another one for you after you were such a huge help. I have column B which receives an x if an order was placed online. I have column F which is the total number of items ordered. What I need is a cell that will add the number in F if there is an x in B but to ignore the number if F if there is no x. I want a count of online ordered items.

    ReplyDelete
    Replies
    1. The simplest answer would be to add another column with another IF formula:

      =IF(B14<>"",F14,0)

      Then sum that column.

      Delete
  21. Hello, Im trying to write an If statement for the following
    Cell K36 has a number (could be negative or positive)
    In Column M36 my if statement is =IF(K36>0,D36,L36)
    This is working when the number is positive however if the number is negative its not returning the right value.
    Please can you help
    Many thanks

    ReplyDelete
    Replies
    1. =IF(K36>0,D36,L36) should return D36 if K36 is positive or L36 if K36 is zero or negative. Is that not what you want?

      Delete
  22. Hi Glen thanks for getting back to me.

    If K36 is 0 or positive number then return D36,
    If K36 is a negative number then return L36

    Thank you very much for your help
    kc

    ReplyDelete
  23. I'm trying to do a capital gain tax calculation based on the days in column K26. Strangely its working in some of the cells and not in the rest.
    If I change the formula to =IF(K36<0,D36,L36) then it works. Not sure what I'm missing, spent a lot of time today in trying different things and reading lot of blogs but no luck.

    Appreciate your help

    ReplyDelete
  24. If this is what you want:
    If K36 is 0 or positive number then return D36,
    If K36 is a negative number then return L36

    Then you need:

    =IF(K36>=0,D36,L36)



    ReplyDelete