+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Problem With #division/0!

  1. #1
    Registered User
    Join Date
    10-25-2010
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    10

    Problem With #division/0!

    First of all, i am quite new to excel (been using it for two weeks now), and i am creating a template, where i can put some data in and it calculates and so on. It's going really well except that in some situations keep running into the #division/0! error.

    My formula looks like this:
    =MIDDEL.HVISER('1'!H96:H160;'1'!F96:F160;">21";'1'!H96:H160;">0";'1'!F96:F160;"<6";'1'!F96:F160;">0")

    Middel.hviser() is the danish equal to average.if.

    as you can see i have written that the data has to be >0 in both columns. And thats why it puzzles me that #division/0 keeps returning.

    sorry if this is a little hard to understand, as i am new to excel, my way around the right terms is a little hard.

    Thanks
    Kristoffer

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,053

    Re: Problem With #division/0!

    Well, it still don't need to hold any value greater than 0...

    If you have range -2, -3, -1 AVERAGEIF(range, ">0") will return you error...

    So all criterias are does not met... >21, >0 or <6
    Last edited by zbor; 10-25-2010 at 04:15 AM.
    Never use Merged Cells in Excel

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem With #division/0!

    In your function the requirements state that F must be > 21 AND < 6 AND > 0 ... obviously no cell will meet these conditions.

  4. #4
    Registered User
    Join Date
    10-25-2010
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Problem With #division/0!

    I must admit im not quite sure what you mean, but i have no data which is below 0. I can attach my sheet if that would help?

  5. #5
    Registered User
    Join Date
    10-25-2010
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Problem With #division/0!

    A little more information on what i am trying to do.

    The cells in the F column is the hours of the day, and H is the bloodpressure at the different times a day. i am trying to get the average of the hours of the night therefore it needs to be >21 and <6, as this is what the hours of the night in the project is set as.

    DonkeyOte has a good point, but the question is now, how do i do it then?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem With #division/0!

    Quote Originally Posted by baek2
    i am trying to get the average of the hours of the night therefore it needs to be >21 and <6
    IMO you will struggle to do this using AVERAGEIFS given you need 2 separate AVERAGEIFS and averaging the results of each will not give you the correct result (ie won't weight) - you would need SUM(2 SUMIFs) / SUM(2 COUNTIFs)

    Given the relatively small dimensions in use you could use a single Average Array

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 10-25-2010 at 05:41 AM. Reason: edited per correction outlined in post below

  7. #7
    Registered User
    Join Date
    10-25-2010
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Problem With #division/0!

    With a little correction of some of the numbers (<6 instead of one of the >21) this worked perfectly, with the correct result, checked and double checked.
    Thank you very much Donkey, it was very useful, i have to look it through a few times to exactley know how it works and what it does, but as i am new i have very much to learn. But it surely was a big help

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Problem With #division/0!

    Quote Originally Posted by baek2 View Post
    With a little correction of some of the numbers (<6 instead of one of the >21) this worked perfectly
    apologies - I changed the order of the OR test but forgot to change the first test to <6 from >21 !
    I have since revised my prior post per your correction.

  9. #9
    Registered User
    Join Date
    10-25-2010
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Problem With #division/0!

    No reason for the apology. You helped me a lot, the simplicity of it is astonishing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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