Average function showing #DIV/0!

Question

Hi, 

I have a column of numbers I have calculated using the IFs function, which I have labelled as “Ratings”, however when I try to use the AVERAGE function of the “Ratings” column, I get #DIV/0!

I have tried to copy and paste only the values in the neighbouring column, yet I still get the same results. I have also tried to simply use the SUM function of the column and I get a figure of 0, the same also happened when I tried to use the COUNT function. 

The column of data that I am trying to find thte average of are all numerical so I am confused as to why the functions are not working.

Answer 1

So You ;-) generated the Text values with the double quotes around 5, 4, 3.... Your formula should say:

=IFS(C2>96,5,C2>92,4,C2>88,3,C2>84,2,C2>80,1)

Hope this helps

Answer 2

Hi

Your values are stored as Text values, no doubt. Assuming your values are in column A starting row 1, in another column enter formula: =VALUE(A1) and copy down

Then do a SUM, COUNT, AVERAGE of that new column. Works better?

If not please upload & share your workbook (i.e. with OneDrive)

Answer 3

My guess would be that Excel for some reason sees the values as text even though they look like numbers.

Do the following:

• Make sure that the number format is NOT set to Text, but to General, Number, Currency, Accounting or Percentage.
• Select the data in a column.
• On the Data tab of the ribbon, click Text to Columns.
• Without changing anything, click Finish.

Does that make a difference?

Answer 4

Hi,

Make sure that no cell in that column returns an error.

Answer 5

The column is set to general and it still shows #DIV/0!

I’m not sure why the functions are not recognising the numbers.

Thanks for trying!