CODEWITHSUNDEEP
excelexcel-formula
Brian Leach
Brian Leach

Reputation: 4154

excel "IF" formula returning "0" (it shouldn't)

Here's the formula

=IF(
    AND(
        NOT(ISERROR(Table3[[#This Row],[lot_ID]])),
        Table3[[#This Row],[uniquness]]="unique"
    ),
    LEFT(Table3[[#This Row],[lot_ID]],3),
    "junk"
)

Below is the formula evaluation (some middle steps omitted. The last two contain the puzzle.)

The mystery has really got me.

step 1

Step 2

Step 3...

...Step 8

Step 9

Upvotes: 4

Views: 32919

Answers (5)

Chechaquo
Chechaquo

Reputation: 11

It's sure related to Circular references. Had the same issue though cell Auditing Circular References is grayed out which is normally means NO circular references. Caught the cause by the following way: 1) Keyed the value into the cell with that 0 issue 2) Pressed undo 3) Got Error message referring to Circular Reference error. Still no clue how to trace where the source of that circular reference.

Upvotes: 1

tonytone
tonytone

Reputation: 81

I've also had the experience of an IF statment returning 0 when it should've returned yes" or "no" (using Office '13, 16GB pc).

My issue was the program needed to process the formula and the file full of formulas and eventually returned "yes" or "no" after several minutes. But it did cause a good 10 mins of confusion...enough for me to Google and find this.

Upvotes: 0

HeroRandom
HeroRandom

Reputation: 11

You can do a length check for the if true and if false:

=IF(B2="",IF(LEN(C2)>0,C2,""),IF(LEN(B2)>0,B2,""))

Upvotes: 1

Patrik
Patrik

Reputation: 31

I had exactly same problem.

Turns out I had a circular reference that was messing things up. I solved it by

  1. going to Formulas tab ->
  2. Formula Auditing ->
  3. Error Checking ->
  4. Circular references

It showed me the exact cell that was a circular reference. Once adjusted everything started working as expected.

Upvotes: 3

buradi
buradi

Reputation: 19

Possible answer:

The "IF" formula will only return the "0" value in these conditions:

 1. "Value_if_true"  =  0     AND Logical_test = TRUE
 2. "Value_if_false" =  0     AND Logical_test = FALSE
 3. "Value_if_true"  = "0"    AND Logical_test = TRUE
 4. "Value_if_false" = "0"    AND Logical_test = FALSE
 5. "Value_if_true"  = (NULL) AND Logical_test = TRUE
 6. "Value_if_false" = (NULL) AND Logical_test = FALSE

According to your screenshots, you have strings in both "Value_if_true" and "Value_if_false". So we must check how Excel are evaluating these strings:

In Excel (2010), go into

"Options" > "Advanced" > "Lotus Compatibility Settings for: [YourSheetName]"

Check if the "Transition formula entry" option is checked. If it is, then you probably found your problem. You just need to uncheck it. If it isn't checked, then keep it unchecked.

Excel evaluates strings to 0 when the "Transition formula entry" option is enabled. It would evaluate your formula to "=IF(TRUE,0,0)" and then to 0, just like you said it did.

Upvotes: 1

Related Questions