How to NOT graph empty cells [closed - with note to Calc developers in answer]

[FBachofner]

I am making something like a “dashboard” in Calc.

Each day a new value gets added to the next row.

for example:

Monday: A1

Tuesday: A2

Wednesday: A3


and so on, for a whole month

These values get plotted on a line graph (which incorporates the entire month’s range so the graph does not have to be reset each day!).

The (default) way of graphing this shows the empty cell values “plummeting” to the x axis (as the empty values are interpreted as zero). This implies a trend which is completely inaccurate (including an inaccurate trend-line, if enabled)!

Is there a good way of making the graph not display the empty cells?

In “Data Series,” “Plot Options,” “Plot Missing Values” – leave gap is selected, but this does not work either in the middle of a series of cells or in the “tail.”

This is on LO 5.4.4.2. Machine can not be upgraded to a more recent version until next year.

Thanks in advance for any insights.

[FBachofner]

Interesting:
If I delete the formula in the “blank” cells, the graph properly omits displaying the missing values as “zero.”

Of course, I would like to leave the formula intact for the entire relevant range – so the data-entry operator does not have to “paste-down” the formula to the next cell each day!

[FBachofner]

OK . . .

My question was oversimplifying the actual usage. The range for the graph is not just a numeric entry. It is a formula based on an entry in an adjacent column.

One way of handling “blank” values in the resulting range (the one to be graphed) is to use an IF statement for the formula to filter out the blank values.

For example, IF(A1 = “”, “”, (non-blank formula for the graphed column goes here))

[ i.e.: if A1 is blank, display blank, else use the entered formula entered here for this cell)

That said, I think LO Calc graphing should be “smart” enough to enforce “blank” graphing rules even when the results come from a formula!

Additional wisdom is welcomed!

[erAck]

A "" as formula result is not blank, it is an empty string. I didn’t get exactly what you’re actually up to, but maybe in Chart context it helps to have the NA() error value instead, which also suppresses a data point, so IF(A1="",NA(),formula). Btw, to test for a blank cell, using ISBLANK() is more appropriate than to test against an empty string or zero, so IF(ISBLANK(A1),NA(),formula)

[FBachofner]

@erAck

I’s funny you point this out; in my own implementation I actually did use the IF(ISBLANK(A1) construct!

I offered the alternative above as I thought it might be easier for the average Calc user to understand.

While null (i.e. blank) and an empty string are indeed not computationally the same, for a great many use cases “” is effectively the same as BLANK.

Regardless of which method is used, I still thing LO Calc graphing should enforce “blank” graphing rules regardless of how the blank arrives. This should certainly be true when a formula is lacking inputs!

Thanks for your response.