why is my dynamic chart plotting blank points as zero?

New Contributor



I am working on a template for charting data. I want to be able to use a dynamic named ranges for series data so the chart is dynamic and I don't need to manually update the series data everytime. Sample number is x axis and Average Weights is the y axis. I made the dynamic range names based on the batch number. It is dynamic and if i change the batch number in one of the columns the appropriate datapoints change on the dynamic chart but it is plotting all empty data even though I have hidden/empty cells set to gaps. 


Is it something wrong with my named ranges or with the chart data? (included the name manager for reference)


the end goal is to then have a template so the data entry can be done by someone without excel knowledge but they will still get the chart that they need. 


Thank you in advance for help!





2 Replies
best response confirmed by emmalc2950 (New Contributor)

@emmalc2950 Change the double quotes at the end of the named ranges to NA(). That should fix it. A double quote is not considered an empty cell when plotting a line, whereas NA() will generate an #N/A error which will be ignored.

Screenshot 2023-05-14 at 05.48.43.png

@Riny_van_Eekelen that fixed it! thank you so much! I knew I was close but stared at it too long, that was so much easier than all the complex offset equations I was finding online!