AVERAGEIF is not equal to two values

Thanks:  0
Likes:  0

# Thread: AVERAGEIF is not equal to two values

1. ## 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)

 A B 1 MON 236 2 TUE 467 3 WED 765 4 THU 345 5 FRI 897

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. ## Re: AVERAGEIF is not equal to two values

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

or more simply:

=AVERAGE(B2:B4)

3. ## 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. ## Re: AVERAGEIF is not equal to two values

Originally Posted by HOTPEPPER
=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. ## Re: AVERAGEIF is not equal to two values

Originally Posted by ekacan
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.

6. ## 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. ## Re: AVERAGEIF is not equal to two values

Originally Posted by ekacan
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.

8. ## 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. ## Re: AVERAGEIF is not equal to two values

Originally Posted by ekacan
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)))))

## User Tag List

#### Posting Permissions

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