Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 3 down vote favorite
1

In a particular strange workbook,

In cell A1, I key the value 0

In cell A2, I key the formula =IF(A1="APPLE", 1, 0)

The result in cell A2 gives 1.

I tried to replicate the same in a new workbook, I get 0 in both cells. What happened to the strange workbook? I may seem like I am lying, but it's true. Included below is screen shot but in different cells.

enter image description here enter image description here enter image description here enter image description here enter image description here

EDIT: Cell A2 (G212) will show 1 when A1 (G211) is 0, empty or case-insensitive Apple e.g. aPpLe, ApPlE etc.

share|improve this question
What happens if you enter "APPLE" or some other value in G211? – Indrek Aug 13 '12 at 9:14
Are you sure that the content of G11 is "APPLE"? How is the cell formatted? Could you post a screenshot with the edit focus on G211? – Matteo Aug 13 '12 at 9:17
Cell A2 (G212) will show 1 when A1 (G211) is 0 or case-insensitive Apple e.g. aPpLe, ApPlE etc. – Jake Aug 13 '12 at 9:19
1  
Try evaluating the formula step-by-step (Formula tab -> Evaluate Formula), and see if at any point you see an unexpected value in the Evaluation text field. You can also post each of the steps here so we can check them. – Indrek Aug 13 '12 at 9:40
1  
Alternatively, maybe you've turned off automatic formula calculation. Under the Formulas tab, either click on "Calculate Now" to force recalculation of all formulae, or click on "Calculation Options" and select "Automatic". – Indrek Aug 13 '12 at 9:43
show 15 more comments

1 Answer

up vote 3 down vote accepted

Thanks everyone for your help. I managed to narrow down the problem to individual worksheet and found the culprit settings.

Go to Excel 2010 Options > Advanced > and then uncheck Transition formula evaluation.

enter image description here

share|improve this answer
2  
Good find! According to this Microsoft KB article, "if the Transition formula evaluation check box is selected, when Excel evaluates formulas, text strings are converted to 0" (emphasis mine). Hence why 0 and "APPLE" were considered equal. – Indrek Aug 13 '12 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.