+ Reply to Thread
Results 1 to 5 of 5

AVERAGEIF with array parameter?

  1. #1
    Registered User
    Join Date
    07-18-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    AVERAGEIF with array parameter?

    I have three columns, "type," "hours," and "quantity"

    I am trying to average the product of the hours and quantity if the type matches inputted criteria.

    I thought I could do this, but no success:
    {=AVERAGEIF(F:2:F:8, A1, {G2:G8 * H2:H8})}

    I know I could create another column with the product and then run AVERAGEIF on that column, but with my application I really would like to do it in one pass. Any ideas? Thanks!

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: AVERAGEIF with array parameter?

    Try

    =SUmproduct(--(f2:f8=A1),g2:g8,h2:h8)/SUmproduct(--(f2:f8=A1))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    07-18-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: AVERAGEIF with array parameter?

    Thanks Ace, that got it! I added the hour parameter to the second SUMPRODUCT.

    I'm not familiar with the --() notation, what exactly does that do?

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: AVERAGEIF with array parameter?

    glad to help

    I'm not familiar with the --() notation, what exactly does that do?
    See here
    http://www.xldynamic.com/source/xld....ml#performance

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: AVERAGEIF with array parameter?

    If I understand, try this array formula**:

    =AVERAGE(IF(F2:F8=A1,G2:G8*H2:H8))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] array in parameter
    By tibibs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2012, 04:19 AM
  2. [SOLVED] AVERAGEIF Formula on an array of data
    By Shoto in forum Excel General
    Replies: 2
    Last Post: 04-19-2012, 02:20 AM
  3. opentext, array parameter
    By HarryKlein in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2012, 07:59 AM
  4. [SOLVED] SOS-How to pass array parameter to Workbooks.OpenText(...) in VC++
    By Lily in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2010, 04:28 AM
  5. [SOLVED] ReDim Object array as parameter of Variant array
    By Peter T in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-10-2005, 10:06 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