Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display data points that are less than the chart trendline | Charts and Charting in Excel | |||
Missing Lines Between Data Points | Excel Discussion (Misc queries) | |||
How do I chart missing data points in a scatter graph? | Charts and Charting in Excel | |||
Trendline excluding last 2 data points of series | Charts and Charting in Excel | |||
interpolate missing data between points | Excel Worksheet Functions |