#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?
Best Answer
-
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
Answers
-
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
-
Hi there!
Yes, that did the trick - thank you so much!!
Thanks,
Melissa
-
No problem! I'm glad I could help.
Help Article Resources
Categories
Check out the Formula Handbook template!