Plotting when some cells in the data range get "" from a formula

Hello all,

I'm trying to plot a graph where some of the valeus in the data range are "", as a result of a formula. This drives excel crazy. Have a look:
excel-question.PNG

as suggested in other threads (like http://www.excelforum.com/excel-gene...hart-data.html )
using NA() instead of "" solves the problem, but then the problem is the table which holds the data is itself horrendous.

I tried playing around with the Select Data >> Hidden and Empty Cells feature, but got nothing.

Is there a way to get a nice looking graph and a table with empty cells (without manually deleting the "" values in the data table) ?
The empty cells should not be regarded as 0s.. I want to see gaps.

Here is the desired outcome:

excel-desired.PNG

Thanks in advance

ANS

Hi ANS,

There is a really simple but not well known solution to your problem.

This does not require the use of NA() and fits with your data. Make your table area a real table in excel Insert Ribbon and then chart the entire table. Here is a demo with using Tables to create a dynamic chart.

How-to Make Dynamic Excel Dashboard Charts Using Tables

Here is an alternate demo using tables or the offset function. But based on your data, I don't think need to use the offset function:

Case Study - Creating a Dynamic Chart in Excel Using Offset Formula

Please let me know if this helps.

Steve=True

Hi Steve,
Thanks for the reply and I'm happy to have found out about your site and YouTube channel. Will definitely come in useful.

Actually, it didn't solve the problem. I think maybe it is because my table has another dimension than the one you used. Could that be the reason?
What else can be done ?

Thanks
ANS

Originally Posted by Steve=True

Hi ANS,

There is a really simple but not well known solution to your problem.

This does not require the use of NA() and fits with your data. Make your table area a real table in excel Insert Ribbon and then chart the entire table. Here is a demo with using Tables to create a dynamic chart.

How-to Make Dynamic Excel Dashboard Charts Using Tables

Here is an alternate demo using tables or the offset function. But based on your data, I don't think need to use the offset function:

Case Study - Creating a Dynamic Chart in Excel Using Offset Formula

Please let me know if this helps.

Steve=True

Any ideas?