We’ll use a simple dataset with item prices and percentage discounts to subtract them from the original price.
Method 1 – Using a Percentage Formula to Subtract from a Price
Our discount values are given without a percentage (“%”). We’ll apply a percentage to these discounts and deduct it from the original “Price”.
Steps:
- Use the following formula in cell E5.
=C5-(C5*D5%)
We’re adding a percentage to values from the “Discount” column. We’re multiplying it by the values from the “Price” column. Then, we’re subtracting the result from the “Price”.
- Press Enter.
- Use the Fill Handle to AutoFill the formula into other cells.
- Here are all the results.
Method 2 – Subtracting a Percentage from a Price Using a Simplified Formula
Steps:
- Our “Discount” values are given in the percentage format.
- Use the following formula in cell E5.
=C5*(1-D5)
- Press Enter and auto-fill the formula.
- Here are the results.
Method 3 – Subtracting a Percentage in the Decimal Format from a Price
Step:
- Put the discount percentages in the decimal format.
- Select the cell range E5:E10.
- Use the following formula in cell E5.
=C5-(C5*D5)
This formula is the same as in the first method. We’re just omitting the percentage sign (“%”) here, as it is already given.
- Press Ctrl + Enter.
Method 4 – Applying VBA to Subtract a Percentage from a Price+
Steps:
- From the Developer tab, select Visual Basic.
The Visual Basic window will appear.
- From Insert, select Module.
- Insert the following code.
Sub SubstractPercentage()
Dim Discount As Range
Dim oldPrice As Range
Dim newPrice As Range
Set oldPrice = Range("C5:C10")
Set Discount = Range("D5:D10")
Set newPrice = Range("E5:E10")
For x = 1 To 6
newPrice(x) = oldPrice(x) * (1 - Discount(x))
Next x
End Sub
Code Breakdown
- We’re calling our Sub Procedure “SubstractPercentage”.
- We’re assigning our 3 variables as Range.
- We’re using the Set statement to define our ranges.
- A “For Next Loop” uses the iteration value up to 6, as there are 6 cells in our range. The Formula inside will Subtract Percentages.
- Click on Save.
- Click on the Run button.
The Macros dialog box will appear.
- Press Run.
- Here are the results.
Download the Practice Workbook
Related Articles
- Excel Formula to Add Percentage Markup
- How to Do Sum of Percentages in Excel
- How to Add Percentage to Price with Excel Formula
- How to Add 20 Percent to a Price in Excel
- How to Subtract a Percentage in Excel
- How to Use Excel Formula to Calculate Percentage of Grand Total
- How to Add 10 Percent to a Number in Excel
- How to Add 15 Percent to a Price in Excel
<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!