Getting a #divide by zero message on simple formula

CAS

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!

Paul Newcome

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?

CAS

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

Paul Newcome

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))

CAS

ahhh school boy error there!

Thanks Paul that's done the job. Hope you have a good weekend.

kind regards

Craig

Paul Newcome

Happy to help. 👍️