Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2021, 2019, 2016 and Mac versions more…

Calculating the original number after a percentage increase

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.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Recent Articles

Calculating loan payment dates

Calculating Loan Payment Dates

This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.

Subtracting percentages in Excel

Subtracting percentages in Excel

This article explains how Excel handles percentages and shows you how to subtract a percentage from a number in Excel using two different methods.

2 Responses

  1. 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

    1. 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.

Leave a Reply

Your email address will not be published.

Which tutorial do you need?