Divide by Zero Message and IFERROR formula

Options

I am getting a Divide by Zero Message when putting in this formula. It is correct because there are no items that fit this criteria to average.

=AVG(COLLECT({Credit Granting Article Tracker Compliance}, {Credit Granting Article Tracker Month Completed}, "2", {Credit Granting Article Tracker Major?}, "No"))

I attempted to add a IFERROR formula but I am not sure where to add it or how to get it to work. If I add it at the beginning, it comes back as #INCORRECT ARGUMENT.

=IFERROR(AVG(COLLECT({Credit Granting Article Tracker Compliance}, {Credit Granting Article Tracker Month Completed}, "2", {Credit Granting Article Tracker Major?}, "No", "-")))

Am I putting the IFERROR in the wrong place or is there a better way to do this?

Any help with this would be greatly appreciated!!

Kristie Diersen 😀

Tags:

• ✭✭✭✭
Options

Hi Kristie,

So if I understand correctly, in the event that the first formula results in a divide by zero error, you'd like the formula to return "-" correct? If so, the problem with your formula is you need to close the parenthesis for both the AVG and Collect functions after "No", and not at the end. Here is an updated version of your formula that should work.

=IFERROR(AVG(COLLECT({Credit Granting Article Tracker Compliance}, {Credit Granting Article Tracker Month Completed}, "2", {Credit Granting Article Tracker Major?}, "No")), "-")

Hope this helps! Let me know if you have any questions.

Best,

Mike

• ✭✭✭✭
Options

Hi Kristie,

So if I understand correctly, in the event that the first formula results in a divide by zero error, you'd like the formula to return "-" correct? If so, the problem with your formula is you need to close the parenthesis for both the AVG and Collect functions after "No", and not at the end. Here is an updated version of your formula that should work.

=IFERROR(AVG(COLLECT({Credit Granting Article Tracker Compliance}, {Credit Granting Article Tracker Month Completed}, "2", {Credit Granting Article Tracker Major?}, "No")), "-")

Hope this helps! Let me know if you have any questions.

Best,

Mike

• Options

That worked. I had a feeling it was a simple error. Thank you so much!

Kristie Diersen 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!