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

Sure I used the IFS formula to group Points under Ratings (I have attached a picture if that may help).

The function reads

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

Answer 4

That makes sense haha, thank you for your help!!!

Answer 5

Glad I could help. Take care...