Chart Range but Ignore Ending Data

[pendulum]

My chart only looks correct if I manually set the range of data each time I add a new value.
I would like to select (almost) the entire column of data without the chart plotting zeros for everything non-numerical in the range.

The forumula for the data is: =IF(K23=0, "", K23-K22-1), so its either a number or a "" nothing.

How do I select the entire range but have it auto-update the chart while showing only the valid range?

 

[sheetspread]

dynamic named range

 

[pendulum]

Beuller?

 

[sheetspread]

Try:

https://vimeo.com/184687460

you can also set your data as a table

 

[Worf]

Ribbon>Chart Tools>Design>Data>Select Data>Hidden and empty cells>Gaps.

Missing data will be ignored.

Alternatively, hide the rows with empty cells and they will not be charted. There is an option to force the use of hidden data, but it is not the default behaviour.