Jump to content


Photo

Calculating the original number after a percentage increase

formulas ROUND percentages

  • Please log in to reply
1 reply to this topic

#1 Stewart Armstrong

Stewart Armstrong

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 18 June 2014 - 07:41 PM

I've been asked to show our accounting people how to write an Excel formula that can calculate the original value after a number has increased by a certain percentage.

 

For example, we know that the value of 15,002.56 increased by 23% on last month's value and need to work out what last month's figure was.  It seems like it should be simple, but I've been trying for a while now and just can't get it.  Can you help?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 18 June 2014 - 09:11 PM

Hi Stewart,

 

The formula side of things probably isn't the challenge here so much as the math, but I think I can help.

 

If you know that the figure this month is 23% higher than last month's, it can be said that this month's figure is 123% of last month's figure.  This means you can get 1% of last month's figure by dividing this month's by 123: 15,002.56 / 123 = 121.9720325203252

Now you can easily calculate last month's figure by simply calculating: 121.97 * 100 = 12197.20325203252

...or rounded down to 2 decimal places: 12197.20

 

Calculating this in Excel should be easy enough if you have an understanding of simple formulas.  The formula will look something like this:

=ROUND((15002.56/(100+23))*100,2)

 

The above formula uses the ROUND function to round the result to 2 decimal places.

For more on rounding and the ROUND function, see the video and e-book lesson: Essential Skills Lesson 4-3: Format numbers using built-in number formats.

 

Aside from the ROUND function, this is a simple mathematical formula.  For an introduction to creating formulas like this one, see the video and e-book lesson: Essential Skills Lesson 2-12: Create your own formulas.

 

I have attached an example spreadsheet showing an example of this formula in action.

 

Attached File  PercentageIncrease.xlsx   8.88KB   903 downloads

percentageincrease.png

 

Please feel free to reply if you need any more assistance with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






3 user(s) are reading this topic

0 members, 3 guests, 0 anonymous users