Getting a #divide by zero message on simple formula
Hi I'm getting a #divide by zero message when attempting a simple AVG formula below:
=AVG([Total Percentage Score]191:[Total Percentage Score]207)
The data is simply showing tasks that are 0% or 100%
its working ok as a formula on another sheet!
Answers

It sounds like the data in those cells is being stored as text. How exactly is the data being input? Are you manually entering the % symbol or are you entering a number into a text/number column that has been formatted for percentages?

Hi Paul,
thanks for getting in touch.
I have a text column with yes or no answers that feeds another column (Subject Score) with this formula below and this works fine
=IF(Compliant@row = "Yes", "100%", IF(Compliant@row = "No", "0%"))
I then have resulting values in this column as 0% or 100% value and then this is the average % percentage I'm attempting to get if that makes sense. Its as shown below in my sheet

Having the quotes around the numbers is outputting text. Format the column containing the formula to percentages and then use this formula instead:
=IF(Compliant@row = "Yes", 1, IF(Compliant@row = "No", 0))

ahhh school boy error there!
Thanks Paul that's done the job. Hope you have a good weekend.
kind regards
Craig

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!