Displaying trendline numbers

jcg31

Board Regular
Joined
Aug 20, 2006
Messages
176
I have an excel chart that plots historical performance data monthly- - each month displaying the year to date position. Each year is a separate series whose data is plotted on the chart with each month's values displayed in a table below the x-axis (as provided for with excel charting). The future months of the current year are projected using a trendline. Is there a way to display the monthly values of the trendline in either the table or in the charted area?

Thanks for any help.

Jim
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
if you are displaying a trend line you have data points so turn on show data values

highlight chart then chart, chart options, data labels
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
To show in a table,

H​
I​
J​
K​
1​
Month
Actual
Forecast
2​
1​
9,671​
3​
2​
9,213​
4​
3​
9,616​
5​
4​
9,942​
6​
5​
10,329​
7​
6​
9,250​
8​
7​
9,631​
9​
8​
10,349​
10​
9​
10,078​
J10:J13: {=TREND(I2:I9, H2:H9, H10:H13)}
11​
10​
10,151​
12​
11​
10,224​
13​
12​
10,297​
 

jcg31

Board Regular
Joined
Aug 20, 2006
Messages
176
Thanks for the quick replies.

Oldbrewer:
I am using 2010, in which, apparently that functionality isn't available on Trendlines.


shg,
My data is displayed horizontally with the months running from column E- P, Years running rows D2:D5 with the most recent three months of data in E5:G3 (we are three months into our fiscal year). I am having difficulty getting the trend formula to work horizontally. Can you provide an example with the data structured as above?
Thanks,
Jim
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

i make my own trend lines using the y mx and c values so my line is just an ordinary line
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
1​
Month​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
2​
Actual​
9,671​
9,213​
9,616​
9,942​
10,329​
9,250​
9,631​
10,349​
3​
Forecast​
10,078​
10,151​
10,224​
10,297​
J3:M3: {=TREND(B2:I2, B1:I1, J1:M1)}
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.

BTW, you know that on a chart you can extend the trendline left and/or right?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top