Percent reverse formula in excel

[Caly]

In excel when calculating a percentage decrease on a price band then adding that percentage the answer is different. How do I fix my formula? Price of $429 And then when taking 7% off that price comes to $398.97. But then as a check, adding 7% to $398.97 equals $426.89. Why does it not equal The original price of $429?

 

[mikerickson]

Because that's what percentages do. This has nothing to do with Excel and everything to do with arithmetic.

Lets make the numbers easy.
Price is 100. Take off 7% and we get $93.

Since 93 is less than 100, 7% of 93 is less than 7% of 100.

So when one ads 7% of 93, that making up for less than the amount that was taken off in the discount in the first place.

I think that your general question is "if X is the result of taking p% off of a price, what is the orginal price."

So, we are given X and p, let O be the original price, we know that X = O*(1-p)

or O = X/(1-p)

As a check, if 398.97 is the result of discounting a price by 7%, 398.97/(1-.07)=429.00

So if given the discounted price X and the amount of discount, p, the original price is = X/(1-p)

 

[KRice]

I was in the middle of working up a response when I saw the one from @mikerickson. I'll add just a little a more and point out the the two operations aren't inverses because the basis of your percentages are different. Following Mike's example, the original price O - p*O = X, or we can express this as X = O*(1-p).

If you want some way to check in the other direction...that is, given X, how do I obtain O based on the original discount percentage, you need to determine a different percentage p' based on price X, so...
X * (1+p') = O, or substituting for X, we get O*(1-p)*(1+p') = O. And solving for p', we get p' = p/(1-p)...this is the formula you want.

For the example described, if a 7 % discount off O gives X, then X increased by p' = 0.07/(1-0.07) ~ 7.527 % will return O.

 

[Caly]

Thank you both so much! This really helped and explained the issue to understand it properly. Very much appreciated.

 

[ashakantasharma]

The difference is due to the fact that in the first instance you are calculating 7% on 429 which is 30.03 and you are deducting the same to arrive at 398.97. Now you want to add 7% on 398.97 (27. 92790) which will be less then 7% calculated earlier on 429 as the amount is being calculated on reduced amount so while adding you will get only 426.89790 and not 429.

The difference is mainly due to the fact that you are calculating 7% on reduced amount. If you add the difference on 7% calculations (30.03 - 27.92790 = 2.10210 ) which can be added to 426.89790 + 2.10210 to arrive to 429.

 

[Caly]

Thank you so much!

In excel this is what I am doing but which way would you advise?
Original price 429
Percent deduction 7%
Resulting price 398.97

one way
=398.97*(1+7%) this comes to 426.90

another way
=398.97/(1-7%) this comes to 429

 

[sparky2205]

You can't start with your result so: =429*(1-0.07) Result 398.97

Simple check: (once you have your result)
=429*.93=398.97
=398.97/.93=429

 

[Caly]

Thank you so much to everyone for your help and explanations. This helped me tremendously... very greatly appreciated.