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?

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?

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?