Looking for percentage, getting incorrect arguement

I am sure there is some simple answer, but I brain hurts.

We are developing calculations and metrics to quickly assess the status of a project. I am trying to find the percentage of Critical Path taskes up to a Go Live date, that are marked at risk. My first attempt worked, but I received a 'Divide by Zero' error.

Now I am trying to wrap it in an IFERROR( to return blank if the total is zero. Where should it go? No matter where I put it, I get an incorrect arguement error. Do we have to calcute the denomitaor/numerators separately first? Thanks!

=COUNTIFS([Program type(s)]:[Program type(s)], OR(HAS(@cell, "ANE"), HAS(@cell, "Core")), Summary:Summary, 0, [Completed Date]:[Completed Date], <=$Start$30) / COUNTIFS([Program type(s)]:[Program type(s)], OR(HAS(@cell, "ANE"), HAS(@cell, "Core")), [CP Helper]:[CP Helper], <>"Post Go Live - We Have LiftOff!", Tracker:Tracker, "Red")

Tags:

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @Tina Rustvold if the original formula works, it should work to just wrap the entire thing in the IFERROR. Additionally, I would add some parenthasis around the numerator and denominator just to feel safe:

    =IFERROR(

    (COUNTIFS([Program type(s)]:[Program type(s)], OR(HAS(@cell, "ANE"), HAS(@cell, "Core")), Summary:Summary, 0, [Completed Date]:[Completed Date], <=$Start$30)) / (COUNTIFS([Program type(s)]:[Program type(s)], OR(HAS(@cell, "ANE"), HAS(@cell, "Core")), [CP Helper]:[CP Helper], <>"Post Go Live - We Have LiftOff!", Tracker:Tracker, "Red"))

    , "")

  • Tina Rustvold
    Tina Rustvold ✭✭✭✭✭

    Thank you Lucas! I see what I was doing now; I thought I needed to state the value of zero before the "" (blank). Like I said, my brain was done!

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Totally understand @Tina Rustvold -- glad it worked out!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!