Google Sheets – Why =AVERAGE Returns Divide by Zero Error

google sheets

I get a divide by zero error when trying to =AVERAGE the output of a =SUBSTITUTE function in two cells. When I don't use the =SUBSTITUTE function, but instead enter the desired result manually, I don't get the error. I don't understand why.

Best Answer

This is because SUBSTITUTE transforms number to string or text.

To convert it back to number, You could use any of the below methods:

=SUBSTITUTE(C2,"%","")*1

Just multiply(or divide) by 1 as shown above: *1
Add(or -) a zero: +0
Use a Double unary operator: --
(For Ex: --SUBSTITUTE(...))

Related Solutions

Google Sheets – Sum of Two Numbers with a Minimum of Zero

You can use the MAX function for this, since

MAX( A3+B3, 0 )

will return 0, when the sum is negative.

Google Sheets – Get Average Function for Accumulative Input

This is another solution.

Formula

=INT(E4/COUNTBLANK(B2:B))

Explained

The COUNTBLANK formula will count the blank cells, independent of the range and E4 will be divided by it. The INT formula will simply round the value to the nearest integer, since it concerns days.

Example

I've created an example file for you: Count Blank Cells