Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Nat
 
Posts: n/a
Default 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   Report Post  
bj
 
Posts: n/a
Default

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.

  #3   Report Post  
Tushar Mehta
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Table - Graph Links Breaking!!! Geise, Brad Charts and Charting in Excel 1 March 31st 05 12:34 AM
Help making a chart that doesn't graph cells without data? Filtration Guy Excel Discussion (Misc queries) 0 March 22nd 05 10:21 PM
Formatting lines between data points in a line chart ltanner Charts and Charting in Excel 2 March 13th 05 06:12 AM
Help with data not getting plotted Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 06:17 AM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 02:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"