I have a column of percentages(column B), and another column next to it(column C) that calculates grades from the percentages using this formula:


When I try to average all the grades(column C) I get a division by 0 error. Formula used to average:


What is the problem here?

  • 2
    BTW your formula could be simplified to: =MATCH(B3,{0,0.5,0.6,0.7,0.8}) Jun 7, 2019 at 17:57

2 Answers 2


The problem is you are trying to take an average of text strings.

Change your formula to:


A number wrapped in quotes like "4", is not actually a number, but rather text, you cannot take an AVERAGE of text values.

You can do a simple test to see if a number is actually a number. In D2 use =ISNUMBER(C2) if it returns TRUE, then it is a number.


An Array Formula will solve your problem:

enter image description here



  • Finish this Formula with Ctrl+Shift+Enter.
  • VALUE Converts Text data into Numbers.
  • Your Formula should written like


  • + for use of VALUE, never come across this before
    – PeterH
    Jun 7, 2019 at 11:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .