Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

why is my dynamic chart plotting blank points as zero?

Copper Contributor

Hello, 

 

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!

 

 

emmalc2950_1-1683996303843.png

 

2 Replies
best response confirmed by emmalc2950 (Copper Contributor)
Solution

@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!

1 best response

Accepted Solutions
best response confirmed by emmalc2950 (Copper Contributor)
Solution

@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

View solution in original post