Automate compound annual growth rate CAGR calculation

0 votes

I have a list of firms' revenue and employment (historical and forecast). I want to calculate the compound annual growth rate (CAGR) from 2014 to the latest available year. I used the following formula,

=(C17/C12)^(1/COUNT(C13:C17))-1

Although the formula is effective, it cannot be expanded. This means that I would have to manually code it for each firm (there are thousands of them) and for each attribute aside from revenue and employees (there are many others). Additionally, some businesses have varied end dates; for example, some businesses' projected data finishes in 2017, while others end in 2018, etc.

Could any of the upcoming suggestions work as a remedy? Exist, other people?

  1. VBA
  2. PivotTable
  3. PowerPivot
Oct 7, 2022 in Others by Kithuzzz
• 38,010 points
969 views

1 answer to this question.

0 votes

The following PowerPivot DAX formulas worked for me,

first year revenue = CALCULATE(SUM(source[revenue]),source[year]=2014)

last year revenue = CALCULATE(sum(source[revenue]),filter(source,source[year]=max(source[year])))

Forecast Revenue CAGR = ([last year revenue]/[first year revenue]) ^(1/(max([year])-2014))-1
answered Oct 7, 2022 by narikkadan
• 63,420 points

Related Questions In Others

+1 vote
1 answer

How to Automate OutOfMemoryError Troubleshooting?

Troubleshooting OutOfMemoryError or any memory related problem ...READ MORE

answered Jun 18, 2019 in Others by Jim
• 810 points
589 views
0 votes
2 answers

How clone script help your business growth?

Clone script is a customizable solution that ...READ MORE

answered Oct 20, 2020 in Others by rentall
• 270 points
521 views
0 votes
1 answer

How to automate AWS Elastic Transcoder Jobs for s3 buckets?

This article will walk you through uploading ...READ MORE

answered Mar 24, 2022 in Others by gaurav
• 23,260 points
645 views
0 votes
1 answer

Average TIME Calculation differs between Excel and PowerBI

They are calculating different things. In Power ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,420 points
2,606 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
698 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,409 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,268 views
0 votes
1 answer

Insert pie chart in Excel macro function

Think about arranging your data in a ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
873 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP