6

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?

1
  • 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

9

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.

8

An Array Formula will solve your problem:

enter image description here

{=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))}

1
  • + 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 .