A common task for an Excel analyst is to apply a percentage increase or decrease to a given number. For instance, when applying a price increase to a product, you would typically raise the original price by a certain percent. When giving a customer a discount, you would decrease that customer’s rate by a certain percent.
The screenshot below illustrates how to apply a percent increase and decrease using a simple formula. In cell E5, you apply a 10 percent price increase to Product A. In Cell E9, you give a 20 percent discount to Customer A
 |
| % decrease and increase using simple formula |
How it works
To increase a number by a percentage amount, multiply the original amount by 1+ the percent of the increase. In the example in Screenshot, Product A is getting a 10 percent increase. So you first add 1 to the 10 percent, which gives you 110 percent. You then multiply the original price of 100 by 110 percent. This calculates to the new price of 110.
To decrease a number by a percentage amount, multiply the original amount by 1- the percent of the increase. In the example in Screenshot, Customer A is getting a 20 percent discount. So you first subtract 20 percent from 1, which gives you 80 percent. You then multiply the original 1,000 cost per service by 80 percent. This calculates to the new rate of 800.
Note the use of parentheses in the formulas. By default, Excel’s order of operations states that multiplication must be done before addition or subtraction. But if you let that happen, you would get an erroneous result. Wrapping the second part of the formula in parentheses ensures that Excel performs the multiplication last.
No comments:
Post a Comment