using formulas to generate the mode of Earnings Growth from stock data without populating the data in some other area explicitly

[Mikel_1234]

I have a stock spreadsheet that I have been working on and improving over the years and this is
one of the things that I have finally gotten around to try to accomplish. I would much prefer this in a formulaic manor as opposed to VB, as if I migrate to VB, there are a LOT of improvements I can make and I am not yet ready to head down that road. Pivot tables and such are not something that I want to use, again it would require a redesign, and I am trying to have my spreadsheet be almost fully automatic as opposed to yet another investigative tool. Anyway...

I have a list of data on given stocks the stock tickers are in column A and the data for the ticker in that same row. I have a method to pull the EPS from this list using Index/Mod/Match that is pretty solid. What I am trying to do is generate a list of annual growth rate for each year, so a year to year growth rate, where I will then round to the nearest percent and basically make an array out of that, so that I can then use the MODE function to get the most common growth value. I could theoretically do this in a multi step function, but it would require an ever growing length for the growth rates as I keep historical data so far back to 2000 and will keep adding as the years go forward. The thought of a dedicated separate tab suddenly comes to mind, if there are no other options, I will try that.

My real goal is to try and find a way to generate a rough slope of earnings growth rate is to determine if it is an upward/downward trend or flat. I know I can do this all graphically, but I want to integrate this into some form of a scoring system so being able to apply a 1,0,-1 value would be of assistance. There maybe other higher math functionalities that will get this done, but not my specific area of knowledge so I am trying to hack it out the way that makes sense to me. I am open to new options and new math. I think the only things I would like to try and avoid is Pivot almost definitely, and VB as not my preferred method but tolerable, forcing me to learn VB isn't a bad thing.

Below is some test data and the blanks under 2008 is to be able to test my logic to be able to handle blanks. So far all of my logic does manage this, it detects first year of data and last year of data and can associate data to a given year if necessary.

Thanks in advance for any help.



[TABLE="width: 978"]
<colgroup><col span="4"><col><col span="2"><col><col span="2"><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]2008[/TD]
[TD="colspan: 3"]2009[/TD]
[TD="colspan: 3"]2009[/TD]
[TD="colspan: 3"]2010[/TD]
[/TR]
[TR]
[TD]ticker
[/TD]
[TD][/TD]
[TD]DIV[/TD]
[TD]EPS[/TD]
[TD]Shares[/TD]
[TD]DIV[/TD]
[TD]EPS[/TD]
[TD]Shares[/TD]
[TD]DIV[/TD]
[TD]EPS[/TD]
[TD]Shares[/TD]
[TD]DIV[/TD]
[TD]EPS[/TD]
[TD]Shares[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[TD]$1.12[/TD]
[TD]1577334090[/TD]
[TD]$0.00[/TD]
[TD]$1.24[/TD]
[TD]1577334090[/TD]
[TD]$0.00[/TD]
[TD]$1.50[/TD]
[TD]1577334090[/TD]
[/TR]
</tbody>[/TABLE]