This is a surprisingly common problem that I have encountered many times over the years.
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:
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:
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:
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.