Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Line Graph Data Recognition
I have an array (about 15 x 15) with years 1990-2003 along the top that is
missing data in some years, i.e. one series begins at 1990 and goes to 2000 while other series begin in 1998 and goes to 2003. The array content is in number format to the first decimal place, e.g. 14.1. These values were originally created by a formula and were in ratio format, e.g. 0.141, but I converted them by multiplying them by 100 and then removed the fomula by the "copying and paste value" command. When line graphing the array using the ratio values, the image turns out as anticipated, i.e. the graph line associated with a particular data series stops when the spreadsheet data stops and similarly starts when the spreadsheet includes data. However, when using the array containing the converted values, e.g. 14.1, the line graph always begins and finishes a partial data series, e.g. one going from 1990-2000 or from 1997-2003, by going to zero. For example, if a data series goes from 1990-2000 in the spreadsheet, the graph will plot a line that goes from the value in 2000 to zero in 2001, even though the cell for 2001 for that data series is empty. I have created a "test" array in the same spreadsheet and entered some test values that approximate those in the original array and am able to graph them without any problem, i.e. the graph wizard doesn't automatically begin and end data series with zero values. I have tried every trick I can think of to get around this problem with no success. I have copied the original array into different spreadsheets but the problem seems to follow this data array. I have tried retyping the data in the "offending" cells of the original array - no luck. However, I have copied the array to a new location and "gutted" the data included in the array (keeping only the column and row headers) and repopulated the array data with "test" data that appears to be in the same format as the deleted data and have been able to graph successfully. Thus, the problem appears to reside with the data in the original array - but I can't figure out how to resolve the issue. All suugestions would be most welcome. Thank you in advance. |
#2
|
|||
|
|||
are the cells truely empty or are they "" as the result of an equation. try
going through selecting the "empty" cells and deleting as there there were something there. "Nat" wrote: I have an array (about 15 x 15) with years 1990-2003 along the top that is missing data in some years, i.e. one series begins at 1990 and goes to 2000 while other series begin in 1998 and goes to 2003. The array content is in number format to the first decimal place, e.g. 14.1. These values were originally created by a formula and were in ratio format, e.g. 0.141, but I converted them by multiplying them by 100 and then removed the fomula by the "copying and paste value" command. When line graphing the array using the ratio values, the image turns out as anticipated, i.e. the graph line associated with a particular data series stops when the spreadsheet data stops and similarly starts when the spreadsheet includes data. However, when using the array containing the converted values, e.g. 14.1, the line graph always begins and finishes a partial data series, e.g. one going from 1990-2000 or from 1997-2003, by going to zero. For example, if a data series goes from 1990-2000 in the spreadsheet, the graph will plot a line that goes from the value in 2000 to zero in 2001, even though the cell for 2001 for that data series is empty. I have created a "test" array in the same spreadsheet and entered some test values that approximate those in the original array and am able to graph them without any problem, i.e. the graph wizard doesn't automatically begin and end data series with zero values. I have tried every trick I can think of to get around this problem with no success. I have copied the original array into different spreadsheets but the problem seems to follow this data array. I have tried retyping the data in the "offending" cells of the original array - no luck. However, I have copied the array to a new location and "gutted" the data included in the array (keeping only the column and row headers) and repopulated the array data with "test" data that appears to be in the same format as the deleted data and have been able to graph successfully. Thus, the problem appears to reside with the data in the original array - but I can't figure out how to resolve the issue. All suugestions would be most welcome. Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Table - Graph Links Breaking!!! | Charts and Charting in Excel | |||
Help making a chart that doesn't graph cells without data? | Excel Discussion (Misc queries) | |||
Formatting lines between data points in a line chart | Charts and Charting in Excel | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |