+ Reply to Thread
Results 1 to 8 of 8

Average formula with filter for two criteria

  1. #1
    Registered User
    Join Date
    05-16-2017
    Location
    Brighton, England
    MS-Off Ver
    QuattroPro!
    Posts
    5

    Cool Average formula with filter for two criteria

    Hi,

    I have mastered the AVERAGE formula, including the specification of static criteria. For example, ($A$1..$K$5000,10,$E$1..$E$8) to lookup the block specified, to average numbers in the 10th column but to pick rows only where a column whose heading is denoted at E1 contains one of the values in E2 through E8. Here's an example row from the data block, where the value 107 in the 8th column matches the criteria:
    1445585069 41 1300689105 87.123.456.43 479 nz c 107 overall a 5

    This formula works for static values in the criteria block. I also need a formula that works for relative values. A summary sheet is to lay out averages of rows where, as well as the static criteria match, there is a match of both user number (in the first column below and corresponding to the 41 in the second column of the above example row) and also for a given supplier (in the top row below and corresponding to the 479 of the above example row).

    479 45 466 33 234 42 33

    41
    42
    43

    Googling, I see a tip for Google Spreadsheet. From that, I have come up with this syntax:
    =AVERAGE(filter($Rawdata:$A$1..$K$5000;($Rawdata:B=$A1)*($Rawdata:E=A$2),10,$E$1..$E$8))

    I have tried this (as @davg) in QuattroPro (which I have access to currently) - but I get "invalid reference". I'd really appreciate any guidance in getting this to work in QuattroPro or in Excel - which I can use on a colleague's machine!

    Many thanks,

    Andy

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Average formula with filter for two criteria

    Hi limegrove and welcome to the forum,

    I loved Quatro Pro when it first came out but Excel has been my standard for the last 20 years.

    I think you are needing to do a Control+Shift+Enter with your Excel formula. I have no idea if QPW has this capability. Read about CSE formulas at:

    http://www.excel4business.com/excel-...-formulas.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    05-16-2017
    Location
    Brighton, England
    MS-Off Ver
    QuattroPro!
    Posts
    5

    Re: Average formula with filter for two criteria

    Thanks MarvinP! That seems to have set me on the right path. I now have a formula which doesn't throw up any syntax error, although presently delivers an ERR (or a 0 where there are no matching rows) in place of the average value across the matched rows:

    @PUREAVG(@IF(($RawData:$B$14..$B$10000=$A5)*($RawData:$E$14..$E$10000=E$2),$RawData:$K$14..$K$10000,0))

    I will try to work through this to vouch and correct the formula.
    Last edited by limegrove; 05-16-2017 at 09:21 AM. Reason: Update re. ERR or 0 result

  4. #4
    Registered User
    Join Date
    05-16-2017
    Location
    Brighton, England
    MS-Off Ver
    QuattroPro!
    Posts
    5

    Re: Average formula with filter for two criteria

    [removed holding note]
    Last edited by limegrove; 05-16-2017 at 12:49 PM.

  5. #5
    Registered User
    Join Date
    05-16-2017
    Location
    Brighton, England
    MS-Off Ver
    QuattroPro!
    Posts
    5

    Re: Average formula with filter for two criteria

    Now in Excel, I've got the following to produce a value:
    =AVERAGE(IF($B$14:$B$10000=$A5,$K$14:$K$10000))

    I've also got multiple criteria to work, with:
    =AVERAGE(IF(($B$14:$B$10000=$A5),AND($E$14:$E$10000=E$2),$K$14:$K$10000))

    No syntax errors, but the math is not right. Each delivers the average of all values in the K14:K10000 range, not the average of those rows which match the specified criteria.

    Can anyone see where I've gone wrong, please?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Average formula with filter for two criteria

    Try

    =AVERAGE(IF(($B$14:$B$10000=$A5)*($E$14:$E$10000=E$2),$K$14:$K$10000))

    Look at AVERAGIFS function.

  7. #7
    Registered User
    Join Date
    05-16-2017
    Location
    Brighton, England
    MS-Off Ver
    QuattroPro!
    Posts
    5

    Re: Average formula with filter for two criteria

    Many thanks John.

    AVERAGEIFS has cracked it - and allows a huge number of conditions, which should allow me to do everything I need to do!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Average formula with filter for two criteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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 Formula that averages certain cells based on how i filter.
    By phbryan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2015, 04:07 PM
  2. Add Total & Average at Bottom row of ListView And Filter based on criteria
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-11-2015, 03:22 AM
  3. [SOLVED] How can I get a formula to filter 2 columns and average a 3rd column
    By blogger153 in forum Excel General
    Replies: 3
    Last Post: 08-30-2014, 07:28 AM
  4. Average Formula based on checkbox filter
    By gan_xl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-12-2014, 10:44 AM
  5. Replies: 0
    Last Post: 01-22-2013, 12:22 PM
  6. [SOLVED] Array formula which can average and sum two different criteria
    By CFlack8472 in forum Excel General
    Replies: 5
    Last Post: 07-05-2012, 11:13 AM
  7. [SOLVED] Average Formula with Criteria
    By PW11111 in forum Excel General
    Replies: 1
    Last Post: 06-10-2005, 10:05 AM

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