+ Reply to Thread
Results 1 to 4 of 4

Return Month-on-Month growth using current month-prior month/prior month per country

Hybrid View

  1. #1
    Registered User
    Join Date
    03-25-2020
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    53

    Return Month-on-Month growth using current month-prior month/prior month per country

    Hello

    In the Period tab, I'd like to return the Month-on-Month growth per Fiscal Period for each country, by using the data from the Budget tab comparing prior month to current month. So for example, for Period's 2020P9 for Country AU, I need a formula that calculates the month-on-month growth (in %) by looking at the increase/decrease. So (28-32)/28. 28 being AU's numbers for prior month, 2020P8, and 32 being AU's numbers for current month

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    3,168

    Re: Return Month-on-Month growth using current month-prior month/prior month per country

    I think if you want period growth, for AU Period 2020P9 it would be (32-28)/28, right?
    Now, if your years were always the same on both sheets (like they are now) and if the list of countries was in the same order (like it is now), you could simply use this formula in C8 and then copy it down and across:
    =(Budget!E2-Budget!D2)/Budget!D2

    If they were more mixed up, you could try this formula in cell C8. You can copy this down and across.

    =(INDEX(Budget!$D$2:$AA$17,MATCH(Period!$A8,Budget!$A$2:$A$17,0),MATCH(Period!C$7,Budget!$D$1:$AA$1,0))-INDEX(Budget!$D$2:$AA$17,MATCH(Period!$A8,Budget!$A$2:$A$17,0),MATCH(Period!C$7,Budget!$D$1:$AA$1,0)-1))/INDEX(Budget!$D$2:$AA$17,MATCH(Period!$A8,Budget!$A$2:$A$17,0),MATCH(Period!C$7,Budget!$D$1:$AA$1,0)-1)

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Return Month-on-Month growth using current month-prior month/prior month per country

    In C8:

    In case of ( 28-32)/28

    =SUMPRODUCT(VLOOKUP($A8,Budget!$A$1:$AA$17,MATCH(C$7,Budget!$A$1:$AA$1,0)+{-1,0},0)*{-1,1})
    /VLOOKUP($A8,Budget!$A$1:$AA$17,MATCH(C$7,Budget!$A$1:$AA$1,0)-1,0)
    In case of (32-28)/28:

    =SUMPRODUCT(VLOOKUP($A8,Budget!$A$1:$AA$17,MATCH(C$7,Budget!$A$1:$AA$1,0)+{-1,0},0)*{1,-1})
    /VLOOKUP($A8,Budget!$A$1:$AA$17,MATCH(C$7,Budget!$A$1:$AA$1,0)-1,0)
    Attached Files Attached Files
    Quang PT

  4. #4
    Registered User
    Join Date
    03-25-2020
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    53

    Re: Return Month-on-Month growth using current month-prior month/prior month per country

    Thanks for that, looking for the increase from prior month always, so I think the second one is the one I'm after

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Subtracting Data Dynamically Between New Month and Prior Month?
    By Dan1027 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-16-2020, 01:32 PM
  2. Vba to filter current month, prior month, two months ago and three months ago
    By M7A0S8T8E5R in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2019, 06:56 PM
  3. [SOLVED] date formula that puts the first of the prior month based on which month it is
    By cmorten82 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-08-2015, 05:38 PM
  4. Convert date from current month to first day of prior month
    By maacmaac in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-07-2010, 12:23 PM
  5. Calculating the prior month given the current month
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-15-2010, 04:40 PM
  6. Replies: 4
    Last Post: 02-14-2010, 05:21 AM
  7. formula for prior month & year of a month end date.
    By mikeburg in forum Excel General
    Replies: 2
    Last Post: 09-25-2007, 04:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1