I have a data table to plot figures that are generated from other data using formulas. For some points there is no data so it shows blank. As it is not a true blank cell the chart is dropping to zero. I have tried the ignore blanks option in the chart settings - showing it as NA, NA() and #NA but it still plots. Can anyone tell me how to stop this happening?
If the chart is column or bar then the category space will still be present. You would either need to hide the row on the worksheet or create another contiguous data set for the chart excluding unwanted rows.
If the chart is a line chart then =NA() will cause the data marker to be omitted. If the cells with =NA() are at the front or back of the data series then the line will be truncated. If in the middle the line will not be broken but instead interpolated between valid data points. If the line drops to zero then it's possible you have the stacked line subtype.
It depends somewhat on chart type you are using.
But NA() is appropriate for scatterplots (XY plot) (Sounds like you are using this chart type) - formulas like:
Formula:
Please Login or Register to view this content.
should do the job for you.
Best Regards,
Kaper
#n/a
should work
as should na()
can you post a sample sheet with the issue please
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Using #N/A did work, the issue was I wrote it in the formula as "#N/A". Thanks for the help
your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks