+ Reply to Thread
Results 1 to 6 of 6

Average if range not blank

  1. #1
    Registered User
    Join Date
    12-02-2008
    Location
    London
    Posts
    51

    Average if range not blank

    Hello, quick question please could you tell me how to average a range if range not blank. If range is blank then I would like to return a blank.
    many thanks,
    A

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,779

    Re: Average if range not blank

    You could do something like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    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


  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,867

    Re: Average if range not blank

    Or maybe:
    Please Login or Register  to view this content.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    12-02-2008
    Location
    London
    Posts
    51

    Re: Average if range not blank

    Many thanks, both.
    A

  5. #5
    Registered User
    Join Date
    12-02-2008
    Location
    London
    Posts
    51

    Re: Average if range not blank

    And a follow up question if I may. I am averaging a range that is populated via index match. the formula I am using is an array like this:

    =IFNA(INDEX(J10:J13,MATCH($A$13:$A$16,$H$10:$H$13,0)),"")

    How do I prevent the average function I am using (thanks Willem) calculating on the blank "". At present it is counting the empty cells as 0.
    Many thanks,
    A

  6. #6
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    Re: Average if range not blank

    Hi Adame,
    To ignore 0 (zero), try: =IF(COUNT(A1:A10)=0,"",AVERAGEIF(A1:A10,"<>0"))
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

+ 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] Average Based on Finding The Most Recent Non Blank, Positive Values In Range
    By rlmh06 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-14-2018, 05:52 PM
  2. Average number of Blank Cells between entries in a Range
    By skydavis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2016, 11:27 AM
  3. Replies: 10
    Last Post: 04-25-2013, 06:12 PM
  4. average formula for non contiguous range and eliminate blank cells
    By kkotter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2013, 10:13 AM
  5. Replies: 3
    Last Post: 04-09-2012, 02:53 PM
  6. Replies: 6
    Last Post: 03-05-2012, 04:46 PM
  7. Average range including blank cells: #DIV/0!
    By Areaka in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2006, 05:21 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