Month over month change

**searchmanager** · 03-08-2010, 06:05 PM

Hello

I am trying to calculate month over month percentage change in revenue and this is for 6 months. What formula would I use?

here are the values from July to Feb
$31,099 $46,895 $83,447 $234,181 $324,901 $432,667 $74,268 $110,715

Thank you

**darkyam** · 03-08-2010, 06:19 PM

If values are in A2:H2, B3 could have =B2/A2-1 and that could be dragged over as needed.

**searchmanager** · 03-08-2010, 06:31 PM

hmm..but how would that tell me what the average change has been. we are trying to figure out the % change over six months. (i am presuming i am looking for avg here).

**darkyam** · 03-08-2010, 06:35 PM

"Average" can mean a few different things. You could mean an average the month over month changes (your title threw me off as to what you're looking for), a weighted average based on the last month's $, a six-month over six-month change, or simply the change from July to January divided by six. Which do you want?

**searchmanager** · 03-08-2010, 06:37 PM

oops. sorry.

so basically i am trying to see our revenue as it goes up and down over the months. what i am want to see is how much it has changed in the total six months. so if it went down 10 percent in one month and went up 20 percent in another, i finally want the number that would be more or less the true change over 6 months

**darkyam** · 03-08-2010, 06:45 PM

Methinks the total of six months would make most sense then. You'd need the last year's worth of data, so if that's in A2:L2, it'd be =SUM(G2:L2)/SUM(A2:F2)-1. If it's only the last month of that period you care about, it'd be =L2/F2-1.

**searchmanager** · 03-08-2010, 06:52 PM

I think you are right. i am attaching a file here. can you please plug that in so that i have it right the first time and can use this in future

thank you so much, this really helps

**darkyam** · 03-08-2010, 06:56 PM

Nope, you didn't include the last year's worth of data, so doing the change over six months won't work here. If you want the last suggestion of Jan over July, use =G2/A2-1.

**DonkeyOte** · 03-09-2010, 01:50 AM

Originally Posted by searchmanager

I am trying to calculate month over month percentage change in revenue and this is for 6 months.

It might be an idea to outline the results you expect based on your sample.

If the answer you're looking for is the average of the % movement from month to prior month for six months ie:

49.45% - {77.95%;180.63%;38.74%;33.17%;-82.83%;49.08%} [Sep % change from Aug through to Feb % change from Jan]

then perhaps:

=SUMPRODUCT(C2:H2/B2:G2-1)/6

Though I'm not convinced that's the best measurement necessarily depending on what you want ... the above is perhaps a good indication of the Volatilty of the measure over that period but other than that... (?)

If you total the prior 6 months and compare that to the same but for the prior month, eg: Sep-Feb vs Aug-Jan:

=SUM(C2:H2)/SUM(B2:G2)-1

the difference will be 5.33% - ie $1,260,179 / $1,196,358 - 1

However the above could also be misleading stat. if you have significant seasonality at which point a YOY analysis would perhaps make most sense.

Regardless... it follows that whichever route you implement you will need to adapt the formulae so as to make "dynamic" given you're presumably adding data all the time ?

At which point the key questions become:

a) where will the calculation reside in relation to your data (ie cell reference)

b) will the data points always be contiguous - ie no blanks interspersed amongst valid values