Need Formula to Average Monthly Increase

[rerhart]

I have a spreadsheet where there are columns for each month of the year
Each cell contains a number of loan submissions that graduall
increases per month as we gain more business from each mortgage vendor
I need a formula that can look at multiple cells in a row (i.e
Jan-Dec) per vendor and then give me a result that shows the averag
monthly percentage increase factor in submissions...

Example:
MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A SUBS 1 5 18 29 30 40 57 85 150 275
.........


..I will be using this data fo forecasting future submissions pe
vendor.

Thanks!
-

 

[Ron Rosenfeld]

I have a spreadsheet where there are columns for each month of the year.
Each cell contains a number of loan submissions that gradually
increases per month as we gain more business from each mortgage vendor.
I need a formula that can look at multiple cells in a row (i.e.
Jan-Dec) per vendor and then give me a result that shows the average
monthly percentage increase factor in submissions...

Example:
MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A SUBS 1 5 18 29 30 40 57 85 150 275
........


.I will be using this data fo forecasting future submissions per
vendor.

Thanks!
-R

You probably want to use either the GROWTH or FORECAST formulas depending on
how you think things will progress. The FORECAST essentially fits a straight
line to the data; the GROWTH function assumes exponential growth. Neither
assumption is likely to be correct.

One way of obtaining the average monthly increase, though:

Assume your data above in A1:K1 with Jan in B1 and '1' in B2.

Your last month with data is OCT which is in K1.

=(AVERAGE(C2:K2)-AVERAGE(B2:J2))/AVERAGE(B2:J2)

should give the average monthly increase.


--ron

 

[AlfD]

Hi!

I think I need to know a bit more about your concept of
"the average monthly percentage increase factor in submissions".

The percentage increase from Jan to Feb in your figures is 400%. Fro
Feb to Mar, 260%: Mar to Apl, 61%; Apl to May 3% etc.

The average monthly increase would be 400% in Feb: 330% in Mar (averag
of 400 and 260): 240 in Apl (average of 400,260 and 61).

Is this the sort of figure you are looking for?

I have to say, I don't as yet know what these numbers _mean_ : jus
that I can compute them if I have to.

Questions: Does the averaging start anew each year or does it roll ove
into next year?
If it rolls over, do you continue to count all months from you
original (arbitrary?) starting month or do you at some point dro
front-end months and add in back-end months? (The notion of rollin
average, say)
There may well be others. One thing I would certainly beware is takin
averages of averages.

Al