# Excel division by 0 error when trying to average results of formulas

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:

`=IF(B3>=0.8,"5",IF(B3>=0.7,"4",IF(B3>=0.6,"3",IF(B3>=0.5,"2","1"))))`

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

`=AVERAGE(C3:C30)`

What is the problem here?

• 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

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

Change your formula to:

``````=IF(B3>=0.8,5,IF(B3>=0.7,4,IF(B3>=0.6,3,IF(B3>=0.5,2,1))))
``````

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:

``````{=AVERAGE(VALUE(B1:B4))}
``````

N.B.

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

`{=AVERAGE(VALUE(C3:C30))}`

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