How to reference trendline equation
 

I am trying to put together a petroleum consumption projection for my
University and need to reference a trendline equation.

I have it set up so every year, we can enter in that years petrol.
consumption and the graph updates, then the trendline updates, but the
projected figures (a list going out to 2020) does not. This is because to get
the projections, I had to manually copy the trendline equation to the cell,
referencing appropriate cells (i.e. the year).

If I could set up an absolute reference to the equation, whenever it was
updated, the projected figures would also be updated but I cannot figure out
how to do this. Sorry for the wordy post, thanks for any help.

--
CMS

How to reference trendline equation
 

You want to use the slope and intercept functions to create the formula...
With the following data in Cells A1:C13

Date Sales
Jan 39.57395298 1
Feb 38.34244752 2
Mar 21.74347515 3
Apr 3.235821974 4
May 72.04120397 5
Jun 61.27490214 6
Jul 71.66341066 7
Aug 22.2055994 8
Sep 27.11134988 9
Oct 95.8971653 10
Nov 27.19312349 11
Dec 84.79849085 12

I used the formulas
=SLOPE(B2:B13, C2:C13) = 3.13448

and
=INTERCEPT(B2:B13, C2:C13) = 26.71596

to derive the formula
y = 3.1345x + 26.716
--
HTH...

Jim Thomlinson


"CMS" wrote:

I am trying to put together a petroleum consumption projection for my
University and need to reference a trendline equation.

I have it set up so every year, we can enter in that years petrol.
consumption and the graph updates, then the trendline updates, but the
projected figures (a list going out to 2020) does not. This is because to get
the projections, I had to manually copy the trendline equation to the cell,
referencing appropriate cells (i.e. the year).

If I could set up an absolute reference to the equation, whenever it was
updated, the projected figures would also be updated but I cannot figure out
how to do this. Sorry for the wordy post, thanks for any help.

--
CMS

How to reference trendline equation
 

You may wish to look at functions such as FORECAST and LINEST ?
--
David Biddulph

"CMS" wrote in message
...
I am trying to put together a petroleum consumption projection for my
University and need to reference a trendline equation.

I have it set up so every year, we can enter in that years petrol.
consumption and the graph updates, then the trendline updates, but the
projected figures (a list going out to 2020) does not. This is because to
get
the projections, I had to manually copy the trendline equation to the
cell,
referencing appropriate cells (i.e. the year).

If I could set up an absolute reference to the equation, whenever it was
updated, the projected figures would also be updated but I cannot figure
out
how to do this. Sorry for the wordy post, thanks for any help.

--
CMS

How to reference trendline equation
 

Thanks Jim. I found a way around this through using the GROWTH function after
some more research. Your way looks equally valid. Thanks again.
--
CMS


"Jim Thomlinson" wrote:

You want to use the slope and intercept functions to create the formula...
With the following data in Cells A1:C13

Date Sales
Jan 39.57395298 1
Feb 38.34244752 2
Mar 21.74347515 3
Apr 3.235821974 4
May 72.04120397 5
Jun 61.27490214 6
Jul 71.66341066 7
Aug 22.2055994 8
Sep 27.11134988 9
Oct 95.8971653 10
Nov 27.19312349 11
Dec 84.79849085 12

I used the formulas
=SLOPE(B2:B13, C2:C13) = 3.13448

and
=INTERCEPT(B2:B13, C2:C13) = 26.71596

to derive the formula
y = 3.1345x + 26.716
--
HTH...

Jim Thomlinson


"CMS" wrote:

I am trying to put together a petroleum consumption projection for my
University and need to reference a trendline equation.

I have it set up so every year, we can enter in that years petrol.
consumption and the graph updates, then the trendline updates, but the
projected figures (a list going out to 2020) does not. This is because to get
the projections, I had to manually copy the trendline equation to the cell,
referencing appropriate cells (i.e. the year).

If I could set up an absolute reference to the equation, whenever it was
updated, the projected figures would also be updated but I cannot figure out
how to do this. Sorry for the wordy post, thanks for any help.

--
CMS