Create Relative Performance Chart But Starting Date Unknown

I'm trying to create individual charts of the performance of over 100 separate stocks going back to 1990. It's a line chart starting at 0% at the left axis (1990) and the line moves up or down based on the stock's performance until September 2013. The math for the individual points on the line chart is pretty simple: (closing price at any point in time)/(closing price in January 1990)-1.

I'm using =INDIRECT in my primary worksheet to do the math and create the individual graphs, which pulls raw closing stock price data from another spreadsheet. The problem is that some stocks weren't trading back in 1990 and in those cases, the math is dividing the current stock price by zero. I want to avoid manually finding the first trade date for each stock (which is the denominator in every cell for a given stock) by automating the process.

So my question: Is there a way, using the =INDIRECT function to have the formula automatically calculate performance back to the first non-zero point in time (i.e. when the stock first started trading)? The formula would need to be sophisticated enough to know which cell was the first month of non-zero data.

Hi,

There is definitely a way to do what you want (either using the OFFSET function or a MATCH function), but could you please upload your workbook so that I can take a look at your data to help you develop an appropriate formula?

Thanks

Thanks. Here is a link to the file:
https://www.sugarsync.com/pf/D451512...tDownload=true

For simplicity, I've only included a few stocks, and their tickers are UTX, HON, BA, ECHO, FDX, and HUBG, which can be found in the tab "DATA". Go to the tab "Tables for Charts" and in cell A1 enter any of these tickers. You'll see for the companies that have been trading since 1990 (such as FDX), the calculations and chart look perfect because they have a starting stock price for January 1990. But for stocks that started trading after January 1990, it results in a #DIV/0 error. I need a way to have it start computing the stock's return based on its first trading month. I could hunt this down manually, but there are over 100 stocks in the active workbook and I want it to be a dynamic process, whereby I can add more stocks over time.

Thanks!

Hi,

All that was required was to change the formula in column B to an array formula:

Formula:

Please Login or Register  to view this content.

to hide the errors, and then change the source data range of the chart to a named range with the following formulae:

Formula:

Please Login or Register  to view this content.

for the Date Axis (I called the Named Range "DateAxis", because I'm creative like that :P), and

Formula:

Please Login or Register  to view this content.

for the values (called "ChartValues").

I hope this helps

Thanks! You are an Excel genius! I've been using Excel for 25 years and had never used MATCH, arrays, or created range names for charting purposes. It took me about 2 hours to get your insights above working in my active worksheet (I had to learn about those items by using some YouTube videos), but I eventually got it to work. Thank you so much! Also, thank you for teaching me how to use these new features.

No problems! I'm just happy to be able to share some of my knowledge with other people