![]() |
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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 | |
|
|