Sample Spreadsheet

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.

1 Answer 1


This is because SUBSTITUTE transforms number to string or text.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.