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)

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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))

  • Adamcain62
    Adamcain62 ✭✭✭✭
    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...

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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))

  • Adamcain62
    Adamcain62 ✭✭✭✭
    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!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!