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
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 467 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!