Excel for Calculating a CAGR 3

[michels24]

Hi,
I am setting up a formula so Excel will calculate the CAGR (defined as: (end number/begin number)^(1/(end year-begin year))-1. The Excel sheet has the years (1999 to 2006) running fixed across the top in columns b to i. The concept is that someone will enter the begin year and end year into columns K & L, respectively. The formula will then see what begin number and end number K & L correlate to (ie. which column b to i does it reference).

So, the (end year-begin year) piece is simply ($k3-$L3) for row 3 in this example. But, inorder for it to reference the correct b to i column it requires to many "if" statements. By using "if" statements, there are 28 possible scenairios for which years the CAGR will be calculating between the years 1999 to 2006 - e.g. 2001 to 2005.

Any suggestions? There should be an easier way to do this. Is there a function I am not aware of?

Thanks!!

 

[darag2358]

Don't think I am completely understanding what you are trying to accomplish, but I think you might be looking for the Vlookup or Hlookup function? Check out the help section in Excel for information on how these two functions work.

 

[bkpchs237]

michels24,

I have placed my label headers/years in cells B1 thru I1 from 1999 to 2006. Then my data is in cells B2 thru I20 for my example. Then in cells K2 thru L20 are the years you want to compare.

The CGAR formula in cell M2 should read:
=IF(L2<=K2,&quot;no good&quot;,(HLOOKUP(L2,$B$1:$I$20,ROW(),FALSE)/HLOOKUP(K2,$B$1:$I$20,ROW(),FALSE))^(1/(L2-K2))-1)

Now copy that formula down the entire column as needed.

Hope this helps.

 

[DaleWatson123321]

Hi michels24,

I've created a working model of what you've described.

If you would like the file, email me and and send it via return email.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[michels24]

Thanks for eveyone's help. I wound up using the following formula:

=IF(K3>=L3,&quot;nm&quot;,IF(K3<$B$1,&quot;nm&quot;,IF(K3>I$1,&quot;nm&quot;,IF(L3<$B$1,&quot;nm&quot;,IF(L3>I$1,&quot;nm&quot;,((HLOOKUP(L3,$B$1:$I$199,ROW(),FALSE)/HLOOKUP(K3,$B$1:$I$100,ROW(),FALSE))^(1/(L3-K3))-1))))))

Whereby the begining year is in the k column, the end year is in the l column, the year I am looking at are in B1 to I1, and the data is from b2 to i2.

Thanks again!!