Why is =AVERAGE returning a divide by zero error in this scenario?

Question

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.

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:

=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(...))