Welcome!

00 triggered as greater than zero

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)

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Adding sequetial number string
Hello I could use some help with this one. I got the formula to work in the "number column"…. but I cant currently get it to work as a column formula. Basically what I am trying to achieve is have it add a number in order if the "footer comp" column is unchecked. and miss is unchecked Right now it is counting but in the…
Average with exceptions
I'm need to apply a function to average a column (answer is correct (8)) if the other column (The nurse is caring for a a patient who dove into a) is NOT blank. I copied this over from an excel spreadsheet. I would like to keep the average in the top row. Right now it's saying "#unparseable" as the formula is not the same…
Need help with INDEX COLLECT Formula
This discussion was created from comments split from: How do I combine multiple INDEX Collect formulas in one Formular?.