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

Solved

Posted on 2014-08-03

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

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

6 Comments

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

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

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?or

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

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.

Title | # Comments | Views | Activity |
---|---|---|---|

Excel 2010: Automation > Save Workbook > Exit Workbook | 6 | 32 | |

Problem to paste | 18 | 61 | |

conditionally copy in excel VB | 12 | 40 | |

Excel 2013: Displaying times in Milliseconds | 6 | 84 |

Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

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

Connect with top rated Experts

**17** Experts available now in Live!