Excel: Nested If, Then Statement

Hello:

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?

Thanks!

TBSupport
LVL 1
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
``````=IF(D2=2, 3,2)
``````
0
Author Commented:
Hi Randy:

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

TBSupport
0
Commented:
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...
0
Microsoft MVP ExcelCommented:
Hello,

let's take this apart. Your formula is

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

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")
``````
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
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
> =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"," "))
or
=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.
0
Microsoft MVP ExcelCommented:
@Sunbow:

>> 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.
0