#DIVIDE BY ZERO Error

Hello -

I get this #DIVIDE BY ZERO error when I try to take the average on multiple criterias. Here is my formula:

=AVG(COLLECT([Target Date Variance]:[Target Date Variance], [Design Team]:[Design Team], "Experience Design", [Design Team]:[Design Team], "Prism Core", [Design Team]:[Design Team], "Platform Vertical"))

I took the average on the column itself and got an actual number. What am I doing wrong?

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @MelissaTrinh

    It looks like you want to Average the one column if three possible options are selected in another column, is that correct?

    If so, you'll want to only list the second column once, then use the OR function to list your multiple options, like so:

    =AVG(COLLECT([Target Date Variance]:[Target Date Variance], [Design Team]:[Design Team], OR(@cell = "Experience Design", @cell = "Prism Core", @cell = "Platform Vertical")))


    If this still gives you an error, this means that the formula is either unable to find matching content for any of those three values (so make sure everything is spelled correctly), or there is an error in one of the columns you're referencing and it's being displayed in this formula as well.

    Let me know if this helped!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @MelissaTrinh

    It looks like you want to Average the one column if three possible options are selected in another column, is that correct?

    If so, you'll want to only list the second column once, then use the OR function to list your multiple options, like so:

    =AVG(COLLECT([Target Date Variance]:[Target Date Variance], [Design Team]:[Design Team], OR(@cell = "Experience Design", @cell = "Prism Core", @cell = "Platform Vertical")))


    If this still gives you an error, this means that the formula is either unable to find matching content for any of those three values (so make sure everything is spelled correctly), or there is an error in one of the columns you're referencing and it's being displayed in this formula as well.

    Let me know if this helped!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Hi there!

    Yes, that did the trick - thank you so much!!

    Thanks,

    Melissa

  • No problem! I'm glad I could help.

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!