Ignore #Dividebyzero

08/23/18 Edited 12/09/19


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. 




  • Mike WildayMike Wilday ✭✭✭✭✭

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



  • L_123L_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_123L_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 WildayMike 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. 

Sign In or Register to comment.