+ Reply to Thread
Results 1 to 14 of 14

Getting a #div/0! error using =AVERAGEIF

  1. #1
    Registered User
    Join Date
    02-05-2016
    Location
    Richmond
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    7

    Getting a #div/0! error using =AVERAGEIF

    Hello,

    I am using excel to track inventory for a small business. I am building out a few pages for each category of inputs and have a sheet dedicated to collecting the usage/month that pulls the sums from the input specific sheets. On my dashboard page (which houses current levels, and other high level information) I want to have a cell that averages the historic monthly demand. Due to the way the monthly sums are set up I have it looking for information from January to December, however as it is only February, there are 10 months that have 0 in the current monthly demand.

    Because of this, I am using the formula =AVERAGEIF('Month Sums'!B2:B13,">0",'Month Sums'!B2:B13). Where Month Sums is the sheet where the month sums is, B2:B13 is the monthly Sum of product usage (those cells consist of the formula =SUMIF('Coffee Beans'!$D:$D,month number,'Coffee Beans'!$B:$C) ). So i want to have the average of all the non 0 numbers from the monthly sums page.

    However I am recieveing a #div/0! error on the averageif formula. is this because i have it averaging a formula rather than a number?

    Thanks

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Getting a #div/0! error using =AVERAGEIF

    Looks like there are no values in the range B2:B13 that are > 0.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    02-05-2016
    Location
    Richmond
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    7

    Re: Getting a #div/0! error using =AVERAGEIF

    Thanks for getting back to be Special-K

    The values in b:12:B13 are bellow

    880.00
    300.00
    0.00
    0.00
    0.00
    0.00
    0.00
    0.00
    0.00
    0.00
    0.00
    0.00

    I would like the formula to average January (880) and Febrauary (300) and then add in March once it has information.

    The 880 is populated by the formula =SUMIF('Coffee Beans'!$D:$D,1,'Coffee Beans'!$B:$C)
    300 by =SUMIF('Coffee Beans'!$D:$D,2,'Coffee Beans'!$B:$C)

    This led to my development of the =AVERAGEIF('Month Sums'!B2:B13,">0",'Month Sums'!B2:B13) formula in the hopes that it would average the non zero numbers.

    Can I not set the criteria range the same as the average range?

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Getting a #div/0! error using =AVERAGEIF

    Your formula should work. Can you upload an example where it is not working? (Go Advanced>Manage Attachments)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    728

    Re: Getting a #div/0! error using =AVERAGEIF

    You may check wher the error is by evaluating the formula step by step

    Use Evaluate Formula under the Formula Tab

    or use sumif and countif function to see where the error is

    Regards
    Last edited by mahju; 02-05-2016 at 01:13 PM.
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  6. #6
    Registered User
    Join Date
    02-05-2016
    Location
    Richmond
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    7

    Re: Getting a #div/0! error using =AVERAGEIF

    The average if formula is on the Dashboard tab. The Sum formula is on the monthly sum sheet.

    Inventory for help.xlsx

  7. #7
    Registered User
    Join Date
    02-05-2016
    Location
    Richmond
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    7

    Re: Getting a #div/0! error using =AVERAGEIF

    I am on a mac, and am not seeing the formula tab that says evaluate formula

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Getting a #div/0! error using =AVERAGEIF

    Hmmm, opened up with a calculated 590 right there in F2. Has to be a MAC thing. I'll see if I can find a MAC guru.

    Last question, if you double click in F2 of dashboard and then enter, it still reads as an error?

  9. #9
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    728

    Re: Getting a #div/0! error using =AVERAGEIF

    I am sorry but I cannot see any div /0 error
    the formula is calculating 590 in cell F2
    Thanks

  10. #10
    Registered User
    Join Date
    02-05-2016
    Location
    Richmond
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    7

    Re: Getting a #div/0! error using =AVERAGEIF

    I am glad to know that the formula is built correctly!

    So i wonder if it is a mac thing. I have included a screenshot so you know I am not crazy!Screen shot 2016-02-05 at 1.49.15 PM.png

  11. #11
    Registered User
    Join Date
    02-05-2016
    Location
    Richmond
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    7

    Re: Getting a #div/0! error using =AVERAGEIF

    Correct, Double clicking and then hitting enter still results in an error

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Getting a #div/0! error using =AVERAGEIF

    Hi Ben

    The formula calcs correctly in Mac Excel 2011.

    If you Google the issue there's a strong inference it was ADDED in Mac Excel 2011.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  13. #13
    Registered User
    Join Date
    02-05-2016
    Location
    Richmond
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    7

    Re: Getting a #div/0! error using =AVERAGEIF

    Thanks John.

    I uploaded it to a google sheet and it works great!!!!

    Thanks for all of the help, and I appreciate all of the insights I was given.

    Ben

  14. #14
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Getting a #div/0! error using =AVERAGEIF

    You're welcome...glad I could help.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ 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] Averageif help
    By Lewster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2015, 03:54 PM
  2. [SOLVED] averageif returning #div/0! error now
    By BennyH in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2013, 01:26 AM
  3. Sumif/Averageif error linking outside workbook
    By kcasey1318 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-31-2013, 06:25 PM
  4. [SOLVED] AverageIF Error vs Raw Data
    By Hudson in forum Excel General
    Replies: 1
    Last Post: 09-28-2012, 11:58 AM
  5. AVERAGEIF: can't get around #DIV/0! error
    By dadowai in forum Excel General
    Replies: 6
    Last Post: 07-04-2012, 03:36 PM
  6. AverageIF
    By graybush in forum Excel General
    Replies: 1
    Last Post: 06-14-2012, 09:18 PM
  7. Averageif for cells in different position having error value
    By jrammb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2010, 02:38 PM

Tags for this Thread

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