Excluding Zeros from an Average Formula



I am creating a simple dashboard showing the average percent complete for a portfolio of projects. We have now decided to carve out those projects not yet started (0% complete). How do I edit my formula to carve out those projects that are at 0% complete?

=AVERAGEIF({Information Technology Business Plan Range 2}, "Transit Information Program", {Information Technology Business Plan Range 3})



  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭

    Hi @Stacey Gerhardt!

    Here's a couple options for you:

    AVERGAGIF() is a good option if there's only one criteria you're trying to meet, like excluding zeros. If you want to exclude more values, then using the AVG() and COLLECT() formulas together can help you get more options.

    Let us know if that helps, thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!