A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trendline when data points are missing



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 06:16 PM posted to microsoft.public.excel.charting
Chris Moran
external usenet poster
 
Posts: 1
Default Trendline when data points are missing

I have data where for some of the series there is data in each "category" and
in other series there is some missing data. Here's the example:

Fund Type Fund Company One Year Return
Target 2050 Principle 12.2
Target 2050 TRP 11.3
Target 2045 Principle 11.1
Target 2040 Principle 10.8
Target 2040 TRP 9.4

When I chart this I get a line for all of the "Principle" data because it
has a data point for all three "Fund Types". I only get data points and no
line for "TRP" because it has no data point for the "Target 2045" "Fund
Type".

I would like to add a trend line that simply "connects the dots", but none
of the available trendlines will do that. I also can't put a formula in the
cells because the source data is a pivot table. Any solutions for this?
  #2  
Old May 12th, 2010, 02:58 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default Trendline when data points are missing

What is your category axis (X values)? If it's not numerical, you do not
want a trendline.

Why can't you use a line with markers style to connect the points?

Your data should be in this arrangement to make a proper chart:

| Principle | TRP
Target 2050 | 12.2 | 11.3
Target 2045 | 11.1 |
Target 2040 | 10.8 | 9.4


- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 5/11/2010 1:16 PM, Chris Moran wrote:
I have data where for some of the series there is data in each "category" and
in other series there is some missing data. Here's the example:

Fund Type Fund Company One Year Return
Target 2050 Principle 12.2
Target 2050 TRP 11.3
Target 2045 Principle 11.1
Target 2040 Principle 10.8
Target 2040 TRP 9.4

When I chart this I get a line for all of the "Principle" data because it
has a data point for all three "Fund Types". I only get data points and no
line for "TRP" because it has no data point for the "Target 2045" "Fund
Type".

I would like to add a trend line that simply "connects the dots", but none
of the available trendlines will do that. I also can't put a formula in the
cells because the source data is a pivot table. Any solutions for this?

  #3  
Old May 12th, 2010, 03:18 PM posted to microsoft.public.excel.charting
trip_to_tokyo[_3_]
external usenet poster
 
Posts: 932
Default Trendline when data points are missing

EXCEL 2007

You don't say what type of chart you have constructed nor do you give the
format of your underlying data.

However, if I break down your data as follows:-

Cells E1 to E4
Fund Type
Target 2040
Target 2045
Target 2050

Cells F1 to F4
Fund Company
TRP
TRP
TRP

Cells G1 to G4
Rate
9.40
blank
11.30

Highlight the above cells / Insert / Charts / Line with Markers / OK

I see the start and end point on the Chart.

Right click one of the data points / Add Trendline . . . / Format Trendline
window pops up and defaults to Linear / hit Close / the 2 points are now
joined in the way that you want.

If my comments have helped please hit Yes.




"Chris Moran" wrote:

I have data where for some of the series there is data in each "category" and
in other series there is some missing data. Here's the example:

Fund Type Fund Company One Year Return
Target 2050 Principle 12.2
Target 2050 TRP 11.3
Target 2045 Principle 11.1
Target 2040 Principle 10.8
Target 2040 TRP 9.4

When I chart this I get a line for all of the "Principle" data because it
has a data point for all three "Fund Types". I only get data points and no
line for "TRP" because it has no data point for the "Target 2045" "Fund
Type".

I would like to add a trend line that simply "connects the dots", but none
of the available trendlines will do that. I also can't put a formula in the
cells because the source data is a pivot table. Any solutions for this?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.