Step 1 – Create the Dataset
- List down some items in column B.
- We put the price for each item in column C.
- The 10% discount for each item is in column D.
- To calculate the discount price, we have inserted a new column (column E) named Discounted Price.
Step 2 – Input the Formula
The formula to get the discount % is.
Discounted Price = Original Price – (Original Price * Discount Percentage)
- Select the cell where you want to put the formula for the calculation of the 10% discounted price. We selected cell E5.
- Insert the following formula into the selected cell.
=C5-(C5*D5)
- Press Enter to see the result.
- Drag the Fill Handle down to duplicate the formula over the range or, to AutoFill the range, double-click on the plus (+) symbol.
Final Output
- Here’s the result.
Note: Sometimes we don’t have the discount percentage showing in the dataset, but the customer gets a 10% discount for a specific item. For this, you can also calculate the discounted price by just using .01 instead of using the 10%.
How to Calculate the Original Price from a 10 Percent Discount in Excel
Here’s the formula you can use:
Original Price = 1/(1-Discount Percentage)* Discount Price
STEPS:
- Choose the cell where you wish to enter the formula to determine the original price of 10%. We chose cell E5.
- Insert this formula into the cell you just chose.
=1/(1-C5)* D5
- Press Enter.
- Drag the Fill Handle down or double-click on the plus (+) symbol.
- Column E displays the original price for each item.
Things to Remember
- Applying the Percentage format to the cells will allow you to display a number in Excel as a percentage. For this, go to the Home tab from the ribbon, and choose the Percent Style (%) button in the Number group.
- You may adjust the decimal place as necessary. Click on Decrease Decimal or Increase Decimal for rounding.
- Excel always keeps the numeric value as a fundamental value. Therefore, even if you formatted a number to show something as a percentage (10%), all that is happening is formatting or a visual meaning of the true value. Excel automatically makes computations on that decimal’s fundamental number (0.1). To solve this, pick the cell, and press Ctrl + 1. Then, check the Text box under the General menu to confirm the fundamental value.
Download the Practice Workbook
<< Go Back to Discount | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!