Calculating CAGR in a new column with VBA

[reck1ns]

Hi all,

I have extracted a few tables from a large set of data into a new workbook with VBA. Next, I wrote a macro to copy/paste these tables into new sheets on the workbook. I know how to copy/paste ranges with macros but now I would want to figure out a way to calculate CAGR in a new column for all rows in the tables while they are pasted to new sheets. I have tried googling this for a few hours with no luck, maybe some of you experts would be able to help me.

The table below illustrates my problem. Everything else is already done but I'd like the CAGR column to be calculated when the tables are pasted to respective sheets with VBA.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Revenues
[/TD]
[TD]2010
[/TD]
[TD]2011
[/TD]
[TD]2012
[/TD]
[TD]2013
[/TD]
[TD]2014
[/TD]
[TD]2015
[/TD]
[TD]CAGR 2010-2015
[/TD]
[/TR]
[TR]
[TD]Company A
[/TD]
[TD]100
[/TD]
[TD]110
[/TD]
[TD]120
[/TD]
[TD]130
[/TD]
[TD]140
[/TD]
[TD]150
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company B
[/TD]
[TD]110
[/TD]
[TD]123
[/TD]
[TD]154
[/TD]
[TD]134
[/TD]
[TD]155
[/TD]
[TD]178
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company C
[/TD]
[TD]200
[/TD]
[TD]300
[/TD]
[TD]400
[/TD]
[TD]500
[/TD]
[TD]600
[/TD]
[TD]700
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company D
[/TD]
[TD]220
[/TD]
[TD]320
[/TD]
[TD]450
[/TD]
[TD]420
[/TD]
[TD]555
[/TD]
[TD]666
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company E
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]35
[/TD]
[TD]85
[/TD]
[TD]45
[/TD]
[TD]99
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance,

reck1ns

 

[reck1ns]

Could you post your code and an accurate representation of your data with the expected CAGR.

Sheets("Table 1").Range("B2").Value = "Revenue"
Sheets("Table 1").Range("M2").Value = "CAGR"
Worksheets("Table 1").Range("B2").Font.Bold = True
Worksheets("Table 1").Range("M2").Font.Bold = True

    Sheets("MasterSheet").Range("B19:B58").Copy
    Sheets("Table 1").Activate
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

    Sheets("MasterSheet").Range("G4:P4").Copy
    Sheets("Table 1").Activate
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

    Sheets("MasterSheet").Range("G19:P58").Copy
    Sheets("Table 1").Activate
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
    
    Sheets("Table 1").Range("M3:M42").FormulaR1C1 = "=POWER(RC[-1]/INDEX(RC[-10]:RC[-1],1,COUNTBLANK(RC[-10]:RC[-1])+1),1/COUNT(RC[-10]:RC[-1]))-1"
    Sheets("Table 1").Range("M3:M42").NumberFormat = "0.00%"

For example, for row 9, the CAGR should be calculated for years 2012-2014 (J9:L9), and for row 15 CAGR for 2006-2014 (D15:L15). Hope this makes my problem more clear.

 

[Comfy]

Here's the VBA formula to work with "N/A" rather than blanks:

"=POWER(RC[-1]/INDEX(RC[-10]:RC[-1],1,COUNTIF(RC[-10]:RC[-1],"N/A")+1),1/COUNT(RC[-10]:RC[-1]))-1"

 

[reck1ns]

Here's the VBA formula to work with "N/A" rather than blanks:

"=POWER(RC[-1]/INDEX(RC[-10]:RC[-1],1,COUNTIF(RC[-10]:RC[-1],"N/A")+1),1/COUNT(RC[-10]:RC[-1]))-1"

I get a Compile Error (syntax error) when running the macrofor this row:

Code:

Sheets("Table 1").Range("M3:M42").FormulaR1C1 = "=POWER(RC[-1]/INDEX(RC[-10]:RC[-1],1,COUNTIF(RC[-10]:RC[-1],"N/A")+1),1/COUNT(RC[-10]:RC[-1]))-1"

Edit: When I run the macro, excel higlights the "N" in "N/A" in the code and I get a screen saying "Expected: end of statement"

 

[Comfy]

Yea that's an oversight:

"=POWER(RC[-1]/INDEX(RC[-10]:RC[-1],1,COUNTIF(RC[-10]:RC[-1],""N/A"")+1),1/COUNT(RC[-10]:RC[-1]))-1"

 

[reck1ns]

Yea that's an oversight:

"=POWER(RC[-1]/INDEX(RC[-10]:RC[-1],1,COUNTIF(RC[-10]:RC[-1],""N/A"")+1),1/COUNT(RC[-10]:RC[-1]))-1"

Works as intended now, million thanks to you!