Friday, October 12, 2012

yogi_Compute Average Of Values In A Column Based On Multiple Conditions Including OR Condition

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #804  Oct 12, 2012    www.energyefficientbuild.com.

user queensbookseller said: (http://productforums.google.com/forum/?zx=3rrg4qfnsz5v#!category-topic/docs/spreadsheets/6WPzmt8GdCI)
OR formula in Filter Formula
I'd like to average values in one column in rows that fit certain criteria.  I've found generally that the FILTER function works great.  However, in this case, one of my conditions is that a cell could be one of several specific values. I've tried nesting an OR formula inside the FILTER:

AVERAGE(FILTER(J:J,OR(F:F="searchterm1",ISNUMBER(SEARCH("searchterm2",F:F))),H:H="",I:I=K2))

This gives me a value error.  I tried putting the OR in an array formula, but it gave me the save value no matter what K2 says. 
-------------------------------------------------------------------------------------
following is a solution to the problem