rate of change using dates and number values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have combinations of dates with numerical values. How can I use Excel to
calculate the rate of change over a given unit of time such as change per
month or per quarter, or per year. An example of my data sets would be:
9/29/2004 19.8
4/5/2004 15.6
10/3/2003 16.3
6/30/2003 15.8
2/3/1999 12.4
7/7/1998 11.9

Thanks, Ronan
 
I have combinations of dates with numerical values. How can I use Excel to
calculate the rate of change over a given unit of time such as change per
month or per quarter, or per year. An example of my data sets would be:
9/29/2004 19.8
4/5/2004 15.6
10/3/2003 16.3
6/30/2003 15.8
2/3/1999 12.4
7/7/1998 11.9

Thanks, Ronan
Click to expand...

The most likely easiest way is to use a pivot table. You can define
there the times to be displayed. e.g. months, quarters etc.
Let me know if you need some support.

Regards,
Michael
http://bereichverschieben.blogspot.com
 
I have combinations of dates with numerical values. How can I use Excel to
calculate the rate of change over a given unit of time such as change per
month or per quarter, or per year. An example of my data sets would be:
9/29/2004 19.8
4/5/2004 15.6
10/3/2003 16.3
6/30/2003 15.8
2/3/1999 12.4
7/7/1998 11.9
Click to expand...

That depends on your definition of "rate of change". For some people,
that refers to the percentage change (growth rate). Other people
speak of "rate of change" to mean "amount of change per unit time".

Assuming the table above is in A1:B6, the __amount__ of change per day
is simply

=(B1 - B6) / (A1 - A6)

Multiply by 30 (or 365/12), 120 (or 365/4) and 365 to estimate
monthly, quarterly and annual change. That assumes linear growth.
Use Excel Chart to see if you agree with that assumption.

The __percentage__ change per day can be computed in one of two ways:

a. =(B1/B6) ^ (1 / (A1-A6)) - 1

b. =(B1/B6 - 1) / (A1-A6)

The first formula (a) assumes that change compounds. The following
are two equivalent ways to annualize that:

=(B1/B6) ^ (365 / (A1-A6)) - 1

=(B1/B6) ^ (1 / (A1-A6)) ^ 365 - 1

Replace 365 with 30 and 120 (or 365/12 and 365/4) to compute monthly
and quarterly rates. However, sometimes the "square root of time"
rule is used to convert between units of time. Google that for
details.

The second formula above (b) assumes that change does not compound.
It is consistent with the linear-change assumption made in the "amount
of change" formula above. Multiply by 30 (or 365/12), 120 (or 365/4)
and 365 to convert to monthly, quarterly and annual percentages.
 
Thank you for the reply. Unfortunately, when I use the formulas with the
appopriate letter/number combination for the series of data, the result is
always 1/0/1900, which doesn't make sense. Is there something wrong with my
settings? Thanks, Ronan
 
Thank you for the reply. Unfortunately, when I use the formulas with the
appopriate letter/number combination for the series of data, the result is
always 1/0/1900, which doesn't make sense. Is there something wrong with my
settings?
Click to expand...

No. You just need to change the format (Format -> Cells -> Number) to
Number or Percentage with some number of decimal places.
 
thanks again for the help.
I want to work with the "amount of change per unit time", but I want to take
all values in the data series into account. I noticed that the formula
B1-B6/A1-A6 in my example only makes use of the first and last date and value
pairs.

What I would like to do is to have the formula that would use all the data
points in a way that would be the equivalent of the slope of a linear
trendline. I can graph the points in Excel using a time-scale axis and
select a linear trend line, but I have no idea what the value of the slope is
for that trendline. Is there a way to get that value, or a formula that
would give me the same information. Thanks again.
 
What I would like to do is to have the formula that would use all the data
points in a way that would be the equivalent of the slope of a linear
trendline. I can graph the points in Excel using a time-scale axis and
select a linear trend line, but I have no idea what the value of the slope is
for that trendline. Is there a way to get that value, or a formula that
would give me the same information.
Click to expand...

First, using Chart Trendline, if you go to the Options tab, you can
select Display Equation and Display R-Squared. The latter provides
some indication of closeness of fit; the closer to 1 the better.

Second, in a cell, you can use the SLOPE() and INTERCEPT() functions.
Since date serial numbers are in days, the unit of the slope is days.
You can compute any point on the trendline by:

=B7*slope(B1:B6,A1:A6) + intercept(B1:B6,A1:A6)

where B7 contains a date.
 
thanks again for the help.
I want to work with the "amount of change per unit time", but I want to take
all values in the data series into account. I noticed that the formula
B1-B6/A1-A6 in my example only makes use of the first and last date and value
pairs.

What I would like to do is to have the formula that would use all the data
points in a way that would be the equivalent of the slope of a linear
trendline. I can graph the points in Excel using a time-scale axis and
select a linear trend line, but I have no idea what the value of the slope is
for that trendline. Is there a way to get that value, or a formula that
would give me the same information. Thanks again.
Click to expand...

You could determine the change per time (effectively slope) for all pairs of
points, but if (say) you were just going to then average that data, then
just using the first and last points would be as useful ... and easier. :)
 
Back
Top