Aniruddah Alam
In this article, I will show how to calculate delta percentage in Excel. In many situations, we not only need the absolute change of a value but also the percentage change of that value( For example, change in the price of commodities, change in stock price, etc). Today, we will learn how we can calculate the percentage change (delta percentage) in those situations. So let’s start our journey.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
What Is Delta Percentage?
Delta ( Δ) is a greek letter that is used to express a change in any quantity. Hence, the delta percentage basically means the percentage change in any quantity. For example, if my weight in June is, W1= 70 Kg and in July, W2=75 Kg then the change in weight, ΔW=W2-W1=75-70=5 Kg.
Therefore, the Delta Percentage = (Change in Weight/Initial Weight)*100% = ΔW/W1*100%
4 Useful Methods to Calculate Delta Percentage in Excel
In this section, we will demonstrate 4 effective methods to calculate delta percentage in Excel. To illustrate, I have taken a dataset containing the stock’s old price and new price.
Now, we want to calculate the delta percentage in price (Change of price in Percentage). We will accomplish that by using the two methods mentioned below. Let’s look at the 1st method.
1. Use of Mathematical Formula
In the first method, we will use the regular formula to obtain the delta percentage. The formula that we will use here is below.
Delta Percentage = (New Value-Old Value)*100/ Old Value
To know more, follow the steps below.
Steps:
- In cell E5, write down the following formula.
=(D5-C5)/C5- Now, click Enter and you will see the desired result.
- If your result doesn’t show in percentage form, then you have to format the cell as Percentage.
- Now, use Fill Handle to get results for other cells.
In this way, we can get the delta percentage for the total dataset.
Read More: How to Calculate Delta in Excel (2 Ideal Examples)
2. Calculating Delta Percentage Using Excel Alternative Formula
In this method, we will use another alternate function to do our calculation. Delta percentage can also be calculated by the following formula.
Delta Percentage= (New Value/Old Value-1)*100%
To apply the function, follow the steps below.
Steps:
- In cell E5, write down the formula below.
=(D5/C5)-1- Now, press Enter and you will see the following result.
- Now, use the Fill Handle to get the results of the remaining cells.
This will give the delta percentage for the entire dataset.
Read More: How to Calculate Option Greek Delta in Excel (with Easy Steps)
3. Application of Excel SUM Function to Calculate Delta Percentage
We can also utilize the SUM function to calculate Delta Percentage in Excel. To do that, follow the steps below.
Steps:
- In cell E5, write down the following formula.
=SUM(D5,-C5)/C5*100- Now, click Enter and you will see the same answer as we saw in methods 1 and 2.
- Now, if you use the Fill Handle to apply the formula to the rest of the cells the result will be like this.
As a result, we can apply this formula to get the delta percentage for the complete dataset.
Read More: How to Calculate Delta E Color in Excel (4 Suitable Methods)
4. Use of VBA
Instead of manually using a mathematical formula, we can use VBA macro to calculate Delta Percentage in Excel. Here, we will first write a VBA Macro code then we will run the code to get Delta Percentage. To know more, follow the steps below.
Steps:
- First, open the VBA Developer window by pressing Alt+F11.
- Now open a new module by going to Insert > Module.
- Now, copy-paste the following code.
Sub Delta_Percent_Formula()
Dim AA_rOld As Range
Dim AA_rNew As Range
Dim sOld As String
Dim AA_sNew As String
Dim AA_sFormula As String
On Error GoTo ErrExit
Set AA_rNew = Application.InputBox( _
"Select the cell that contains the NEW Value", _
"Select New Cell", Type:=8)
Set AA_rOld = Application.InputBox( _
"Select the cell that contains the OLD Value", _
"Select Old Cell", Type:=8)
AA_sNew = AA_rNew.Address(False, False)
sOld = AA_rOld.Address(False, False)
AA_sFormula = "=IFERROR((" & AA_sNew & " - " & sOld & ")/" & sOld & ",0)"
ActiveCell.Formula = AA_sFormula
ErrExit:
End Sub
🎓How Does the Code Work?
Here, I have created a Subroutine named Delta_percent_Formula. Then I have declared 2 variables (AA_rold & AA_rNew) as Range type and 3 variables (sOld, AA_sNew and AA_sFormula) as String. Then we write code to open prompt windows asking New Value and Old Value Cells. After that, we calculate the delta percentage using the AA_sFormula. We also used some InputBox to set a prompt window to input the values.
- Now first select cell E5 then go to the developer window and run the code by clicking the F5.
- In a popped-up window, first, we need to select the cell that contains the New Value then click OK.
- Then, on the next popped-up window, we have to select the cell that contains the Old Value and then click OK.
- As a result, we will get the delta percentage in the selected cell.
- For the rest of the cells follow a similar procedure to obtain the delta percentage.
Finally, we can see that the VBA macro also yields the same results for every cell just like the previous methods.
Things to Remember
- While using the SUM function, be careful to input the minus(-) sign before the Old value cell reference.
Conclusion
That is the end of this article regarding how to calculate delta percentage in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.
