Month on month % change formula

[Mika Taukave]

Hi

I have a table with basic performance metrics on it (refer attached file).
Every month, I would populate the results accordingly for each metric.

I actually need help with formulating the cells on column J.
I always have to change the formula on these cells every month to calculate the month-on-month (MoM) % change.

The MoM % change formula is denoted as: (current_month - base_month)/base_month
where the current_month and the base_month vary after every month.

So if today's 17 Dec, the current_month is Nov and the base_month will be Oct; therefore MoM %change is (Nov-Oct)/Oct
The formula would then change come January to reflect %change between Nov and Dec, so on and so forth.

Is there a formula to ensure that the % change is calculated accordingly without me having to worry about changing the formula every month?

Thanks in advance
MT

 

[SirJB7]

Hi, Mika Taukave!

As a new user you might want (I'd say should and must, but it seems as you at least partially did it yet) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, take care of this considerations:
a) Your formulas work and will work correctly while you keep the months range in K2:V2 as it is no. of months and base month, since your formula in column J refers to column V as base and U as current.
b) What will your month list be next January? Same columns K:V or one newly added before actual K? And in this case expanding the range to W or dropping actual Oct'13?

Depending on that the solution will be different.

Regards!

 

[Mika Taukave]

Hi SirJB7

Firstly, thank you heaps for the prompt response - I was actually anticipating an answer tomorrow

I'm currently going through the 'starter kit' for new users. I did this yesterday but not completely though. I realize now that I need to use the straight brackets [ ] instead of the curved ones ( )

Regarding your question, yes the month range is fixed at K2:V2. The same month list will be used every month until Oct 14.

Thanks again.
MT

 

[SirJB7]

Hi, Mika Taukave!

If your date list will be of 12 columns, always with the same dates (1st day of each month backwards from last to 1st column), you can try this:

J4: =(INDICE($K4:$V4;1;COINCIDIR(FECHA(AÑO(HOY());MES(HOY())-1;1);$K$2:$V$2;0))-V4)/V4 -----> in english: =(INDEX($K4:$V4,1,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),$K$2:$V$2,0))-V4)/V4

Month list: K2:S2 where actual dates, T2:V2 were strings representing dates, so:
V2: Base date: 01/mm/yyyyy
K2: =FIN.MES(L2;0)+1 -----> in english: =EOMONTH(L2,0)+1
Copy across K2 thru U2.

Just advise if any issue.

Regards!

 

[Mika Taukave]

Hi SirJB7

Thank you for this. Correct me if I'm wrong but I can see it calculating the % change using V4 as the base date.
However, the base date varies as we go in to a new month.

So from next month (Jan), my base date will be cell U4. The following month (Feb), base date then becomes cell T4, and the same pattern continues till Oct-14.

Could this be also factored in to the formula?

Thanks
MT

 

[SirJB7]

Hi, Mika Taukave!

I understood that for until Oct'14 the base month will be Oct'13 and the other involved month that of the month previous to actual date. As per your clarification, try this:

J4: =(INDICE($K4:$V4;1;COINCIDIR(FECHA(AÑO(HOY());MES(HOY())-1;1);$K$2:$V$2;0))-INDICE($K4:$V4;1;COINCIDIR(FECHA(AÑO(HOY());MES(HOY())-2;1);$K$2:$V$2;0)))/INDICE($K4:$V4;1;COINCIDIR(FECHA(AÑO(HOY());MES(HOY())-2;1);$K$2:$V$2;0)) -----> in english: =(INDEX($K4:$V4,1,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),$K$2:$V$2,0))-INDEX($K4:$V4,1,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1),$K$2:$V$2,0)))/INDEX($K4:$V4,1,MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1),$K$2:$V$2,0))

Regards!

 

[Mika Taukave]

Thank you so much SirJB7! Appreciate your expertise.

Regards
MT

 

[SirJB7]

Hi, Mika Taukave!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!