Fairly complex formula needed.

[Traderjoe56]

Hi everyone,

Thanks ahead of time for anyone that helps with this problem. basically I have a couple hundred of rows of countries, and data columns for years, with the final column calculating the average annual % change for preferably a 5 year span. The problem is, the data is spotty, and I need to change the formula manually for a number of countries. What I would like, is a macro/formula that can look for the first non zero year. Whatever that year is serves as an anchor. I then want the formula to go back five years, and check if that's non zero. if it is, I want it to run say the formula, (=(I21-E21)/E21/4) to basically calculate the average annual % change for five years. if it's zero, I want it to expand to the 6th year... and if that's zero, the 7th year... until it reaches the 8th year, and then I want it to snap back into the 4th year, 3rd year, and finally the 2nd year.... in other words, i'm trying to create something that can fill in the final three columns of what's in the picture.

Hope this makes sense, and again, thanks in advance for whatever help you can give....

Joe

 

[GlennUK]

I cannot see your picture ... can you try some other way of showing your layout? Like Excel Jeanie maybe:
http://www.excel-jeanie-html.de/index.php?f=1
?

 

[Traderjoe56]

my apologies... and I'm not entirely sure if I'm using excel Jeanie right. After a bit of talking with the person in charge of the project, I've actually redefined what the algorithm should be doing:
1. Starting from 2009 and working backwards, find the first, non zero value. let this be (M)
2. starting from 2005, and working forward, find the first non zero value. let this be (N)
3. if (1) and (2) are different years, calculate average annual % change and spit it out using the formula ((M)-(N))/(N)/(interval between M and N)
4. also if possible, calculate % change using (M-N)/N, and print out the year intervals of m and n (i.e. 2005-2008)

I'm gonna paste what excel jeanie is spitting out, and hoping this will look alright once posted.

Thanks again for any help!
Joe

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Countries	*	2002	2003	2004	2005	2006	2007	2008	2009	Average Annual % Change 2005-2009 *	% Change 2005-2009*	*different year interval
2	Philippines	*	2.8	3.0	3.2	3.2	3.0	3.4	4.0	4.2	0.082	0.33	*
3	Nicaragua	*	1.9	2.1	2.5	2.7	2.7	3.0	2.9	-	0.023	0.07	2005-2008
4	Venezuela	*	-	3.3	3.4	3.0	2.6	2.5	-	-	-0.071	-0.14	2005-2007
5	El Salvador	*	-	-	5.2	5.4	5.5	-	-	-	0.023	0.05	2005-2006
6	Kazakhstan	*	-	-	-	-	-	-	-	-	*	*	*
7	France	*	4.1	-	-	-	-	-	-	5.0	*	*	*
8	Mongolia	*	3.9	3.9	-	*	4.0	*	*	3.6	-0.033	-0.10	2005-2008
Sheet1

 

[GlennUK]

Have a look at this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Countries	*	2002	2003	2004	2005	2006	2007	2008	2009	Average Annual % Change 2005-2009 *	% Change 2005-2009*	*different year interval
2	Philippines	*	2.8	3	3.2	3.2	3	3.4	4	4.2	0.078	0.31	2005 - 2009
3	Nicaragua	*	1.9	2.1	2.5	2.7	2.7	3	2.9		0.025	0.07	2005 - 2008
4	Venezuela	*		3.3	3.4	3	2.6	2.5			-0.083	-0.17	2005 - 2007
5	El Salvador	*			5.2	5.4	5.5				0.019	0.02	2005 - 2006
6	Kazakhstan	*
7	France	*	4.1							5
8	Mongolia	*	3.9	3.9			4			3.6	-0.033	-0.10	2006 - 2009
Sheet4

 

[GlennUK]

The numbers will be slightly different to yours, as I believe that yours have more than 2 decimal places for the actual stored values, and I am simply using the figures shown.

 

[Traderjoe56]

Hi Glenn,

I think this will actually do it. I tried an approach using VBA and a user defined function, but am not sure if i had it working correctly.

Thanks again for all your help, I definitely appreciate it!

Joe