Increasing a value by a percentage is simple
Imagine you have a value, such as 15,002.56. You know that it is 23% higher than last month’s value and you want to find out what last month’s value was.
Your immediate instinct might be to multiply the number by 0.77 (77%) with a formula like this:
=15002.56*0.77
This results in a value of 11,551.9712, but unfortunately it is not the correct answer. Adding 23% to this value results in 14,208.92458 rather than the correct value of 15,002.56.
If you’re not familiar with Excel formulas you might find our free Basic Skills E-book extremely useful. Basic Skills explains the basics of Excel formulas, as well as many other Excel basics.
The solution is simple when you realize that 15,002.56 represents 123% of last month’s value. This means you can get 1% of last month’s value by dividing it by 123, and can calculate last month’s true value with this formula:
=(15002.56/123)*100
This gives you the correct result of 12,197.20325.
Rounding the result
If the number in question was a financial value (as is often the case in Excel worksheets), you probably wouldn’t want to keep the 5 decimal places shown in the result. You could hide the decimal places using Excel’s formatting options, but this could result in ‘off by one’ errors when summarizing the data later on.
The best solution is to use the ROUND function to round the result to 2 decimal places. The final formula is therefore:
=ROUND((A1/123)*100,2)
This gives you a much cleaner result of 12,197.20.
Decimal places and the ROUND function are explained in more depth in our Essential Skills Books and E-books.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Some of the things you will learn
2 Responses
Hi, this is very helpful but, not exactly what I am looking to solve.
Here is the real world example I am trying to figure out:
I’ll keep the numbers simple to make it as easy as possible to build the Excel formula (and an example where you know the answer .
You have TWO pieces of information (column A and column B).
Column A your is current price of stock, $20,
Column B is the other piece of info you are provided and is “% off peak price”, in this example it is -80%.
Column C is where the formula needs to be built. In this example you know that if you are at $20 and are 80% off peak you need to a 400% % increase to get back to your starting price (in this case we know that is $100, because to grow from $20 to $100 is a 400% gain.
I am trying to build a formula for column C that uses the actual data in column A (current stock price in $) and in Column B (the % off peak stock price).
It is probably a simple solution but, I simply cannot figure out how to build it in Excel (and I was a Math major!).
Thank you,
Dan
Hi Dan
If you only want to cater for negative percentages then (assuming your values are in row 1) the formula you need is:
=A1/(1+B1)
If you wanted to cater for positive percentages as well you would need to use an IF function with B1>0 as the logical test.