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
Help Article Resources
Categories
Check out the Formula Handbook template!