+ Reply to Thread
Results 1 to 4 of 4

Using Average and ignoring cells with #div/0

  1. #1
    Registered User
    Join Date
    10-13-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    2010
    Posts
    99

    Using Average and ignoring cells with #div/0

    Hello,
    I've attached an image below but I'm not sure you can see it. Jan - Dec and YTD average are in cells I5 to I17 and their corresponding values are in cells J5 to J17.

    I need to average the column of twelve monthly percentage scores. The problem I'm having is that the cells all pull their own values from other cells on the same sheet. The values are averages of each month, Jan, Feb, etc. Those other cells are currently blank. For example, lets say this is March 2015. I have scores for January, February, and March but April to December are all empty so the values in those cells are all #div/0. How do I calculate a year to date average for the year while ignoring the cells with #div/0?

    Thanks for your help
    Attached Images Attached Images

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,085

    Re: Using Average and ignoring cells with #div/0

    can you put an error correction into the cell that has a div/0!
    whats the formula in month average thats causing the error ?
    perhaps an IFERROR( )
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    10-13-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    2010
    Posts
    99

    Re: Using Average and ignoring cells with #div/0

    I've solved my own thread!

    In cell J5 (and J6 through J16, slightly differently) I have formula

    =IF(SUM(B4:B23)=0,0,AVERAGE(B4:B23))

    This returns zero if B4:B23 is blank or zero and it gives me the average for that area otherwise.

    In cell J17 I have formula

    =AVERAGE(B4:B23,D4:D23,F4:F23,B25:B44,D25:D44,F25:F44,B46:B65,D46:D65,F46:F65,B67:B86,D67:D86,F67:F86)

    This gives me the average of all of the scores in the above fields.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Using Average and ignoring cells with #div/0

    This might be what you are looking for. The picture doesn't show what cells are involved but this may be adapted. This averages B2:B10 and ignores errors.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Sumproduct Average ignoring blank cells
    By fgruhlke in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2014, 12:32 PM
  2. Average Ignoring Zero Values - Non-consecutive cells
    By a7mad3wies in forum Excel General
    Replies: 2
    Last Post: 12-05-2013, 02:13 AM
  3. Getting average but ignoring cells with blank (#value!)
    By Elainefish in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-23-2013, 08:30 AM
  4. Average ignoring Blanks cells
    By Justinmih in forum Excel General
    Replies: 10
    Last Post: 09-30-2011, 06:21 PM
  5. [SOLVED] 30 Day Moving Average Ignoring Blank Cells
    By ethatch in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2006, 05:40 AM

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