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.

Answers


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

MATCH() will find the right value in column 1 and return its position in A:A.

OFFSET

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

AVERAGE

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.

IFERROR

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


Need Your Help

Best mobile framework to build an iPhone app for a non mobile-dev?

ios mobile

I'm a php dev, and i want to port my web application first on the iPhone.

Binary Tree being filled with zeros

c++ binary-tree

I am writing a binary tree (for only numbers zero or larger) in C++ and for some reason all the values but the head seem to be zero (when I add an element) and I am not sure why that is happening. ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.