Month on Month +/- Percentages

Donal28

Well-known Member
Joined
Apr 23, 2010
Messages
527
Hi All

I am trying to do a calculation for Month on Month % figures for a list of figures but am finding running into difficulty as some of the percentages are + and some are -. See table below for the calculations I am trying to do so for examples the Month on Month +/- % for 2012-10 is -30.02% as that is the drop in percentage between 39191 and 30143 and the Month on Month +/- % for 2012-11 is 188.20% as that is the rise in percentage between 30143 and 86874.


Month Totals Month on Month +/- %
2012-09 39191
2012-10 30143 -30.02%
2012-11 86874 188.20%
2012-12 126439 45.54%
2013-01 73452 -41.9%
2013-02 105000 42.95%
2013-03 94822 -9.69%

Any help on this would be very much appreciated

Regards
Donal
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hello,

With the years starting from A1, and the totals from B1, how about this formula from C2 and copy downwards?

=(B2-B1)/B2, then format as percentage.

Let me know if it works for you.
 
Upvote 0
Hello,

With the years starting from A1, and the totals from B1, how about this formula from C2 and copy downwards?

=(B2-B1)/B2, then format as percentage.

Let me know if it works for you.

Thanks for the reply miss_ell, unfortunately this doesn't return the correct totals for all calculations...see below I have posted the totals it returns in the last column marked (B2-B1)/B2 Formula

2012-09 39191 (B2-B1)/B2 Formula
2012-10 30143 -30.02% -30.02%
2012-11 86874 188.20% 65.30%
2012-12 126439 45.54% 31.29%
2013-01 73452 -41.9% -72.14%
2013-02 105000 42.95% 30.05%
2013-03 94822 -9.69% -10.73%
 
Upvote 0
Hello,

It appears that you're asking for two different results - one the percentage of the previous year and one of the current year depending on which figure is greater. Try this:

=IF(B2 > B1,(B2-B1)/B1,(B2-B1)/B2)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top