How to represent empty cells on line chart?

Hi Sophie,

Here are two examples. Both start with the same table, shown here with gaps in the data (and the resulting xy scatter plot with connecting lines):

Method 1:

Delete the lines containing no data in column C (or enter only the dates in column B for which there is data to be entered in column C):

Method 2:

Incude a line for each date, enter amount data only in lines for dates when there is data to enter.

Filter the table on column C to show only the rows containing data (Note line numbers in the tabs to the left of the table).

Note that the dates in column B are:

• Date and Time values (formatted to display only the month and day)
• in a body column, where they will be read as data, not a header column, where they would be read as categories (text).

There is a checkbox in the Chart Format Inspector labelled "Hidden Data". It is unchecked by default, and should remain so when using Method 2.

Regards,

Barry

Barry wrote:

Hi Sophie,

Here are two examples. Both start with the same table, shown here with gaps in the data (and the resulting xy scatter plot with connecting lines):

Method 1:

Delete the lines containing no data in column C (or enter only the dates in column B for which there is data to be entered in column C):

Method 2:

Incude a line for each date, enter amount data only in lines for dates when there is data to enter.

Filter the table on column C to show only the rows containing data (Note line numbers in the tabs to the left of the table).

Note that the dates in column B are:

• Date and Time values (formatted to display only the month and day)
• in a body column, where they will be read as data, not a header column, where they would be read as categories (text).

There is a checkbox in the Chart Format Inspector labelled "Hidden Data". It is unchecked by default, and should remain so when using Method 2.

Regards,

Barry

Thanks, Barry. Grateful to you for the time and energy you put into that reply.

My primary concern was that if I deleted days, the gaps in time would not be reflected. You've shown that it's possible to accurately reflect the gaps, so long as I ensure that my data is formatted as date and time values.

And it looks like it's possible to do this and have the x-axis display all of the dates, or at least regular intervals, not just the dates with data. That had been my secondary concern.

I'm working with data from a four-month program where data was collected once or twice a week on a sporadic, irregular basis.

I will try out your second method and hide the dates without data.

One follow-up question. It would be useful to have reference lines that rise from the x-axis-- an automatic reference line to denote between months, and also a custom line to denote key turning points in the program (my hire date, for example). Do you know if this is a currently available feature? I saw reference lines offered on the y-axis, but I don't need them there.

Thanks again.

One follow-up question. It would be useful to have reference lines that rise from the x-axis-- an automatic reference line to denote between months, and also a custom line to denote key turning points in the program (my hire date, for example). Do you know if this is a currently available feature? I saw reference lines offered on the y-axis, but I don't need them there.

Two sets of grid lines are offered—

major divisions: These mark each of the label values marking a step on the x axis. The number of steps can be set in the x axis part of the Axis section of the Chart format inspector. In the example, I've set the number of steps to 7, and the Major Gridlines to a solid line.

minor divisions: These are placed at the midpoint between the steps on the axis. I've chosen a dotted line for these.

Reference Line(s) can be placed at specific values on either axis. I've placed one at January 6 on the x axis,using the default style chosen by Numbers. and choosing to show the value, but not the name of this line (above the table).

Colour and thickness of the reference line can be adjusted y selecting the line, then choosing a colour and setting a weight using controls in the Inspector panel.

Axis choice is via two buttons near the top of the Inspector panel. I've copressed the height of the window, and scrolled down to make the image fit better here.

Regards,

Barry

Microsoft Excel has exactly this option. Selecting data source for a range, there's a "Show empty cells as:" with a choice of "Connect data points with line". This works perfectly. In this case, I'm forced to switch to Excel due to this missing feature in Apple Numbers.

brsw wrote:

In this case, I'm forced to switch to Excel due to this missing feature in Apple Numbers.

Excel is a great app. But Numbers easily handles this situation. See the 30-Day History sheet in the 'My Stocks' template at File > New in your menu. There is no data for weekends and other non-trading days yet the chart works well. All the template does is filter out the non-trading days. They are then ignored in the chart.

BTW, this is a REALLY old thread. Numbers has changed a lot since then.

SG

You say that Numbers has changed a lot, but I cannot find any solution to this issue from 2011.

Didn't see any template called My Stocks, but not sure how it would have demonstrated a solution as stocks only move when the stock market is open, so data gaps on weekends and holidays would not be relevant.

In this circumstance, I would like data points to connect across gaps in time where data was not collected, but would be expected to have changed at more or less the same rate / slope of the connecting line.

I will reluctantly be moving to Excel.

"You say that Numbers has changed a lot, but I cannot find any solution to this issue from 2011.

Didn't see any template called My Stocks…"

The My Stocks template exists only in version 4.x of Numbers, the version in which the STOCKS function was introduced.

"I will reluctantly be moving to Excel."

Use the tool that is best for the job. There's no need to be reluctant about doing so.

Regards,

Barry

"You say that Numbers has changed a lot, but I cannot find any solution to this issue from 2011.

Didn't see any template called My Stocks…"

The My Stocks template exists only in version 4.x of Numbers, the version in which the STOCKS function was introduced.

"I will reluctantly be moving to Excel."

Use the tool that is best for the job. There's no need to be reluctant about doing so.

Regards,

Barry

"You say that Numbers has changed a lot, but I cannot find any solution to this issue from 2011.

Didn't see any template called My Stocks…"

The My Stocks template exists only in version 4.x of Numbers, the version in which the STOCKS function was introduced.

"I will reluctantly be moving to Excel."

Use the tool that is best for the job. There's no need to be reluctant about doing so.

Regards,

Barry

sophie.. wrote:

In this circumstance, I would like data points to connect across gaps in time where data was not collected, but would be expected to have changed at more or less the same rate / slope of the connecting line.

I will reluctantly be moving to Excel.

Easily handled in both Numbers and Excel but use whatever you're most comfortable with.

SG

SGIII wrote:

sophie.. wrote:

In this circumstance, I would like data points to connect across gaps in time where data was not collected, but would be expected to have changed at more or less the same rate / slope of the connecting line.

I will reluctantly be moving to Excel.

Easily handled in both Numbers and Excel but use whatever you're most comfortable with.

SG

SG,

Please explain how this is easily handled in Numbers. As I said in my original post, I have found no solution.

I would prefer to continue using Numbers rather than export my data to Excel.

Thank you.

"I will reluctantly be moving to Excel."

Use the tool that is best for the job. There's no need to be reluctant about doing so.

Thanks, Barry.

I am sure that you can appreciate the frustration that occurs when one is forced to switch programs because the first program used cannot do the job.

It's not unreasonable to expect that Numbers would be able to accommodate a gap in data on a timeline.

It's quite a disappointment to discover that Numbers cannot do so.

Friends ask me, why even try using macOS / OSX programs, when MS is demonstrably superior?

I trusted Apple to produce software sufficient for my simple task, but it seems that with macOS you lose even the most basic functionality in favour of better aesthetics. As I cannot afford to lose that functionality, I will reluctantly be moving to Microsoft, as its programs are built to perform, not to look pretty.

If the export to xlsx was perfect this would be less of an issue. But I am preparing data for presentation purposes and the formatting does not translate well.

I lost time by first formatting the data and chart in Numbers and then having to reformat in Excel.

Lesson learned. Hold off on all formatting until you can be certain that the charting program can make the chart.

Hi sophie,

It's not unreasonable to expect that not every piece of software will necessarily work the exactly the way you want it for every project that you have.🙂

I think Barry has given some excellent illustrations here of how to deal with visualization of data gaps in Numbers. But of course use the tool that suits you best for your particular project. I use Numbers for some, Excel for others.

And instead of describing apparent frustration to your fellow users here, you could perhaps consider communicating with Apple via Numbers > Provide Numbers Feedback in your menu.

SG

Numbers will graph the data you supply it. The break in the line is an accurate representation of the break in the data.

There are three ways to close the line.

1. Delete the rows representing the missed days from your table. A Line Chart is a category chart, so the missing dates (jan 2 and 7) will simply be removed from the chart. The surrounding categories will close in to fill the space.

2. Calculate a data value for the missed day. The disadvantage here is that the calculated datum will display a data point on the chart that looks the same as a measured datum, which is a misrepresentation of the actual data.

Formula (Placed in C2, and filled down):

=IF(AND(LEN(B1)>0,LEN(B2)=0,LEN(B3)>0),AVERAGE(B1,B3),IF(LEN(B2)=0,"",B2))

B is the data column. C is the column used to supply data to the chart.

The formula looks at three cells in column B, the one in its own row (B2) and the two in the row above and below its own row. If both cells in adjacent rows have data AND the cell in its own row is empty, the formula calculates an interpolated value. If either adjacent row cell is empty, the formula returns a null string if its own row is empty, or the data from column B if there is data entered there.

3. Use an X-Y Scatter Chart. Unlike Category charts, where the dates are treated as labels, a scatter chart plots two data series—in this case the date of the reading (x) and the actual reading (y) against each other. A missing reading will still cause a break in the line connecting the data if its partner date is present. If neither part of the data pair is recorded (ie. if the row is removed), the line will be connected, and the x position of the data that is present will remain unchanged. (Same data as above, with Jan 2 and 7 missing. Note the absence of data points on the line for these dates—where the rising line crosses 125.)

To use a scatter chart, the dates must be recorded as actual Date and Time values, and must be in a 'regular' column, not a Header column (where they would be seen by the chart as Text).

Option 3 gives, IMO, the best representation of the data.

Regards,

Barry