Zero Minus One Equals Zero?

[ncorkren]

I have a formula "=$M16-COUNTIF(S3_Components,$B16)" where $M16 contains the result of a different formula equaling 0 (zero), "S3_Components" is a named range containing text values, and $B16 contains a text value to be counted within the named range.

The COUNTIF formula returns the value of 1, but the whole formula =$M16-COUNTIF(S3_Components,$B16) is returning 0.

I stepped through the formula using "Evaluate Formula" on the Formulas tab (see the snips). Why is this formula calculating 0 - 1 = 0 ???

T

 

[Logit]

Well ... step away from the formula and take a fresh look. In plain ole math .... if you have NOTHING and take ONE AWAY you still have nothing. You can't take anything away from NOTHING.

Hopefully, this is how the formula is working as well. ????

 

[ncorkren]

Well ... step away from the formula and take a fresh look. In plain ole math .... if you have NOTHING and take ONE AWAY you still have nothing. You can't take anything away from NOTHING.

Hopefully, this is how the formula is working as well. ????

Is this a joke? Lol

In plain ole math, zero minus one equals negative one:
0 - 1 = -1

 

[bebo021999]

What formula in M16? Does it relate to range B:B?
Check whether "Circular Reference" warning appear in left-lower corner of screen?
In some cases, "Circular Reference" cause weird calculation.

 

[ncorkren]

What formula in M16? Does it relate to range B:B?
Check whether "Circular Reference" warning appear in left-lower corner of screen?
In some cases, "Circular Reference" cause weird calculation.

This was it! Long story short: The range S3_Components was actually dependent on M16 (among other things). I thought normally there would be a warning box that popped up to prevent such a thing, but not this time.