AVERAGEIF is not equal to two values

[Excelico]

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)

<TABLE style="WIDTH: 128pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=171><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 987" width=27><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" span=2 width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 20pt; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl65 height=20 width=27></TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl64 width=72>A</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl64 width=72>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl65 height=20></TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Verdana; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63></TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl65 height=20></TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl65 height=20>1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Verdana; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63>MON</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>236</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl65 height=20>2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Verdana; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63>TUE</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>467</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl65 height=20>3</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Verdana; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63>WED</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>765</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl65 height=20>4</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Verdana; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl63>THU</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>345</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl65 height=20>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Verdana; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl63>FRI</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>897</TD></TR></TBODY></TABLE>
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.

 

[Scott Huish]

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

or more simply:

=AVERAGE(B2:B4)

 

[Excelico]

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!

 

[ekacan]

=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.

 

[Aladin Akyurek]

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.

 

[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?

 

[Aladin Akyurek]

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.

 

[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.

 

[Aladin Akyurek]

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:

=AVERAGE(
    IF(ISNUMBER(MATCH(DayRange,{"TUE","FRI"},0)),
    IF(SalesRepRange="John",
    IF(DateRage>=StarDate,
    IF(DateRange<=EndDate,
     SalesRange)))))