Excel: Nested If, Then Statement

Posted on 2014-08-03
Last Modified: 2014-08-06

I have an Excel column containing 2s and 3s.  I tried creating a "nested If, Then statement" to say If D2 = 2, then make it 3 and if D2 = 3, then make it 2.  But, my formula produces the word "FALSE" instead of a number:

=IF(D2="2", "3", IF(D2="3", "2"))

How do I fix this statement to make it how I need?


Question by:TBSupport
    LVL 21

    Expert Comment

    by:Randy Poole
    =IF(D2=2, 3,2)

    Open in new window

    LVL 1

    Author Comment

    Hi Randy:

    Thanks, for the quick response.  But, that produces "2" and not "3".

    LVL 21

    Expert Comment

    by:Randy Poole
    I have this formula on Cell E2, if I enter 2 in cell D2 then E2 shows 3, if I enter 3 in cell D2, E2 shows 2...
    LVL 50

    Accepted Solution


    let's take this apart. Your formula is

    =IF(D2="2", "3", IF(D2="3", "2"))

    Open in new window

    and you say that the result is "False".   This means that D2="2" is false, so the 'false' part of the outer IF statement is processed, which is the inner IF statement IF(D2="3", "2")). Here you check if D2="3". The formula only has a value for the 'true' part and nothing for the 'false' part. Since Excel returns "FALSE" it means that the comparison D2="3" is false. If Excel finds no entry for the 'false' part of the IF function, it will return "FALSE".

    So, D2 is neither "2" nor "3".  Please have a closer look at D2. What does it actually contain? In your formula you test for "2" and "3". These are both text values. Does the cell contain text or numbers? Do you WANT to test for text or for numbers?

    If the cell contains text, is the "2" or the "3" all that there is in the cell? There may be a space character leading or trailing, so that the real value is "2 " or "3 ". Select the cell, hit F2 and play with the right and left arrow keys. How many different positions can the cursor take up before and after the number? A space is not a visible character, but it will feature in a comparison.  The text "2" is not equal to "2 " or " 2" or " 2 ".

    If the cell contains a number, then the test in the IF statement is the wrong test. You can test for a number by just entering the number, without the quote characters, as Randy suggested in the formula =IF(D2=2, 3,2)

    But you say that this returns 2 and not 3, which means that D2 is not the numeric value 2 either.

    So, analyse your cell D2 and adjust either the cell value or your comparison value to get the result you expect.

    If D2 contains leading or trailing spaces you can use the TRIM() function to disregard these in the IF statement.

    =IF(Trim(D2)="2", "3", "2")

    Open in new window

    This will work for the regular space character, but not for the non-breaking space that is used on web pages and may be in the cell if the value has been copied and pasted from a web site.

    If all this does not help, please post a sample file that shows the problem.

    cheers, teylyn
    LVL 24

    Expert Comment

    > =IF(D2="2", "3", IF(D2="3", "2"))

    You need two commas per IF. So you are missing last 'ELSE' before the double parens

    =IF(D2="2", "3", IF(D2="3", "2","?"))

    >  if I enter 2 in cell D2 then E2 shows 3, if I enter 3 in cell D2

    Should work for that.
    Question remains, what if you enter something other than a 2 or a 3?
    Maybe this is better:

    =IF(D2="2", "3", IF(D2="3", "2"," "))
    =IF(D2="2", "3", IF(D2="3", "2",""))
    This also works, but produces little -> what else do you want to do?
    Where this is an attempt to just end up switching two values already present in column D, then use

    =IF(D2="2", "3", IF(D2="3", "2",D2))

    and the other values {1,4,5,6...a,b,c,...} will remain in place
    - and if so, heed teylyn comment and ensure values present in sheet that are supposed to be common really are - clean it up with functions like trimming, ensure all cells are formatted to text, or whatever.
    LVL 50

    Expert Comment


    >> You need two commas per IF.

    No you don't. The false argument can be omitted. You just need to understand the implications.

    >> what if you enter something other than a 2 or a 3?

    According to the question that is not a concern.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Digital marketing agencies have encountered both the opportunities and difficulties that emerge from working with a wide-ranging organizations.
    Local Continuous Replication is a cost effective and quick way of backing up Exchange server data. The following article describes the steps required to configure Local Continuous Replication. Also, the article tells you how to restore from a backup…
    Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
    Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now