Page 1 of 2 12 LastLast
Results 1 to 10 of 11

averageif - multiple criteria

This is a discussion on averageif - multiple criteria within the Excel Questions forums, part of the Question Forums category; This is definitely a simple question and i'm certain it's something that i'm overlooking in writing a formula, but say ...

  1. #1
    New Member
    Join Date
    Aug 2008
    Posts
    15

    Default averageif - multiple criteria

    This is definitely a simple question and i'm certain it's something that i'm overlooking in writing a formula, but say you have 5 values:

    A1: 600
    A2: 715
    A3: 822
    A4: 901
    A5: 1001

    B1: 600
    B2: 950

    I want to create an averageif formula. What i would like it do is to average if higher than 600, but lower than 950. So the formula i have is:
    =AVERAGEIF($a$1:$a$5,"<="&B2). What am i missing for the second part -- greater than b1? For some odd reason i'm not typing the formula in correctly to get the right answer here - can anyone help

  2. #2
    New Member
    Join Date
    Nov 2009
    Location
    China
    Posts
    26

    Default Re: averageif - multiple criteria

    Quote Originally Posted by clock245 View Post
    this is definitely a simple question and i'm certain it's something that i'm overlooking in writing a formula, but say you have 5 values:

    A1: 600
    a2: 715
    a3: 822
    a4: 901
    a5: 1001

    b1: 600
    b2: 950

    i want to create an averageif formula. What i would like it do is to average if higher than 600, but lower than 950. So the formula i have is:
    =averageif($a$1:$a$5,"<="&b2). What am i missing for the second part -- greater than b1? For some odd reason i'm not typing the formula in correctly to get the right answer here - can anyone help

    =(sumif(a1:a5,">="&b1)-sumif(a1:a5,">"&b2))/sumproduct((a1:a5>=b1)*(a1:a5<=b2))

  3. #3
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Location
    San Antonio, Texas
    Posts
    4,379

    Post Re: averageif - multiple criteria

    You could also try...

    =AVERAGE(IF($A$1:$A$5>=B1,IF($A$1:$A$5<=B2,$A$1:$A$5)))

    ...confirmed with CONTROL+SHIFT+ENTER.
    Jeff

  4. #4
    New Member
    Join Date
    Nov 2009
    Location
    China
    Posts
    26

    Default Re: averageif - multiple criteria

    Quote Originally Posted by foodorwater View Post
    =(sumif(a1:a5,">="&b1)-sumif(a1:a5,">"&b2))/sumproduct((a1:a5>=b1)*(a1:a5<=b2))

    Here's another formula

    =AVERAGE(IF((A1:A5>=B1)*(A1:A5<=B2),A1:A5))

    Ctrl+Shift+Enter

  5. #5
    New Member
    Join Date
    Nov 2009
    Location
    China
    Posts
    26

    Default Re: averageif - multiple criteria

    Quote Originally Posted by foodorwater View Post
    Here's another formula

    =AVERAGE(IF((A1:A5>=B1)*(A1:A5<=B2),A1:A5))

    Ctrl+Shift+Enter

    more....
    =(SUMIF(A1:A5,">="&B1)-SUMIF(A1:A5,">"&B2))/(COUNTIF(A1:A5,">="&B1)-COUNTIF(A1:A5,">"&B2))

  6. #6
    New Member
    Join Date
    Nov 2009
    Location
    China
    Posts
    26

    Default Re: averageif - multiple criteria

    Quote Originally Posted by foodorwater View Post
    more....
    =(SUMIF(A1:A5,">="&B1)-SUMIF(A1:A5,">"&B2))/(COUNTIF(A1:A5,">="&B1)-COUNTIF(A1:A5,">"&B2))

    more...
    =SUMPRODUCT((A1:A5>=B1)*(A1:A5<=B2)*A1:A5)/SUMPRODUCT((A1:A5>=B1)*(A1:A5<=B2))

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,828

    Default Re: averageif - multiple criteria

    Hello clock245, welcome to MrExcel.

    I assume you are using Excel 2007 since AVERAGEIF function doesn't exist in earlier versions. For multiple criteria there is also an AVERAGEIFS function, in your scenario you can use that like this:

    =AVERAGEIFS($A$1:$A$5,$A$1:$A$5,"<="&B2,$A$1:$A$5,">="&B1)

  8. #8
    New Member
    Join Date
    Aug 2008
    Posts
    15

    Default Re: averageif - multiple criteria

    Thanks all! I will use Mr. Houdini's formula - exactly what i was looking for. i've used it before, but i completely blanked on the formula (surprisingly i never looked past the =averageif on the autocomplete to recognize it - stupid me!)

    Thanks again all!

  9. #9
    New Member
    Join Date
    Jul 2009
    Location
    Melbourne, Australia
    Posts
    12

    Default Re: averageif - multiple criteria

    A bit of a thread dig - but was wondering if there's a way of getting an averageifs to use a multiple OR criteria, i.e. TRUE *or* FALSE.

  10. #10
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,828

    Default Re: averageif - multiple criteria

    You probably can't use AVERAGEIFS for that but there are other ways.....how many criteria do you have, can you expand a little on what you want to do?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com