I've read that the AVERAGEIF function can't take OR as criteria, but there has to be some workaround to this.
I need the formula to tell me the average of value of the cells in the H column, but only if the J cell is either "2-0", or "2-1".
Note that the sheet is affected by autofilter.
Thanks.
You could use =AVERAGE(IF(J2:J540="2-1",J2:J540="2-0",H2:H540))
It needs to be entered as an array formula with Ctrl+Shift+Enter rather than just Enter.
Thanks for your answer. Now that I look at the thread title, it should have been AVERAGEIF with one of multiple criteria
Your formula seems to be working but I have one question.
When I filter by "2-0" and "2-1" in the set column, the average is 1.52, whereas the formula says 1.44
I didn't download your file.
Try this array formula**:
=AVERAGE(IF(SUBTOTAL(2,OFFSET(H2,ROW(H2:H20)-ROW(H2),0)),IF(ISNUMBER(MATCH(J2:J20,{"2-0","2-1"},0)),H2:H20)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Adjust the ranges to suit.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
This is just what I needed, on the other hand I'm sad because I would have never figured this out on my own.
Gracias!
The folks at Microsoft didn't make it easy to perform calculations on filtered data!
Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks