Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Lasci
 
Posts: n/a
Default Area chart - missing values

Hi all,

I have been creating graphs using the stacked area type to display data for
data by year. However I have some years that have missing data. This then
plots as zero. I have been to tools, options, charts and clicked interpolate
for missing values but this doesn`t seem to work for the stacked area
chart...has anyone else found this? Has anyone found an acceptable work
around?
I`d be interested if anybosy has found anything similar, thanks in advance
for any help.
  #2   Report Post  
bj
 
Posts: n/a
Default

what do you want it to plot?

"Lasci" wrote:

Hi all,

I have been creating graphs using the stacked area type to display data for
data by year. However I have some years that have missing data. This then
plots as zero. I have been to tools, options, charts and clicked interpolate
for missing values but this doesn`t seem to work for the stacked area
chart...has anyone else found this? Has anyone found an acceptable work
around?
I`d be interested if anybosy has found anything similar, thanks in advance
for any help.

  #3   Report Post  
Lasci
 
Posts: n/a
Default

Just numbers for each year, they are for employment figures, but they aren`t
available for each year. I could interpolate myself and add in the values to
the spreadsheet, I just wanted to be able to create a graph quickly without
having to do all this...

"bj" wrote:

what do you want it to plot?

"Lasci" wrote:

Hi all,

I have been creating graphs using the stacked area type to display data for
data by year. However I have some years that have missing data. This then
plots as zero. I have been to tools, options, charts and clicked interpolate
for missing values but this doesn`t seem to work for the stacked area
chart...has anyone else found this? Has anyone found an acceptable work
around?
I`d be interested if anybosy has found anything similar, thanks in advance
for any help.

  #4   Report Post  
Pierre
 
Posts: n/a
Default


Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values


--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=471683

  #5   Report Post  
Jon Peltier
 
Posts: n/a
Default

Unfortunately this trick is ineffective in an area chart. You can only make it work
with real blank cells. If you have some kind of lookup formula, you'll have to
delete the ones that have no returned data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pierre wrote:

Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values





  #6   Report Post  
Lasci
 
Posts: n/a
Default

In the line graphs, I have managed to make excel ignore the missing values,
but only by having another data set that has all the values in the graph, and
then blanking out this line by changing the colour. This is the only work
around I seem to have found so far.....

"Jon Peltier" wrote:

Unfortunately this trick is ineffective in an area chart. You can only make it work
with real blank cells. If you have some kind of lookup formula, you'll have to
delete the ones that have no returned data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pierre wrote:

Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values




  #7   Report Post  
Flávio, SP, Brazil
 
Posts: n/a
Default

Hi, Lasci,

I have the same difficult.
Couldn´t anyone find a solution for Lasci´s problem yet?

Thanks,
Flávio

"Lasci" wrote:

In the line graphs, I have managed to make excel ignore the missing values,
but only by having another data set that has all the values in the graph, and
then blanking out this line by changing the colour. This is the only work
around I seem to have found so far.....

"Jon Peltier" wrote:

Unfortunately this trick is ineffective in an area chart. You can only make it work
with real blank cells. If you have some kind of lookup formula, you'll have to
delete the ones that have no returned data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pierre wrote:

Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values




  #8   Report Post  
Flávio, SP, Brazil
 
Posts: n/a
Default

Hi, Lasci,

I have the same difficulty.
Couldn´t anyone find a solution for Lasci´s problem yet?

Thanks,
Flávio

"Lasci" wrote:

In the line graphs, I have managed to make excel ignore the missing values,
but only by having another data set that has all the values in the graph, and
then blanking out this line by changing the colour. This is the only work
around I seem to have found so far.....

"Jon Peltier" wrote:

Unfortunately this trick is ineffective in an area chart. You can only make it work
with real blank cells. If you have some kind of lookup formula, you'll have to
delete the ones that have no returned data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pierre wrote:

Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values




  #9   Report Post  
Lasci
 
Posts: n/a
Default

Hi,

The only work around I have found is to interpolate the missing values in
the spreadsheet myself (which is what excel does for the line chart) by using
some kind of average. Not ideal but gets around the problem relatively
quickly....

"Flávio, SP, Brazil" wrote:

Hi, Lasci,

I have the same difficulty.
Couldn´t anyone find a solution for Lasci´s problem yet?

Thanks,
Flávio

"Lasci" wrote:

In the line graphs, I have managed to make excel ignore the missing values,
but only by having another data set that has all the values in the graph, and
then blanking out this line by changing the colour. This is the only work
around I seem to have found so far.....

"Jon Peltier" wrote:

Unfortunately this trick is ineffective in an area chart. You can only make it work
with real blank cells. If you have some kind of lookup formula, you'll have to
delete the ones that have no returned data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pierre wrote:

Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values




  #10   Report Post  
Flávio, SP, Brazil
 
Posts: n/a
Default

Hi, Lasci, I´ve just done another workaround that was use na() function
instead and it did pretty well in my particular case. But, thanks a lot
anyway.

Best wishes,
Flávio.

"Lasci" wrote:

Hi,

The only work around I have found is to interpolate the missing values in
the spreadsheet myself (which is what excel does for the line chart) by using
some kind of average. Not ideal but gets around the problem relatively
quickly....

"Flávio, SP, Brazil" wrote:

Hi, Lasci,

I have the same difficulty.
Couldn´t anyone find a solution for Lasci´s problem yet?

Thanks,
Flávio

"Lasci" wrote:

In the line graphs, I have managed to make excel ignore the missing values,
but only by having another data set that has all the values in the graph, and
then blanking out this line by changing the colour. This is the only work
around I seem to have found so far.....

"Jon Peltier" wrote:

Unfortunately this trick is ineffective in an area chart. You can only make it work
with real blank cells. If you have some kind of lookup formula, you'll have to
delete the ones that have no returned data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pierre wrote:

Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values






  #11   Report Post  
Jon Peltier
 
Posts: n/a
Default

NA() is the usual workaround for this is, and it works great for line or
XY charts, but it will not work with an area chart as Lasci was using.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Flávio, SP, Brazil wrote:

Hi, Lasci, I´ve just done another workaround that was use na() function
instead and it did pretty well in my particular case. But, thanks a lot
anyway.

Best wishes,
Flávio.

"Lasci" wrote:


Hi,

The only work around I have found is to interpolate the missing values in
the spreadsheet myself (which is what excel does for the line chart) by using
some kind of average. Not ideal but gets around the problem relatively
quickly....

"Flávio, SP, Brazil" wrote:


Hi, Lasci,

I have the same difficulty.
Couldn´t anyone find a solution for Lasci´s problem yet?

Thanks,
Flávio

"Lasci" wrote:


In the line graphs, I have managed to make excel ignore the missing values,
but only by having another data set that has all the values in the graph, and
then blanking out this line by changing the colour. This is the only work
around I seem to have found so far.....

"Jon Peltier" wrote:


Unfortunately this trick is ineffective in an area chart. You can only make it work
with real blank cells. If you have some kind of lookup formula, you'll have to
delete the ones that have no returned data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Pierre wrote:


Cannot you put #N/A in the cells containing 0 ??
I think it does not read these values




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
Scatter chart without numerical values Fred Geiger Charts and Charting in Excel 3 September 26th 05 08:02 AM
Missing lines in chart w/x-axis with months 1-24... DendWrite Charts and Charting in Excel 2 May 10th 05 09:04 PM
Urgent Chart Assistance Brent E Charts and Charting in Excel 1 May 10th 05 09:09 AM
Urgent Chart Assistance Requested Brent E Excel Discussion (Misc queries) 0 May 9th 05 11:01 PM
Area Chart Formatting Question Avrilon Charts and Charting in Excel 3 December 2nd 04 10:14 PM


All times are GMT +1. The time now is 05:46 AM.

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"