Charting - Only plot real values

[Jarke]

Hello,

I have created a dynamic name range, with formulas in the rows. When I chart this area it will display values as I add more down the rows. My problem is tho that I don't want it to plot if the formula don't show my wanted value.

My cell goes blank if another cell is blank, but it still plots in the chart as it's a formula in the cell. I have tried with #N/A or #Error or others but with no success.

In the image below, you can see that the chart continues to plot even if there is a zero or #N/A, I would like it to stop after the last "5". But how?
https://ibb.co/kTwGh5

As there needs to be a formula in the cell, my thought is to make it #N/A or something else, to make the chart exclude it from the data. Best is if it stop at F23.

-If i had formulas 1000 rows down, my data would be cornered and been very small as the x-axis would be so large.

How do I restrict the chart only to plot real values?

 

[Jarke]

Perhaps a "-" or IF function in the dynamic name range formula, that makes it only count my real values, and ignores empty or #N/A cells?

 

[Jon Peltier]

First, you probably really want an XY Scatter chart, not a Line chart. You can format the scatter chart to draw lines and no markers, but you can't fix the goofy way the line chart treats X values.

Second, you can probably make your formulas in column F determine when to stop putting values (extraneous 5's or following zeros) in the column.

 

[Jarke]

Thanks Jon for your answer. After alot of testing and trying I managed to fix it, I just used COUNT instead of COUNTA. Haha.. Sometimes it's easier than you'd think..