Numbers Charts With Non-Consecutive Data

Sometimes you have non-consecutive data in Numbers that you wish to chart. For instance, you may have skipped dates or large gaps in time. However, you want to graph the data with a consistent timeline at the bottom of the graph even if all of the dates are not represented. Here is one method for doing that, using a second hidden table that is automatically populated from the main table.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. On this episode let me show you how you can chart nonconsecutive data in Numbers.
So say you have some data in Numbers that has a set of dates but they're not consecutive. You can see I've got January 6, 7, 8 and then skip to 15th, then the 18th, and then the 30th. I was asked this question recently by a viewer of how do you create a chart for this where the chart doesn't just treat everything equally. Let me show you.
If I select this data here I'm going to select All. I'm going to hit Chart and say I want a line chart. You can see each point is treated equally. The 6th, 7th, 8th, 15th, 18th, 30th. It doesn't spread this out over a month like it should. These three should be grouped closely together, these two somewhat closely together, and there should be big gaps here. How do you get it so that this spreads over the month evenly.
It turns out a lot of people have this question and not many solutions or any solutions really how to do this in Numbers. 
But you can do it in Numbers. It is not easy but you can do it.
The way to do it is to create another sheet that actually has all the dates in it. So, for instance, let's go to sheet 2. You hit the plus button to create sheet 2. Create a simple table here. I'm going to put Date and Weight. This makes sense because this is kind of a weight loss thing and you might not get a chance to weigh yourself everyday.
So I want to start off with the 1st of January and then I'm going to have the second one here be a formula where its the previous day plus one. That's the 2nd of January and I can paste it here, paste it there, and keep pasting it. As a matter of fact I'm going to shrink this table this way and I'm going to grow it to be an entire month. So 32 days let's get that right around there. 32 because the first one is the header there. I'm actually going to paste for all of these, paste it in. So now I've got 1/1 to 1/31.
Now I could go and fill in the weights right here. I could go back here and say 1/6 is 180. I could go fill it in and leave the ones I don't know blank. But I'd like to have that done automatically. What if this was a year's worth of dates or two years worth. It would be easy enough to fill these in there just as I did before by copy and paste. But how do you fill in all the bits of data.
So as with most things in Numbers it's going to be formulas to the rescue. So how do we do formulas for this? Well, we can use a formula here, I'm going to hit an equal sign, and I'm going to use the VLookup formula. I found this just by searching through here and reading descriptions.
The VLookup formula allows me to look up data in another table. So I'm going to do VLookup, there we go, and the first parameter is going to be What to lookup. So I'm going to lookup the date that's here to the left. The second parameter is going to be the set of rows, a whole range of cells, that I want to lookup. So I'm going to actually stretch across these two columns here. The third parameter would be which of the columns should hold the data that gets returned. So it's the second column, the weight here.
Then I can see here there is another one here for what to do if there is a close match. I'm going to set that to false. Just return an error. 
So now I get 180 because it looks up here and it says 1/6 is 180. If I were to copy this and paste it into all of the cells here I'd get a lot of errors and the correct one for the dates it has. So the 6th, 7th, 8th is 180, 180, 180, 178. 1/15 should be 174 and sure enough it is. So now I've got a table that automatically fills up with data based on this data here. If I add more to this it will reflect it.
So, for instance, if I were to insert another row, because I found out I had this data, I can now see that 177 is automatically filled in there. So if I extended this out to be all the way to the end of the year, for instance, and then I kept adding more to this table this table would automatically grow.
Now let's turn this into a chart. I'm going to select the entire table and I'm going to turn it into a chart. I'm going to do a line chart here and you could see now it actually correctly scatters things across. See there is that big gap between the 18th and the 30th. These are consecutive.
That basically gets me what I want. I would like it to be a little neater in that I've got lines between these dates because they are consecutive and then it breaks the lines here because there is just errors for these dates. 
So some experimentation told me that I could, if go to 2D lines and 2D area it actually fills things in very nicely. So one of things I want to do here is I want to change the minimum value, as I should, to zero because it makes more sense. 0 to 180 so everything is represented normally here. So you can see it actually follows along really nicely filling in the gaps. 
I can select this area here, go into Style, and tell it I want you to have a line at the top but no fill. Now I've got a nice line exactly the way I want it. It doesn't show anything down here it just shows the line at the top and it fills in all the gaps. I get a really good sense of all the data going across even though there are blank spots.
So that's how you do it. That's how you make it so it goes to fill a graph in with non-consecutive dates. I'm going to cut that, Command X, and paste it into here because now I can actually use this this chart here, ignore this in the background, and if I add a new date, for instance if I add another one, you can see it added it automatically to this chart. So I can just forget about this working in the background and use this and get the results shown here.

Here is the Numbers file if you would like to play around with it yourself: NonConsecutiveNumbers.zip.

Comments: 6 Responses to “Numbers Charts With Non-Consecutive Data”

Antrim

8 years ago

Great tip. Too bad that this ability is not built in, since people have to do this all the time. Is there an easier way in Excel?

Gary Rosenzweig

Antrim: Not sure if Excel will just automatically do this. But Google Docs spreadsheets will automatically space the dates on charts.

Robyn

Gary...brilliant! You are so clever.

Dorit

Maybe I didn't get the problem yet, but why don't you use the built in scatter diagram type, where you have one column for the horizontal (x) axis and another one for vertical (y) axis?

Dorit: Scatter charts show two sets of data. You can't use them to show a single set of data with two dimensions (value and date). They just can't be used in this way.

Eran Livne

Brilliant, thanks for the tips.

Comments Closed.

Numbers Charts With Non-Consecutive Data

Comments: 6 Responses to “Numbers Charts With Non-Consecutive Data”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information