+ Reply to Thread
Results 1 to 4 of 4

AVERAGEIF function using reference as criteria range

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    2

    AVERAGEIF function using reference as criteria range

    I am trying to use the AVERAGEIF function to give calculate averages based on dynamic data. The formula I want to work is:

    Please Login or Register  to view this content.
    but this returns the "#DIV/0!" error.

    However,

    Please Login or Register  to view this content.
    returns the correct value.

    I want to be able to calculate averages of only the cells that are greater than the value of cell D6. Is there a way to do this?

    Thanks in advance.

    -Andy
    Last edited by AndySan; 12-05-2013 at 05:21 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: AVERAGEIF function using reference as criteria range

    Try
    =AVERAGEIF(J9:J27,">"&D6,L9:L27)

  3. #3
    Registered User
    Join Date
    12-05-2013
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: AVERAGEIF function using reference as criteria range

    Wow, that works! Evidently I need to work on my formula syntax.

    Much thanks.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: AVERAGEIF function using reference as criteria range

    Don't worry about it, I wouldn't call that a "you should have know that" kind of thing.
    It's a little bit of an oddity. I don't think even the help files mention it.

    But it's the kind of thing you'll remember, you only have to learn it once.

+ 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. AverageIf based on different criteria
    By caseman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2013, 02:18 PM
  2. [SOLVED] Using COUNTIF Function with Reference to a Range for Criteria
    By PosseJohn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2013, 02:30 AM
  3. Replies: 2
    Last Post: 08-30-2012, 09:25 AM
  4. Excel 2007 : AVERAGEIF range function
    By Jerseynjphillypa in forum Excel General
    Replies: 1
    Last Post: 07-16-2012, 10:45 AM
  5. Criteria question - AverageIF (if you please)
    By djalexr in forum Excel General
    Replies: 3
    Last Post: 01-14-2011, 08:00 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