To find out your company’s growth per year or to forecast the sales for next year, forecast the sales for next year you may need to calculate the average percentage increase. The average percentage increase gives us the rate of increase per period in percentage over a period of time. In this article, I’ll show you 3 easy steps to calculate the average percentage increase in Excel.
Suppose in your dataset you have your company’s previous 12 years’ sales data. Now, you want to calculate the average percentage increase of sales of your company per year. From the following section, you will get a clear idea of how you can do that.
Download Practice Workbook
What is Average Percentage Increase
Before calculating the average percentage increase, first discuss what is average percentage increase is. The average growth per period over a number of periods is called average percentage increase.
The period can be in any unit of time such as day, month, year, etc. When the period is in the year, the average percentage increase is known as the average annual growth rate (AAGR). There is another type of annual growth rate which is known as the compound annual growth rate(CARG).
In CARG, the compounding effect is considered and each year growth is compounded in the following year. It can be an effective way to find out your savings interest. While in AARG the percentage increase over a number of years is simply averaged. AARG is used when the growth in a year is independent of the growth of the previous year. AARG or simple average percentage increase helps determine the sales growth of a company.
The formula for percentage increase,
Percentage increase in a period = ( Value at the end of the period - Value at the beginning of the period)/ Value at the beginning of the period
And the formula for average percentage increase,
Average percentage increase = Summation of percentage increase in all the period/ Number of period
Understanding the Basics of Average Percentage
Calculating the average of the percentages is a tricky subject. You can’t just simply apply the AVERAGE function to all the percentages. You must know the base of the percentages and need to act accordingly. The following example will give you a clear idea.
Consider the following dataset where you have the percentage sales of factory A (percentage of the total sales of the company) for Automobile and Parts. Here the base of the percentage sales of factory A for Automobile is Total Automobile sales of the company and the base of the percentage sales of factory A for Parts is Total Parts sales of the company. So, you can’t simply average these two percentages to find out the average percentage. If you average the two percentages you will make an error and won’t get the actual percentage.
In this case, to find out the average percentage sales of Factory A, you need to divide the total sales of the factory by the total sales of the company. Then you will get the actual average percentage.
So, when you are calculating the average of some percentages you need to make sure that they all have the same base. In the case of the average percentage increase, the value indicates a percentage increase of the base value over a period of time. Here, you will average the increases of the percentage, not the actual percentages. So, in this time you can simply average all the percentage increases. In the following section, you will see that.
3 Easy Steps to Calculate Average Percentage Increase in Excel
1. Calculate Percentage Increase for the First Period
To calculate the percentage increase you need to know the value of the previous year. But for the beginning year, you don’t have the data for the previous year. So, you have to calculate the percentage increase from the second year of your dataset. To find out the percentage increase in the year 2011, type the following formula in cell D6,
Here the formula will first find the difference between the sales at the end of the year 2011 and the sales at the end of the year 2010. Then it will divide it with the sales value at the end of the year 2010.
➤ Press ENTER
So, you will get the sale increase in 2011.
To get the percentage increase,
➤ Go to the Home tab and click on the Percent Style icon from the Number tab.
As a result, you will get a sales increase in percentage.
- How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)
- Excel Formula to Find Displaced Moving Average (With Easy Steps)
- Average Attendance Formula in Excel (5 Ways)
- How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
- Running Average: How to Calculate Using Excel’s Average(…) Function
2. Percentage Increase in All of the Periods
Now, you need to calculate the percentage increase of all the periods.
➤ Select cell D6 put the cursor in the bottom right corner of the cell.
You will see the cursor will turn into a plus sign.
➤ Drag cell D6 to the end of your dataset after seeing the plus sign.
As a result, the formula which was applied in cell D6 will be applied in other cells and you will get the percentage increases for all the periods.
If you notice, the percentage numbers are showing only one digit. Excel approximates the total number to one digit. As percentage increase is a very small number, so, when it is approximated to one digit, you may not get the accurate value. Let’s add more digits to the numbers.
➤ Go to the Home tab and click on the Increase Decimal icon.
One additional digit will add to the selected numbers per click.
Here, I’ve clicked the Increase Decimal icon thrice. So, three more digits are added to these numbers.
3. Calculate Average Percentage Increase
In the final step, you need to calculate the average of these percentage increases.
➤ Type the following formula,
Here, the AVERAGE function will give you the average of all of the percentage increases.
➤ Press ENTER.
As a result, you will get the average percentage increase of the sales of your company over the year 20111 to 2021.
In this article, I’ve shown you ways to calculate the average percentage increase in Excel. I hope now you can calculate the average percentage increase in Excel effortlessly. If you have any confusion please feel free to leave a comment.
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- How to Calculate Average and Standard Deviation in Excel
- Calculate Average in Excel Excluding 0 (2 Methods)
- Moving Average Formula in Excel (8 Uses with Examples)
- Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
- How to Generate Moving Average in Excel Chart (4 Methods)
- How to Calculate Exponential Moving Average in Excel