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.
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
- 63K Get Help
- 380 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 306 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!