# 00 triggered as greater than zero

Options
✭✭✭✭

I have a formula to flag a column if someone enters a number greater than zero in another column. Problem is, someone accidentally entered a double zero (00), and it triggered the flag.

Any ideas on a good way around this?

current formula: =IF([Number of Patients tested Positive in last 24 hrs] >0,1,0)

• ✭✭✭✭✭✭
edited 03/05/20
Options

Interesting. 00 is being seen as a text input and not a numeral.

Try adding a condition to look for 00

I'm not sure if you're using @row or @ a particular cell in your formula. but try this.

=IF([Number of Patients tested Positive in last 24 hrs]@row = "00", 0, IF([Number of Patients tested Positive in last 24 hrs]@row >0,1,0))

• ✭✭✭✭
Options

Just had a thought - maybe add an additional column that creates a sum of the original value, and then use that in my formula?

would prefer not to have to do that though...

• ✭✭✭✭✭✭
Options

My suggestion to look for "00" should do the trick for you. You could also change that formula to:

=IF(ISTEXT([Number of Patients tested Positive in last 24 hrs]@row), 0, IF([Number of Patients tested Positive in last 24 hrs]@row

OOPS I also noticed I forgot an additional closing parenthesis in the first formula. EDITED IT.

=IF([Number of Patients tested Positive in last 24 hrs]@row = "00", 0, IF([Number of Patients tested Positive in last 24 hrs]@row >0,1,0))

• ✭✭✭✭
Options

Interesting on a couple levels - first of all why it would recognize as text, and secondly why text would be considered greater than zero?

Thank you for the suggestion. I may try to use this on my next one. (I went ahead and started on the additional calculated sum column before I saw your suggestion. I think it might be better overall to eliminate any other possible scenario, like a 000, or some other random thing I haven't thought of that would be considered greater than zero. ; ) For this result, it is massively important not to flag and send a positive result when there isn't one!

• ✭✭✭✭✭✭
Options