Thread: 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.

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.
>

As part of your original data manipulation, you did one of two things.
Either created results of an empty string ("") or zeros that are made to
look like blanks through the format applied to the cell. What you need
to do is delete the contents of all those cells.

If you are using a sufficiently modern version of XL (2002 or later I
believe), use the Find capability to search for "" and click the 'Find
all' button. Then, select all the cells that are shown at the bottom of
the Find dialog box (select the first entry in the dialog box then
shift+click the last one). Close the dialog box and select Edit | Clear
> Contents. The chart should now be OK.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <E5CC1CF1-13B2-46D5-BC8D-3BA4EE6F376F@microsoft.com>,
Nat@discussions.microsoft.com says...
> 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.
>
>