How to Calculate Average Percentage Change in Excel (3 Simple Ways)

While working in Excel, you might need to calculate the average percentage change in a series of data. For example, if you have an Excel worksheet with the number of sales of every month of the year, then you might want to calculate the percentage change in sales between the successive two months to find out if the business is growing or declining. Or you can also use a specific month as the reference and calculate the average percentage change in sales between that month and other months of the year. In this tutorial, I will show you how to calculate the average percentage change in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


3 Suitable Ways to Calculate Average Percentage Change in Excel

Let’s assume a scenario where we are working with the sales volumes of the first 6 months of the year of a shop. We have the Month, Sales Volume, and Monthly Average Percentage Change in the worksheet. Now, we will calculate the monthly average percentage change in sales between 2 consecutive months and also calculate the change in sales between January and every other month. The image below shows the worksheet that we are going to work with.

ow to Calculate Average Percentage Change in Excel


1. Calculate Average Percentage Change Using Formula

We can use the formula for average percent change to calculate the change in sales between two consecutive months. The formula for percent change is like below.

Calculate Average Percentage Change Using Formula

And formula below is for average percentage change.

Calculate Average Percentage Change Using Formula

Step 1:

  • First, we will write the below formula in cell D6 to calculate the percentage changes in Sales Volume between the month of January and February.
=(C6-C5)/C5 * 100

Formula Breakdown:

Here,

C6 = Sales Volume in February = Final Value

C5 = Sales Volume in January = Initial Value

Note: We can not calculate the percentage change in sales for the month of January as we do not have Sales Volume for the preceding month to consider it as an Initial Value.

Calculate Average Percentage Change Using Formula

  • Upon pressing ENTER, we will now see the percentage change in Sales Volume between the month of January and February.

Calculate Average Percentage Change Using Formula

Step 2:

  • Now, we will drag the fill handle of cell D6 to apply the formula to the rest of the cells.

drag the fill handle

  • Finally, we will see the percentage change in Sales Volume between each month and its preceding month.

Calculate Average Percentage Change Using Formula

  • While all the cells with values under the Monthly Percentage Change column are selected, we will click on the Decrease Decimal button several times from the Number under the Home Each time we click on this button, it will decrease the decimal by one place.

Calculate Average Percentage Change Using Formula

  • Now, we will see that each cell in the Monthly Average Percentage Change is now decreased to 2 decimal points.

Calculate Average Percentage Change Using Formula

Step 3:

  • Now, we will calculate the average percentage change using all the monthly percentage changes. Write down the below formula in cell D12.
=SUM(D6:D10)/COUNT(D6:D10)

Formula Breakdown:

  • The SUM formula will sum up all the cell values in the range D6:D10.
  • And the COUNT formula will count all the cells in the range D6:D10.

Calculate Average Percentage Change Using Formula

  • Upon pressing ENTER, we will now see the average percentage change in Sales Volume for all the sales volumes for the 6 months.

Calculate Average Percentage Change Using Formula

Read More: How to Calculate Average in Excel Excluding 0 (2 Methods)


2. Apply Percent Format to Calculate Average Percentage Change

We can also use the Percent Format to calculate the average percentage change. Just follow the below steps.

Step 1:

  • First, we will click on the Percent Style button several times from the Number under the Home It will change our Number Format to Percentage.

Apply Percent Format to Calculate Average Percentage Change

Step 2:

  • Then, we will write the below formula in cell D6.
=(C6-C5)/C5

Apply Percent Format to Calculate Average Percentage Change

  • Upon pressing ENTER, we will now see the percentage change in Sales Volume between the month of January and February.

Apply Percent Format to Calculate Average Percentage Change

Step 3:

  • Now, we will drag the fill handle of cell D6 to apply the formula to the rest of the cells.

drag the fill handle

  • Finally, we will see the percentage change in Sales Volume between each month and its preceding month.

Apply Percent Format to Calculate Average Percentage Change

Step 4:

  • Now, we will calculate the average percentage change using all the monthly percentage changes. Write down the below formula in cell D12.
=SUM(D6:D10)/COUNT(D6:D10)

Formula Breakdown:

  • The SUM formula will sum up all the cell values in the range D6:D10.
  • The COUNT formula will count all the cells in the range D6:D10.

Apply Percent Format to Calculate Average Percentage Change

  • Upon pressing ENTER, we will now see the average percentage change in Sales Volume for all the sales volumes for the 6 months.

Apply Percent Format to Calculate Average Percentage Change

Step 5:

  • Instead of calculating the percentage change in sales between two consecutive months, we can also calculate the change in sales between January and every other month.
  • First, we will write the below formula in cell D6.
=(C6-$C$5)/$C$5

Formula Breakdown:

We have inserted Dollar signs ($) in cell C5 that represents the Sales Volume in the month of January. Inserting the Dollar signs ($) will make the cell reference absolute ($C$5). So, if we now drag the fill handle downward, the cell reference $C$5 will not change and the formula will always calculate the change in sales between January and every other month.

Apply Percent Format to Calculate Average Percentage Change

  • Upon pressing ENTER, we will now see the average percentage change in Sales Volume between the month of January and February.

Apply Percent Format to Calculate Average Percentage Change

Step 6:

  • Now, we will drag the fill handle of cell D6 to apply the formula to the rest of the cells.

drag the fill handle

  • Finally, we will see the percentage change in Sales Volume between January and every other preceding month.
  • We will also see the average percentage change in Sales Volume for all the sales volumes of the 6 months.

Apply Percent Format to Calculate Average Percentage Change

Related Content: How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)


Similar Readings: 


3. Run a VBA Code to Calculate Average Percentage Change

If you are familiar with the VBA macro, then you can also use the VBA macro to calculate the average percentage change more efficiently. Just follow the below steps.

Step 1:

  • First, we will select Visual Basic from the Developer. We can also press ALT+F11 to open it.

Visual Basic from the Developer

  • Now, click on the Insert button and select Module.

click on the Insert button and select Module

Step 2:

  • Then, write down the following code in the window that appears.
Sub Average_Percentage_Change()
init_cell = InputBox("Insert the First Cell Reference (Ex. B5)")
num_cells = InputBox("How many values are there in the data range? (Ex. 10)")
result_cell = InputBox("Which cell will show the result? (Ex. D12)")
 init_cell_num = Int(Right(init_cell, 1))
init_cell_letter = Left(init_cell, 1)
final_cell_num = Int(init_cell_num) + Int(num_cells) - 2
Sum = 0
For i = init_cell_num To final_cell_num
    Range("D" & i + 1) = (Range(init_cell_letter & i + 1) - Range(init_cell_letter & i))
    Range("D" & i + 1) = Range("D" & i + 1) / Range(init_cell_letter & i)
     Range("D" & i + 1) = Round((Range("D" & i + 1) * 100), 2)  
        Next
For i = init_cell_num To final_cell_num
Sum = Sum + Range("D" & i + 1)
   Next
Range(result_cell) = Round(Sum / (num_cells - 1), 2)
 End Sub

 Step 3:

  • Now, click on the Run.

click on the Run

  • Now, if a window named Macro appears, just click on Run from that window.
  • Then, a window with the prompt Insert the First Cell Reference (Ex. B5)” will appear. We will insert C5 in the input box as it has the first Sales Volume.

Prompt Box

  • Then, another window with the prompt How many values are there in the data range? (Ex. 10)” will appear. We will insert C5 in the input box as have the Sales Volumes for 6 months.

Prompt Box

  • Finally, the last prompt will appear which will ask youWhich cell will show the result? (Ex. D12). We will insert D12 in the input box.

Prompt Box

  • Then, we will see the percentage change in Sales Volume between the consecutive months.
  • We will also see the average percentage change in Sales Volume for all the sales volumes of the 6 months in cell D12.

Run a VBA Code to Calculate Average Percentage Change

Read More: Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)


Things to Remember

  • If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.
  • To open the VBA editor Press ALT + F11.
  • You can press ALT + F8 to bring up the Macro window.

Conclusion

In this article, we have learned how to calculate the average percentage change in Excel. I hope from now on you can calculate the average percentage change in Excel very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo