Ignore #Dividebyzero
![wilsondan94](https://us.v-cdn.net/6031209/uploads/drupal_profile/files/2018-02/fe/65/nfe65d633e3b076a93a2af2943fc7b33f.jpg)
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.
Comments
-
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
-
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?
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!