Method 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 shown below.
The formula below is for calculating the average percentage change.
Steps:
- Enter the following formula in cell D6 to calculate the percentage changes in Sales Volume between the months of January and February.
=(C6-C5)/C5 * 100Formula Breakdown
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 the Sales Volume for the preceding month to consider it as an Initial Value.
We can not calculate the percentage change in sales for the month of January as we do not have the Sales Volume for the preceding month to consider it as an Initial Value.
- Press ENTER. It will output the percentage change in Sales Volume between the months of January and February.
- Drag the Fill Handle of cell D6 to apply the formula to the rest of the cells.
- It will output the percentage change in Sales Volume between each month and the preceding month.
- Select all the cells with values under the Monthly Percentage Change column, click on the Decrease Decimal button several times from the Number under the Home tab. Each click will decrease the decimal by one place.
- Each cell in the Monthly Percentage Change will decrease to 2 decimal points.
- We will calculate the average percentage change using all the monthly percentage changes. Enter the following formula in cell D12.
=SUM(D6:D10)/COUNT(D6:D10)Formula Breakdown
- The SUM function will sum up all the cell values in the range D6:D10.
- And the COUNT function will count all the cells in the range D6:D10.
- Press Enter. It will output the average percentage change in Sales Volume for all the sales volumes for the 6 months.
Read More: How to Calculate Percentage Increase Between Three Numbers in Excel
Method 2 – Apply Percent Format to Calculate Average Percentage Change
Steps:
- Click on the Percent Style button several times from the Number under the Home tab. It will change the numbers into percentages.
- Enter the following formula in cell D6.
=(C6-C5)/C5
- Press ENTER. The percentage change in Sales Volume between the months of January and February will be displayed.
- Drag the fill handle of cell D6 to AutoFill the formula to the rest of the cells.
- It will output the percentage change in Sales Volume between each month and the preceding month.
- We will calculate the average percentage change using all the monthly percentage changes. Enter the following formula in cell D12.
=SUM(D6:D10)/COUNT(D6:D10)Formula Breakdown
- The SUM function will sum up all the cell values in the range D6:D10.
- The COUNT function will count all the cells in the range D6:D10.
- Press ENTER. The average percentage change in Sales Volume for all the sales volumes for the 6 months will be displayed.
- 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.
- Enter the following formula in cell D6.
=(C6-$C$5)/$C$5Formula Breakdown
We have inserted Dollar ($) signs in cell C5 that represent 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.
- Press Enter. The average percentage change in Sales Volume between the month of January and February will be displayed.
- Drag the fill handle of cell D6 to apply the formula to the rest of the cells.
- It will output the percentage change in Sales Volume between January and every other preceding month.
- It will also output the average percentage change in Sales Volume for all the sales volumes of the 6 months.
Read More: Calculate Percentage Difference Between Two Numbers in Excel
Method 3 – Run a VBA Code to Calculate Average Percentage Change
Steps:
- Select Visual Basic from the Developer. We can also press ALT+F11 to open it.
- Click on the Insert button and select Module.
- Enter the following code in the module window.
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- Click on Run.
- A window with the prompt Insert the First Cell Reference (Ex. B5) will pop up. We will insert cell C5 in the input box as it has the first Sales Volume.
- Another window with the prompt How many values are there in the data range? (Ex. 10) will open.
- We will insert cell C5 in the input box as have the Sales Volumes for 6 months.
- The last prompt will pop up will ask you Which cell will show the result? (Ex. D12). We will insert cell D12 in the input box.
- The percentage change in Sales Volume between the consecutive months will be displayed.
- It will output the average percentage change in Sales Volume for all the sales volumes of the 6 months in cell D12.
Read More: How to Calculate Year over Year Percentage Change in Excel
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.
- Press ALT + F8 to bring up the Macro window.
Download Practice Workbook
Related Articles
- Percentage Difference Between Two Percentages in Excel
- How to Show Percentage Change in Excel Graph
- How to Calculate Percentage Change with Negative Numbers in Excel
<< Go Back to Percentage Change | Calculating Percentages | Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
