Prantick Bala
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.
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 the 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 is:
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 periodThe formula for average percentage increase:
Average percentage increase = Summation of percentage increase in all the period/ Number of periodUnderstanding Basics of Average Percentage in Excel
Calculating the average of the percentages is a tricky subject. You can’t just simply apply the AVERAGE function 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 the total automobile sales of the company and the base of the percentage sales of factory A for Parts is the 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 of 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, at this time you can simply average all the percentage increases. In the following section, you will see that.
Step 1: Calculating Percentage Increase for 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.
=(C6-C5)/C5Here 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 the difference with the sales value at the end of the year 2010.
➤ Press ENTER and you will get the sales 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.
Read More: How to Calculate Average Percentage Change in Excel
Similar Readings
- How to Calculate Percentage Between Multiple Numbers in Excel
- How to Calculate Percentage Change with Negative Numbers in Excel
- How to Calculate Year over Year Percentage Change in Excel
- How to Show Percentage Change in Excel Graph
Step 2: Determining Percentage Increase in All Periods
Now, you need to calculate the percentage increase of all the periods.
➤ Select cell D6 and 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 that 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. The 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 be added to the selected numbers per click.
Here, I’ve clicked the Increase Decimal icon thrice. So, three digits are added to these numbers.
Read More: How Do You Calculate Percentage Increase or Decrease in Excel
Step 3: Calculating Average Percentage Increase in Excel
In the final step, you need to calculate the average of these percentage increases.
➤ Type the following formula.
=AVERAGE(D6:D16)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 2011 to 2021.
Read More: How to Calculate Percentage Increase from Zero in Excel
Download Practice Workbook
Conclusion
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.
