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

 

[Comfy]

Can you share the code you are using for the paste as it will affect the answer.

 

[reck1ns]

Can you share the code you are using for the paste as it will affect the answer.

Sheets("Table 1").Range("B2").Value = "Revenue"
Worksheets("Table 1").Range("B2").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

The first copied block is the list of company names, the second the year row and the third the revenues for the companies in the years. Hope this helps.

 

[Comfy]

As you're hard coding the ranges you could just do the following:

Sheets("Table 1").Range("Q19:Q58").FormulaR1C1 = "=POWER(RC[-1]/RC[-10],1/COUNT(RC[-10]:RC[-1]))-1"

 

[reck1ns]

As you're hard coding the ranges you could just do the following:

Sheets("Table 1").Range("Q19:Q58").FormulaR1C1 = "=POWER(RC[-1]/RC[-10],1/COUNT(RC[-10]:RC[-1]))-1"

Thanks, Comfy, this worked perfectly. Another question, the growth rates are displayed as decimal numbers, not percentages. How can I automatically format the CAGR column as percentages?

Another question, some of the revenues are not available for all the years (recently established firm, for example) and these cells display "N/A". For these rows, the CAGR code naturally doesn't work. Is there a way to ignore these "N/A" cells, and start calculating the CAGR from the first available year until 2015.

Thanks again.

 

[Comfy]

Hi reck1ns,

Do you have anymore caveats?

It's helpful to provide an example that covers all the variables.

 

[reck1ns]

Hi reck1ns,

Do you have anymore caveats?

It's helpful to provide an example that covers all the variables.

I don't think there are anymore caveats or details. I replaced all empty cells with "N/A" in a previous step as this is how the non-available figures should be displayed in the final table. I'm sorry I forgot to mention this in the first post. Thanks for your help so far, I really appreciate it.

reck1ns

 

[Comfy]

Here you go:

Sheets("Table 1").Range("Q20:Q24").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("Q20:Q24").NumberFormat = "0.00%"

This assumes that there will be no gaps in Revenue once it starts.

 

[reck1ns]

Here you go:

Sheets("Table 1").Range("Q20:Q24").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("Q20:Q24").NumberFormat = "0.00%"

This assumes that there will be no gaps in Revenue once it starts.

The percentage formatting works now, but I get #VALUE! for all the CAGRs for rows with at least one "N/A".

 

[Comfy]

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