DIV/0 Error Help

I exported my worksheet to Excel from Qualtrics after finishing with data collection from my survey. I then proceeded to calculate the mean score from certain participants' results (using AutoSum > Average) and would constantly get the DIV/0 Error, despite there being no zeroes or blanks in my selected cells. For example the selected cell contents would be 3, 3, 4, and the cell depicting the average would still be #DIV/0.

If I clicked on one of these cells, the number allignment would change from left to right and AutoSum would work. Problem is, it would be incredibly time consuming to manually do that for all the results from the 80 participants.

I was hoping someone here knew what caused this and how to fix it.

I cannot provide the actual worksheet, since it contains confidential data from my participants, but I also do not know how to recreate this problem in a different worksheet to present it here. If someone has an idea feel free to inform me.

The shift from left to right alignment suggests that you have numbers stored as text. Many functions (like average) are designed to ignore text (including numbers stored as text). In the case of the AVERAGE() function, if all of the entries are text, the result is 0/0 which means a divide by zero error.

You did not explain in detail how you are exporting from Qualtrics and importing into Excel (via a text type of file, maybe??). My first thought would be to look at that process and see if there is anything you can do differently so the results will be numbers in Excel.

This helpfile has some common strategies for converting numbers stored as text to numbers: https://support.microsoft.com/en-us/...1-c5bad0f0a885

I am not familiar with Qualtrics, so I do not know exactly how it exports data, and I am having trouble visualizing exactly what you are getting when that imports into Excel. I think we will need an example of the text file (or whatever) that Qualtrics is exporting and what that is looking like for you when you import it into Excel. Can you upload a sample text file so we can see what you are trying to work with?

Glad it helped.