Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scatter chart without numerical values | Charts and Charting in Excel | |||
Missing lines in chart w/x-axis with months 1-24... | Charts and Charting in Excel | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) | |||
Area Chart Formatting Question | Charts and Charting in Excel |