+ Reply to Thread
Results 1 to 10 of 10

AVERAGEIFS RETURNING #DIVO! message

  1. #1
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    AVERAGEIFS RETURNING #DIVO! message

    not sure why this is happening> any thoughts?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: AVERAGEIFS RETURNING #DIVO! message

    Your percentages in column E are numbers stored as text. If you convert them to numbers, you will eliminate the divide by 0 error.

    Edit: FYI, the same is true of columns F and G.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: AVERAGEIFS RETURNING #DIVO! message

    Hi,

    Perhaps your data has been imported from some external source, without further modification?

    In any case, the reason is that your entries in column E are not being recognised as numerics.

    One quick way to correct this issue:

    Highlight that column, go to Text to Columns (Data tab) and click Finish immediately (without making any prior selections).

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: AVERAGEIFS RETURNING #DIVO! message

    The data in column E is numbers stored as text. Use Text to Columns to convert it to true numbers and it will be fine.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    05-04-2019
    Location
    London, England
    MS-Off Ver
    2018
    Posts
    20

    Re: AVERAGEIFS RETURNING #DIVO! message

    The issue is with % numbers being Text. Convert them to numbers by highlighting and right clicking.

    One other thing you shouldn't ever average a %

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: AVERAGEIFS RETURNING #DIVO! message

    Quote Originally Posted by Melchizedek View Post
    One other thing you shouldn't ever average a %
    Pardon ?
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    05-04-2019
    Location
    London, England
    MS-Off Ver
    2018
    Posts
    20

    Re: AVERAGEIFS RETURNING #DIVO! message

    Sorry I am lazy so I did just copy paste this.

    A common mistake I often observe in data analysis is the averaging of percentages. To explain this concept let’s examine a simple example.

    Suppose we conduct a survey on whether people like or do not like chocolate. We discover that 90% of children like chocolate; however, only 60% of adults like chocolate. So, can we claim that 75% of the population ( average = (90%+60%)/2 = 75% ) like chocolate? The answer is NO! There is one exception which we will discuss at the end of this post.

    The reason this is not correct is because we do not know anything about the sample size. Let’s provide more information. In the table below we learn that there are 100,000 children and 400,000 adults surveyed. From those, 170,000 people do not like chocolate, while 330,000 like chocolate. Here, we can confirm that 90,000/100,000x100% = 90% of the children, and 240,000/400,000x100% = 60% of adults like chocolate.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: AVERAGEIFS RETURNING #DIVO! message

    Right; but there are lots of circumstances where you can. Just to find a quick example so I don't have to make one up, from mathforum.org:

    For example, if there are 50 questions on an exam, and three students got 20%, 30%, and 40% of them right, then the average number of questions they got right is 30%, or 15 questions.
    Percentages are just numbers and can be averaged like any other. Averaging may or may not be the solution method for a particular problem.

  9. #9
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: AVERAGEIFS RETURNING #DIVO! message

    Thanks guys for all the help. Yes, I am exporting this from access into excel. How can I set the export so that the numbers come in as numbers and not text? Or do I just need to convert every time.

  10. #10
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: AVERAGEIFS RETURNING #DIVO! message

    you either export from a query in access that converts the field to calculated values that are numeric (although why are they stored at text in the first place)

    possibly look at the val function in access

    or you write some code to convert them on opening excel, or do as the solutions above. the best solution is to export the data in the correct format

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] AVERAGEIFS returning #DIV/0!
    By MoldyBread in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-22-2019, 08:30 AM
  2. [SOLVED] Averageifs function not returning correct average
    By CSherman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2017, 11:11 PM
  3. Replies: 3
    Last Post: 07-09-2016, 09:14 AM
  4. Need help with averageifs function - keeps returning #DIV/0
    By JAWilliams in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2015, 01:38 AM
  5. [SOLVED] Averageifs returning #DIV/0 error
    By kbotta in forum Excel General
    Replies: 8
    Last Post: 10-09-2014, 08:38 AM
  6. [SOLVED] Averageifs returning DIV/0 errors even though there are values
    By lifeseeker1019 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2013, 02:19 AM
  7. Averageifs returning Value?
    By katja328 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2009, 06:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1