CODEWITHSUNDEEP
excelexcel-formula
CMWells
CMWells

Reputation: 35

Retrieve the cell reference of the first non-blank cell in a column

I have a data set (discrete monthly return series) with two dozen columns representing data series, c.100 rows representing month-end time-series. The data in the set represents the monthly performance of each data set.

Each series has run for a different length of time and is updated to a different length of time.

My conundrum is; I am looking to find, for each series, the dates of both the earliest data point and the most recent data point. I was hoping to do this by way of referencing the row headers (date) in column A. To do this I would need to know the row number of the earliest data point.

I can get the value of the earliest data point using a CSE IndexMatch, but what I really need is the reference.

EDIT

Tom, this is the data set;

enter image description here

As you can see, each data set starts (and finishes) at a different date.

In a summary sheet I am putting the date range for which each respective data series has data, that is why I need the row value (so that I can lookup the dates).

Upvotes: 1

Views: 4611

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

So your formula was fine, you just needed to MATCH in column G and INDEX into column A:-

=INDEX('Discrete Monthly Returns'!A3:A1048576,MATCH(FALSE,ISBLANK('Discrete Monthly Returns'!G3:G1048576),0))

I appreciate that you would want the start date to appear somewhere else, but this is just an illustration of how to use the formula

enter image description here

Upvotes: 1

Randy
Randy

Reputation: 9809

This example teaches you how to find the cell address of the maximum value in a column.

  1. First, we use the MAX function to find the maximum value in column A.

1

  1. Second, we use the MATCH function to find the row number of the maximum value.

2

Explanation: the MATCH function reduces to =MATCH(12,A:A,0), 7. The MATCH function returns the position of the maximum value in column A. Set the third argument to 0 to return an exact match.

  1. Finally, we use the ADDRESS function to return the cell address.

3

Explanation: the ADDRESS function reduces to =ADDRESS(7,1), $A$7. The first argument specifies the row number. The second argument specifies the column number.

http://www.excel-easy.com/examples/locate-maximum-value.html

The answer is copy-paste work from the above link, but pasted here as reference.

Upvotes: 1

Related Questions