Excel division by 0 error when trying to average results of formulas
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))}