Average Annual Growth Rate that excludes non numeric values.

I hadn't a clue what was going on... So I had to dissect your first formula, to understand what you were doing. For what it's worth, it could be simplified a bit (array entered):

=IFERROR((LOOKUP(10^10,A1:T1)/INDEX(A1:T1,LARGE(IF(A1:T1<>"-",COLUMN(A1:T1)),10)))^(1/(10-1))-1,"")

now to your problem....

What formula have you used for annual growth rates ??? (spot the analytical chemist who knows stuff-all about financial functions)...

Hi Glen,

yes that new formula tidied things up a bit. It also looks better with a blank cell rather an error return, thanks for that.

Below is my annual growth rates formula. Oh and here's a link to the page I got it from, http://www.investopedia.com/terms/a/aagr.asp

=SUM((L1/K1-1)+(M1/L1-1)+(N1/M1-1)+(O1/N1-1)+(P1/O1-1)+(Q1/P1-1)+(R1/Q1-1)+(S1/R1-1)+(T1/S1-1))/9

My starting point is the 10th year so I can only divide by 9.

Thanks.

Sorry Glen, this is the formula for annual growth rates.

=SUM((L1/K1)-1+(M1/L1)-1+(N1/M1)-1+(O1/N1)-1+(P1/O1)-1+(Q1/P1)-1+(R1/Q1)-1+(S1/R1)-1+(T1/S1)-1)/9

Edit,

I think that does the same thing. I'm on a real roll today.

In fact I know it does the same thing. My calculation page is a mess. One of my N1 references was a N51. I've corected it now. Just use the first formula if you think is better.

Try

U1

Formula:

=(INDEX(OFFSET($A1,,MATCH((LOOKUP(10^10,A1:T1)),A1:T1,0)-1,,-10),1,10)/INDEX(OFFSET($A1,,MATCH((LOOKUP(10^10,A1:T1)),A1:T1,0)-1,,-10),1,1))^(1/10)-1

Originally Posted by Eamonn100

Thanks Shukla,

I tried that. It works for some but it seems to return #NUM when the most recent value is a minus value.

You can change - in to blank cell as - this is a text and can't work, will give error

Select the data A1:T15 then control+H put - in find what and replace with should be blank then replace all,

Do the same above thing but just change find what with a space and replace with blank.

Hope it should work.

Put your expected result in sample workbook

Never mind the blanks for now.... Does this give the expected results for "complete" datasets (it's an array formula). I think it does... but I'm getting boggle-eyed!!

=SUMPRODUCT((T1:INDEX(1:1,LARGE(COLUMN(B1:T1)*(B1:T1<>""),9)))/(S1:INDEX(1:1,LARGE(COLUMN(A1:S1)*(A1:S1<>""),9)))-{1,1,1,1,1,1,1,1,1})/9

You'll probably need VBA if you want an elegant solution. This assembles the "acceptable" data, and then uses the simple array average formula.All the interim stuff can be hidden or buried on another sheet.

Glen I think we can call this solved. Prehapes others would like to get it into all one cell but I'm happy with this.

One question though, Column A on my actual spreadsheet is column W. Can you add to your formula below so that it can be moved about. At the moment the CAGR column goes blank when I insert other cells. Also now I can use your "Data matching the criteria" section for the CAGR formula so I don't really need the below formula but it would be good to have it right, in case in the future I could use it for other things.

=IFERROR((LOOKUP(10^10,A2:T2)/INDEX(A2:T2,LARGE(IF(A2:T2<>"-",COLUMN(A2:T2)),10)))^(1/(10-1))-1,"")





If you are into history/military history the below links are your payment for your help. If you're not into history, give yourself a pat on the back.

This is part one of a five or six parter.

https://youtu.be/YFMT_BVBBsA


This is part one of threes parts.

https://youtu.be/J7CmBN741Vw


This is the guys site.

http://www.dancarlin.com/

Glad to have helped... you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.