Results 1 to 9 of 9

AVERAGEIF is not equal to two values

This is a discussion on AVERAGEIF is not equal to two values within the Excel Questions forums, part of the Question Forums category; I would like to average the values that accour during the week excluding Mondays and Fridays. For that I created ...

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    2

    Default AVERAGEIF is not equal to two values

    I would like to average the values that accour during the week excluding Mondays and Fridays. For that I created the following formula but I beleive it is wrong.

    =AVERAGEIF(A1:A5,"<>MON","FRI",B1:B5)

    AB
    1MON236
    2TUE467
    3WED765
    4THU345
    5FRI897

    Additional Details

    Please notice that this formula works very well if only one day of the week is excluded from the criteria. The problem that I am having is when I add more than one day to the formula, it ignores the second day.

    =AVERAGEIF(A1:A5,"<>MON",B1:B5) This formula works fine.

    Please keep in mind that what I need is to exclude ( not equal to <>) the values from Mondays and Tuesdays. In this exmple the correct result should be 525 or 526 depending on the rounding. Thanks.

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,778

    Default Re: AVERAGEIF is not equal to two values

    =AVERAGEIFS(B1:B5,A1:A5,"<>MON",A1:A5,"<>FRI")

    or more simply:

    =AVERAGE(B2:B4)
    Office 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  3. #3
    New Member
    Join Date
    Dec 2010
    Posts
    2

    Default Re: AVERAGEIF is not equal to two values

    Thank you so much. Your formula worked great!

    The second option while valid, it would not have worked as well for me since the real list that I am working with is a bit more complex. It had values for an entire month.

    Thanks again!

  4. #4
    New Member
    Join Date
    Dec 2010
    Posts
    15

    Default Re: AVERAGEIF is not equal to two values

    Quote Originally Posted by HOTPEPPER View Post
    =AVERAGEIFS(B1:B5,A1:A5,"<>MON",A1:A5,"<>FRI")

    or more simply:

    =AVERAGE(B2:B4)
    I'm having a similar issue if the averageifs function. However, what I need to do is to average only the two criteria out of a larger options in the criteria field. To follow the example above, is there a formula to include, let's say, just Tuesdays and Fridays? The solution above would work for me, but I would have a lot of <<>"'s. Thanks in advance.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,480

    Default Re: AVERAGEIF is not equal to two values

    Quote Originally Posted by ekacan View Post
    I'm having a similar issue if the averageifs function. However, what I need to do is to average only the two criteria out of a larger options in the criteria field. To follow the example above, is there a formula to include, let's say, just Tuesdays and Fridays? The solution above would work for me, but I would have a lot of <<>"'s. Thanks in advance.
    Just enter:

    =SUM(SUMIF($A$2:$A$6,{"TUE","FRI"},$B$2:$B$6))/SUM(COUNTIF($A$2:$A$6,{"TUE","FRI"}))

    Control+shift+enter, not just enter:

    =AVERAGE(IF(ISNUMBER(MATCH($A$2:$A$6,{"TUE","FRI"},0)),$B$2:$B$6))

    The latter is extendible with more conditions, the former not.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    New Member
    Join Date
    Dec 2010
    Posts
    15

    Default Re: AVERAGEIF is not equal to two values

    Thank you that worked, but as you suspected, I need to expand the conditions. So, I had started to build it with the AVERAGEIFS statement. Is there a way to use the OR function in the array type formula, and get the same result?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,480

    Default Re: AVERAGEIF is not equal to two values

    Quote Originally Posted by ekacan View Post
    Thank you that worked, but as you suspected, I need to expand the conditions. So, I had started to build it with the AVERAGEIFS statement. Is there a way to use the OR function in the array type formula, and get the same result?
    OR returns a single, scalar result, not an array. That's the reason why it cannot be used within an array formula when returning an intermediate array is essential. But

    IsNumber(Match(Ref1,Ref2,0))

    does so.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    New Member
    Join Date
    Dec 2010
    Posts
    15

    Default Re: AVERAGEIF is not equal to two values

    I appreciate the explanation.

    How would you expand your formula with additional conditions? Lets assume the next column represents sales reps, and the column after that sale dates, and we want to know the avg sales for John on Tue and Fri for the 1st quarter of the year. Thanks in advance.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,480

    Default Re: AVERAGEIF is not equal to two values

    Quote Originally Posted by ekacan View Post
    I appreciate the explanation.

    How would you expand your formula with additional conditions? Lets assume the next column represents sales reps, and the column after that sale dates, and we want to know the avg sales for John on Tue and Fri for the 1st quarter of the year. Thanks in advance.
    Control+shift+enter, not just enter:
    Code:
    =AVERAGE(
        IF(ISNUMBER(MATCH(DayRange,{"TUE","FRI"},0)),
        IF(SalesRepRange="John",
        IF(DateRage>=StarDate,
        IF(DateRange<=EndDate,
         SalesRange)))))
    Assuming too much and qualifying too much are two faces of the same problem.

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