I've been working with Doug Robbins' excellent Mail Merge with Charts and although thanks to Doug's help, I have it working, Unfortunately, I have a major problem with the Excel end of things.

I have fairly simple data in the following form

Date Apr 20 Apr 21 Apr 22
Grade #N/A #N/A 5.0

for a large number of days. If a report for a particular day is not submitted, #N/A is returned.

Unfortunately 'Mail Merge with Charts' will not work with #N/A in the data.

If I use formula returning blank cells instead, the chart plots the values as 0. As I also need to plot a trendline, I cannot go down the route of changing the y-axis so that the zero values are not seen.

So before, I abandon all hope, is N/A() the only option when looking for Excel to ignore cell values in an xy chart?

Thanks