I have a problem that I've tried to demonstrate with the attached file. I have a data file (1000s of rows) that produce a line chart similar to these for two of its columns Both columns result from formula that I cannot do away with. Both generate data that are missing values. I need a chart that looks like "C": no line connection through blank cells and no setting blank cells to zero. This is easily done here by going in and clearing the blank cells, but I cannot use that approach in my real data, as I need to retain the formulae.
Chart A uses "NA()" to code the blank spaces and Chart B uses "". Both have their special problems.
Any suggestions as to how I might solve this problem would be appreciated; note, I'm using Excel 2013.
Thanks,
Ric
Hi Ric,
A line chart will only skip empty cells that are actually empty. A cell containing a formula will never be interpreted as empty by a line chart.
You could change your chart to a column chart and this will respect blanks returned by formulas.
Another option is to use VBA to delete the formulas returning #N/A, but then your formulas are gone.
Finally, you could use Power Query to get the data generated by the formulas and replace the errors with 'null' and then use that output for your chart. You can refresh Power Query using the 'Refresh All' button on the Data tab of the ribbon. See example attached.
Mynda
