How do you prevent a NULL cell from appearing in a Data Series in a Line Graph?

I created a basic line graph in Excel with 3 data series, across a period of time.  The problem is that data isn't available for ONE of the time periods for ONE of the data series (the cell is NULL) ... causing my line in the graph to dip straight down to the X-axis.  I do not want this dip to show.

Is there a way to have the chart wizard IGNORE that NULL cell and remove that errant line from the data series?

Attached is a screenshot to help you understand better...

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm not sure if this will work for the type of graph you are doing, but...

If you select the chart, and then in the menus go to Tools, Options, and Click on the Chart tab,
there is a section:  Plot empty cells as: "Not plotted (leave gaps)"

See if that fixes your problem.
tuan1969Author Commented:
I tried your suggestion and it does not work for my line graph.
This is very strange since the help file definition for "Not plotted (leave gaps)" is:
"Leaves gaps in the line for empty worksheet cells in a data series, making the line segmented."

I wonder why this doesn't work for me?
tuan1969Author Commented:
By the way, the cell with the NULL data contains a FORMULA that produces "" (two double quotes) if the criteria is not met.  I am assuming that "" is recognized as NULL by Excel.

Could this affect my problem?
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

tuan1969Author Commented:
If I delete the formula, leaving an empty cell, the line graph is now okay (it doesn't plot the empty data).

However, is there a way to LEAVE the formula in that cell (a formula that produces "") and have the graph not plot it?

Thanks to all !!!
Jeroen RosinkSoftware testing consultantCommented:
Sorry to say, It is not possible to suppress NULL values which are returned from cells.

a workaround could be taking the value of the previous cell add this to the next cell and divide it to 2
If C4 contains a number which returns NULL or empty value then taken the C3 value and C5 value and simarize them.


I hope this works for you
Rory ArchibaldCommented:
If your formula returns NA() instead of "", then the chart should ignore it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.