Calc handling of blanks and null strings

[DavidL1]

Ths question follows on that of F.Bachofner in December 2018 - see link text - and I have exactly the same issue.

I have a spreadsheet containing a graph with five data-series and a variable number of blanks at the end of each column of data. Some formulae create blank cells using formulae like IF(–,"",–) which I think creates zero-length strings. This spreadsheet is updated almost every day, however updating each data-series with the last non-blank cell in a column is tedious and error prone.

Configuring each data-series to the last cell in a column ( e.g. Sheet1.$A$2:$A$123) means the series are automatically updated as rows are added, but will the graphing software treat the blanks as not-data? This does seem to work, but I’d be grateful for clarification.

David L.

[keme]

There are several choices for chart parameters (chart type, range settings, data handling type of things) which may have a bearing on this. The source data (data types, layout) may also matter.

You have determined that your graphing setup seems to work for you. If you want us to tell us why it works, and whether (or how/why) it is reliable, you must share the file with us. We can guess, but in my view there are too many variables and not enough info to make a better assessment than what you have already made.

Create a copy of your file. Remove or mangle any sensitive content (confidential info, intellectual property, etc.) and attach the copy to your question above. Use the paperclip tool.

[erAck]

Strings, including empty strings, are ignored for chart data points in an otherwise numeric sequence. You can verify easily yourself by creating a short sample data column and include a string between or at the end, or a formula =IF(1;"";2) for an empty string. You can also specify in the options of a data series whether a missing value should leave a gap, or 0 is assumed, or the line be continued in Line or XY(Scatter) charts. For that in the activated (double clicked) chart choose the data series, either by right clicking one of its values and then context menu Format Data Series…, tab Options, or from the listbox in the toolbar and then menu Format → Format Selection…, tab Options.

[DavidL1]

Ahh, thank you Eike, that’s exactly what I wanted to know.
David