How to reference trendline equation

  • Thread starter Thread starter CMS
  • Start date Start date
C

CMS

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.
 
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
 
Thanks Jim. I found a way around this through using the GROWTH function after
some more research. Your way looks equally valid. Thanks again.
 
Back
Top