Problem With Counting a row of data - Equation help

Hi Community,

I am running into an issue with my equations when doing a count.

I have a set of %complete that is being updated each week that I then am using equations to create a binary count.

The Not started equation(Above) seems to be working correctly.

The In Progress equation (Above) is what I am having difficulty with. I have tried a few equations and am stumped. I am wanting this to only give a 1 when it is 99>=X>0. When this is true, I would like a 1, and when it is either at 100% or 0%, then give a 0.

Lastly, the Finished column is not working at all. I want it to give a 1 when the line item is at 100%, and a 0 if not.

From here, I am able to capture all of the 0's and 1's to create a count that changes each week. I use this as an automation pull so that I graph it over the weeks for my team.


Any help is much appreciated.

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @masonebe

    First mistake I see is that you're using 99 and 100 instead of .99 and 1. 99% would be .99 and 100% would be 1. Second mistake is that you're using greater than 100 instead of greater than .99. Unless you have something at 101%+ you should use the logic of either equals 1 (100%) or greater than .99 (99%).

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @masonebe

    First mistake I see is that you're using 99 and 100 instead of .99 and 1. 99% would be .99 and 100% would be 1. Second mistake is that you're using greater than 100 instead of greater than .99. Unless you have something at 101%+ you should use the logic of either equals 1 (100%) or greater than .99 (99%).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!