=AVERAGEIFS(B1:B5,A1:A5,"<>MON",A1:A5,"<>FRI")
or more simply:
=AVERAGE(B2:B4)
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 ...
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
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.
=AVERAGEIFS(B1:B5,A1:A5,"<>MON",A1:A5,"<>FRI")
or more simply:
=AVERAGE(B2:B4)
Office 2007/2010/2013
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!
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.
Assuming too much and qualifying too much are two faces of the same problem.
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?
Assuming too much and qualifying too much are two faces of the same problem.
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.
Assuming too much and qualifying too much are two faces of the same problem.
Like this thread? Share it with others