Return a cell reference for use in a formula
I have an excel workbook with paired data sets of the type [columnA=time:columnB=data] There are thousands of data points.
What I'm looking to do is this: I have a table of time values on a separate sheet. I want to find the corresponding cell in the data column of the data worksheet (column B) and return an average of the values of 5 cells before it and 40 cells after it.
By searching the questions I came up with this formula to return a cell address of the exact value I'm looking for, however I can't put it into an average formula.
=CELL("address",INDEX('EMG Data'!B1:B10000,MATCH(C5,'EMG Data'!A1:A10000,0),1))
C5 is the time value I know I want to get the data value pair for.
use a combination of average, offset and match instead, offset will allow you to change the number of rows/columns in the range:
=IFERROR(AVERAGE(OFFSET($B$1,MAX(MATCH(C5,A:A,0)-6,0),0,46)),"SOMETHING IS WRONG")
MATCH() will find the right value in column 1 and return its position in A:A.
To OFFSET to the corresponding row compared to cell B1 you should do MATCH()-1 (otherwise a Match in cell A1 yielding a 1 will OFFSET B1 by 1 row thus showing the value of B2!!
You want to offset B1 by 0 columns so put that as third parameter.
Moreover you want to have the 5 rows before as well so this becomes MATCH()-6, to avoid trying to retrieve data from rows with negative row numbers we use MAX(MATCH()-6,0).
As you want the 5 cells before and the 40 cells after you want the height to be 5 + 1 + 40 = 46 cells completing the OFFSET parameters with that number for the height parameter
Final step is to get the actual average, as the OFFSET() function gives back a range of cells this is seen as a normal range input for the AVERAGE function.
In case the value you look for is not found in column A:A than MATCH() will give an error which will work its way through the nested functions, Most of us would capture such event with IFERROR() and transform the error into something user friendly, or have an alternative function/result in that case