# Plotting discontinuous data in Excel

**URL:** <https://boards.straightdope.com/t/plotting-discontinuous-data-in-excel/333214>\
**Category:** Factual Questions\
**Created:** [November 29, 2005, 4:07pm UTC](https://boards.straightdope.com/t/plotting-discontinuous-data-in-excel/333214 "2005-11-29T16:07:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [November 29, 2005, 4:07pm UTC](https://boards.straightdope.com/t/plotting-discontinuous-data-in-excel/333214/1 "2005-11-29T16:07:32Z")

</div>

I am creating an Excel chart, and for some X values there is not a Y value. The Y values are determined by formula, and if the data is not applicable for that the point the formula returns the null string. For example,

=IF(AND($B8\>=J$1,$B8\<K$1),$C8,"")

If you have a cell with no data in an Excel chart, Excel graphs it as a discontinuity. However, if you have a cell with a formula that returns the null string, Excel plots it as zero. Same thing if you return a blank. So instead of a chart with a line with missing sections, I get a sawtooth line that keeps dropping to zero and bouncing back.

Is there any result that a formula can return that will cause Excel to ignore the cell and treat it as a discontinuity?

---

<div class="post-metadata">

**Author:** ![Fructose\_Modeler](https://avatars.discourse-cdn.com/v4/letter/f/71c47a/32.png) [@Fructose\_Modeler](https://boards.straightdope.com/u/Fructose_Modeler)\
**Post date:** [November 29, 2005, 6:14pm UTC](https://boards.straightdope.com/t/plotting-discontinuous-data-in-excel/333214/2 "2005-11-29T18:14:55Z")

</div>

I saw this exact question in a magazine this week. Excel will plot nothing when you use “” for a vaule giving you a broken line, and if you leave it blank, it will plot 0. If you use the function =NA() then the cell will have the value of #N/A. Excel ‘ignores’ those cells when plotting and will give you a continous line.

So, your function sould look like this:

=IF(AND($B8\>=J$1,$B8\<K$1),$C8,**=NA()**)

Note the bolded section.

Hope this solves your problems.

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [November 29, 2005, 7:02pm UTC](https://boards.straightdope.com/t/plotting-discontinuous-data-in-excel/333214/3 "2005-11-29T19:02:37Z")

</div>

Thank you! 🙂 Isn’t that great when someone asks a question that you just happened to read the answer for recently? This makes the data look kind of ugly, but that’s OK because I just care about the chart.

> [@Fructose Modeler](#):
>
> So, your function sould look like this:
> 
> =IF(AND($B8\>=J$1,$B8\<K$1),$C8,**=NA()**)

I had to make one correction, which was to drop the equals sign. If you just type that into a cell you need the equals sign, but when using it as the result of a formula you don’t.

---

<div class="post-metadata">

**Author:** ![Fructose\_Modeler](https://avatars.discourse-cdn.com/v4/letter/f/71c47a/32.png) [@Fructose\_Modeler](https://boards.straightdope.com/u/Fructose_Modeler)\
**Post date:** [November 29, 2005, 7:48pm UTC](https://boards.straightdope.com/t/plotting-discontinuous-data-in-excel/333214/4 "2005-11-29T19:48:29Z")

</div>

Duh, I knew that. :smack: I was just cut-and-pasting away there. 🙂 Glad I could help.