Ignore #Dividebyzero

Hi, 

I have a range of percentages and want to calculate the average. There are some #DIVIDEBYZERO cells within the range that will populate later on in the year. Is there a way to tell Smartsheet to ignore the #DIVEBYZERO? It is possible in Excel using an AverageIF formula. But I can't see how to do it in Smartsheet. 

Capture.JPG

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You can use the =ISERROR formula to return whatever you want when there is an error. 

    https://help.smartsheet.com/articles/2476176-formula-error-messages#dividebyzero

    https://help.smartsheet.com/function/iserror

  • L_123
    L_123 ✭✭✭✭✭✭

    or you can use =iferror(formula, "post this if error, usually a 0 or double quotes for blank")

  • Is there a way I can use this and also tell the formula to display the average of the cells that do not have an error within them?

  • L_123
    L_123 ✭✭✭✭✭✭

    use the iferror where you are getting the #Divide By Zero issue not in your average formula. 

     

    Iferror(Current formula,"")

     

    if there is an error the formula will output a blank. If there isn't an error your original formula will run. Then the average function you currently have will be correct, and you will remove the visible errors on your sheet.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Whatever formula you are using to get your average, put it within the =ISERROR brackets. 

    =ISERROR(Put your formula here, 0)

    That will give you a 0 where there is an error.