How can I ignore zeros on a chart with multiple lines?

[ks100]

I have a chart with dates on one axis and tasks due on another axis.

Each line in the chart represents a department.

The data for the chart looks like this:

chart example.jpg


As you can see some dates have a "zero" value for a department but a non zero value for another department.

The problem I have is each individual line on the chart looks like this, because they have zero values in between their non zero values:

bad.PNG

As you can see, the line plummets to zero when the date has a zero value for that department. I want the lines to IGNORE the zero values, and smoothly transition between non zero points, which would make it look like this:

ideal.PNG


I can accomplish this with a chart that only has one department on it by simply removing the dates that have a "zero" value, however this is not an option on a multi department chart because SOME dates have a value for one department and a zero for another.

How can I make this chart to where dates are ignored ON AN INDIVIDUAL line basis if they have a zero value, while still keeping that date on the chart for the other lines that have a value there?

Basically I don't want to ever see a line dip down to zero on the chart, zero values should be treated as if no point exists there, with the line sloping between it's last two non-zero points.

[zbor]

Use NA() instead of 0 where you want blank

[ks100]

Use NA() instead of 0 where you want blank

I tried that, didn't seem to work. Even tried DIV/0

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ks100]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

SampleChartBook - Copy.xlsx

File is attached

[zbor]

I've put NA() and get result.
What is your expected result?

[ks100]

I've put NA() and get result.
What is your expected result?

There is only one line on that chart, I need all four departments to be on the same chart while having non zero values.

[ks100]

I've put NA() and get result.
What is your expected result?

Nevermind, I got it working. Thank you! (I forgot to add #NA to the other columns)

[zbor]

Yea, I know.
I just show you how to do it.
Adopt for other as needed.