Desire blank output instead of #Divide by Zero

sshariati
sshariati
edited 01/30/24 in Formulas and Functions

Hi, I am using values from different sheet to calculate duration. However, the other sheet does not have values at all time, it may be blank, 0 or output an error. How can output a blank field when my formula comes across such a field.

current formula that works fine with fields that DO have values:

=AVG(COLLECT({Incident Duration}, {Month}, "December", {Calculations Range 3}, 2022))

Using this same formula, I'd also like to add some "error if" statment that takes into consideration the following values in the other page:

It can be smthg like, If incident duration is blank, then output blank in =AVG(Collect).. field

I attempted this but does not work:

=AVG(COLLECT({Incident Duration}, {Month}, "February", {Calculations Range 3}, 2023, IFERROR({Incident Duration}, " ", " ")))

I want to incorporate the IFERROR within the same AVG(COLLECT) statement above

Answers

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭

    Hi sshariati,

    You can apply IFERROR function as below in your formula.

    =IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "December", {Calculations Range 3}, 2022))," ")

    Thank you!

    Anjanesh Vaidya

  • sshariati
    sshariati
    edited 01/31/24

    Thank you! This worked!

    How can I add additional months? I'd like to use this formula or entire column and associated months and years that are in other columns

    I tried this and it doesn't work:

    =IFERROR(AVG(COLLECT({Incident Duration}, {Month}, "November", {Calculations Range 3}, 2021)), (AVG(COLLECT({Incident Duration}, {Month}, "December", {Calculations Range 3}, 2021)))," ")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!