00 triggered as greater than zero

Adamcain62

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)

Mike Wilday

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))

Adamcain62

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...

Mike Wilday

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))

Adamcain62

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!

Mike Wilday

Hi @Adamcain62,

I just realized my second variation was jacked up by this text editor. It took my formula and converted it to a blockquote, totally messing up the last half of the formula. This variation will cover all of the Text entries, 00, 000, ETC.

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