Divide by zero error - how to find solution

[Jonathan Lankford]

I am trying to work a very simple spreadsheet for student averages. Some columns have text, but I know that the average function is supposed to ignore text. When I use the average function of =AVERAGE(B2:H2), it gives me the #DIV/0! error. So I rewrote the formula to only include the columns with numbers. I got the same result. Then I started over with the formula and changed the cell types of the one with text to "text" and the cell types of the ones with numbers to "number" because the cell types were originally all "general." I still get the error. Am I missing a 0 somewhere? Do I have bad eyesight? Is there a way to evaluate whether it is a software error? This seems like such a simple function to have an error on.

Image of desktop:

https://onedrive.live.com/redir?res...authkey=!APHpdUpvIozRu-g&v=3&ithint=photo,png


Thanks.

 

[XOR LX]

Hi.

If those values have been entered as text values, then simply changing their cell format to Number will not render the values as such.

To convert them to numerics, one way is:

1) Enter the number 1 in a random cell somewhere
2) Copy that cell
3) Highlight the range of cells for which you wish to perform the conversion to numerics
4) Choose Paste Special/Multiply

Regards

 

[alansidman]

Unable to recreate your error. Suggest you post your actual worksheet instead of an image for analysis.

 

[Jonathan Lankford]

XOR LX,

Fixed!

Thanks for the quick reply and your assessment of the problem, which was right by the way. But the process to fix the problem you suggested didn't quite work for me. However, I copied and all the columns in the spreadsheet that I wanted to average and pasted them to another sheet. When I did that, every numbered cell showed the error that it was designated as text instead of a number. (So simply changing the cell style on the ribbon at the top didn't actually change it to a number cell; but you already told me this.) I selected them all, then clicked on the error pop-up and selected fix all. Then I copied all of those once more and pasted them back into my sheet.

That's strange that I couldn't do this without a workaround like that. Anyway, thanks so much.

 

[XOR LX]

Not sure why my workaround didn't work for you, but sounds like you've found a nice alternative solution.

And yes, good point re the Number Stored as Text error pop-up. Not sure why you didn't get some of those in your original sheet. Perhaps you chose to ignore them?

Glad you've got it sorted anyway.

Cheers