DIV/0 problem

[hbgray]

i'm doing an average =AVERAGE(B1:B27) and it's giving me #DIV/0!

any ideas what is wrong?

 

[Tazguy37]

Are all the cells in the range blank or zero? That would do it.

 

[hbgray]

36     
38     
38.75  
39     
39     
39     
39     
39     
40     
40     
41     
41     
41.5   
41.5   
42     
42     
42     
42     
43     
43.5   
44     
44     
46     
46     
47     
48     
48.1   
those are the cells, that's why it is so wierd--I can do it by hand but I would like to figure this out if I can...or any of you can

 

[Chitosunday]

Is the number formatted as text?

 

[hbgray]

they were but I changed it (range and the formula) and it still didn't help

 

[Chitosunday]

the formula should be array formula with {} (by pressing ctrol shift enter after inputting the formula but before exiting in the cell)

{=AVERAGE(VALUE(B1:B27))}

 

[hbgray]

when I did that with the value in the formula it gave me #VALUE! and #DIV/0! without it

 

[Chitosunday]

CAn you email to me your file so i can check what is wrong

 

[Chitosunday]

I see you have peculiar space in your number
=AVERAGE(IF(B1:B27<>"",VALUE((SUBSTITUTE(B1:B27,CHAR(160),"")))))