How to calculate discount rate or price in Excel?
When Christmas is coming, there must be many sale promotions in shopping malls. But if the different kinds of items have different discounts, how can you calculate the discount rates or prices of the different items? Now, I talk about two formulas for you to calculate the discount rates and discount prices in Excel.
Calculate discount rate with formula in Excel
Calculate discount price with formula in Excel
If you want to follow along with this tutorial, please download the example spreadsheet.
Calculate discount rate with formula in Excel
The following formula is to calculate the discount rate.
1. Type the original prices and sales prices into a worksheet as shown as below screenshot:
2. Select a blank cell, for instance, the Cell C2, type this formula =(B2-A2)/ABS(A2) (the Cell A2 indicates the original price, B2 stands the sales price, you can change them as you need) into it, and press Enter button, and then drag the fill handle to fill this formula into the range you want. See screenshot:
3. Select the formula range cells, in this case, select the range C2:C5, right click > Format Cells. See screenshot:
4. In the Format Cells dialog, click Number > Percentage, and specify the decimal places then click OK. See screenshot:
There is an alternative method: firstly select the range, and then click the % (Percent style) in the Number group on the Home tab.
You can see the result as shown:
Tip: The result percentage reflect a percent discount on the item.
Calculate discount price with formula in Excel
If you have lists of data about the original prices and discount rate in a worksheet, and you can do as follow to calculate the sales prices.
Select a blank cell, for instance, the Cell C2, type this formula =A2-(B2*A2) (the Cell A2 indicates the original price, and the Cell B2 stands the discount rate of the item, you can change them as you need), press Enter button and drag the fill handle to fill the range you need, and the sales prices have been calculated. See screenshot:
Easily merge two tables and update the new items in Excel
|
| In some times, you may want to merge two tables into one, and update the itmes and add new ones. Kutools for Excel's Tables Merge utility can merge the Main table with the Lookup table into one, and update the main table as the preview shown. Click for full-featured free trial in 30 days! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
