why is a cell testing out with >0 when its value appears to be 0?

**jrtaylor** · 03-06-2019, 09:33 AM

I'm running into a weird issue with one of my workbooks, attached.

One of the formulas (Cell T4) should only show a "S" if two conditions are met: AND(H4<0,U4>0)

But even though to the best of my knowledge, U4 should be 0, it "tests out" as having a value >0. (see my test on row 9).

I try to eliminate any minuscule fraction in U4 by using the rounddown(value,0) function, but that doesn't help.

I also try to "see" any value by multiplying U4 times a huge number like 10,000,000,000, but that still doesn't help.

Any ideas? If there is indeed a minuscule fraction in U4 I suppose I could create a nested formula in cell U4 that says if(AQ3*$W$1,and(>0,<1),0).

But it will help me as I do my work to know what is really going on. (one chance being that I've got a blind spot!)
Thanks

**modytrane** · 03-06-2019, 09:47 AM

It will work if you change the formula:

HTML Code:

**MrShorty** · 03-06-2019, 11:50 AM

But even though to the best of my knowledge, U4 should be 0

Why do you think U4 should be 0? If I follow what the formula in U4 is doing, A4 is blank, so U4 will return empty string "" -- which is not the same thing as 0. "" is a short text string, but still a text string. In my testing, all text strings are "greater than" (whatever that means when comparing numbers and text) all numbers. So "">0, "">456, "">125696, and "">1E300 (close to the largest number Excel can store) will all return TRUE, because text is greater than numbers.

Modytrane's solution is to replace the empty string "" output with the number 0 -- and maybe that will fit into the desired logic of this sequence of formulas. It's not clear to me how this test fits into your overall logic sequence, so I cannot say if replacing "" with 0 will be a suitable replacement. That should at least explain why U4>0 is TRUE when A4 is blank.