Share via

Average function showing #DIV/0!

Anonymous
2021-02-25T22:37:12+00:00

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.

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Lz._ 38,106 Reputation points Volunteer Moderator
    2021-02-26T16:41:09+00:00

    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

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. Lz._ 38,106 Reputation points Volunteer Moderator
    2021-02-26T13:22:58+00:00

    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)

    1 person found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-02-26T13:04:17+00:00

    No cell returns as an error

    0 comments
  2. Anonymous
    2021-02-26T14:33:40+00:00

    Yes this worked! Thank you!!! I’m not sure why they are stored as text values or how to get rid of that but this works too, thanks!

    0 comments
  3. Lz._ 38,106 Reputation points Volunteer Moderator
    2021-02-26T14:59:30+00:00

    Glad we could help and thanks for posting back

    Re. I’m not sure why they are stored as text values => Post your IF formula as it might be the root cause...

    0 comments