Numbers Dynamic data range for a chart

Hi Lszip,

Do you mean an interactive graph (chart)?

Have a look at Template Chooser > Graphing Basics > Interactive Graph.

Or, you can roll your own interactive graph to show different rows of data.

Type a number into "Data for Graph" A2 to identify a row (Week) in the "Data" table.

The functions INDEX and MATCH will pull data for that row.

Formula in "Data for Graph" B2 is INDEX(Data::B,MATCH($A2,Data::$A,0))

Fill right.

My cheat sheet for INDEX and MATCH:

=INDEX(Column I want a return value from,MATCH(My Lookup Value,Column I want to Lookup against,Enter “0″ ))

Use the "Data for Graph" table to create your graph.

Choose another week:

Regards,

Ian.

I don't know what you are describing in Excel but, in Numbers, a chart gets its data from the specific range(s) you select when creating the chart. The range in a chart cannot be a formula (such as OFFSET). To make a dynamic chart in Numbers you can make a table that fills in its data dynamically (using formulas) from somewhere else, then make the chart from this table. As the data changes in the table, so will the chart.

Lszlp wrote:

in Excel ... with Offset command then you can put it into serie of chart as range.

Your chart change when your data range change.

Not sure if this specifically addresses your question but in Numbers the chart range can expand automatically when you add new data without having to use OFFSET.

In Numbers, unlike in Excel, it is easy to have multiple tables on a sheet. That means you can have a separate data table for your chart. When you make a chart refer to an entire column in the table rather than a range within a column as you would typically have to do in Excel. Referring to the entire column means whenever you add a new row the chart includes that new data automatically.

For a working example, go in your menu to File > New and have a look at the 'Charting Basics' template. Go to the 'Scatter Chart; sheet and add a new row of data to the data table there. You will see that the chart automatically updates to include the new row of data you added.

You can incorporate this idea into your other charts. Refer to entire columns, not ranges of data within columns. (This goes for formulas too, not just charts).

SG

One other thing you can do is use a filter on the table to hide rows you do not want charted. The default setting for charts is to not show hidden data. You will have to switch the filter on/off depending on whether you want to work on the table or see your abbreviated chart. If a filter is on, formulas will not automatically populate into newly added rows of your table.

Other than Excel being a Microsoft product and costing a lot of money for a 365 subscription (it adds up fast year after year after year), it is pretty much the gold standard for spreadsheets and sometimes you gotta do what you gotta do.

Hi Durandal,

Using the Charm of Numbers, we can modify a graph with click and drag. No formulas.

I have plotted the last 3 rows, rather than the last 90 rows, simply to make a smaller screen shot.

Add another row, click on the graph and click on Edit Data References

Have a play. Drag the black filled cells up, then drag the white dot (the Fill Handle) down to increase the number of rows on the graph. (or drag up to decrease!)

Happy Numbering!

Ian.

The advice in this forum is to use the tool that suits the job. If Numbers is not the right tool, then good luck with your project using OFFSET.

Found this while looking for the same. None of the answers really help.

To clarify the situation, imagine I have a sheet with a growing history of data points, one per day. It already contains hundreds of rows and I only want to graph the last 3 months (90 rows). Every day I want to graph the last 3 months.

With Excel I could do this automatically through OFFSET.

With Numbers sounds like I am supposed to create manually an auxiliary table to reflect those last 90 rows through formulas, and graph that?

And then, if I want to plot the last 6 months instead, I have to extend that auxiliary table?

Sounds like a lot of busywork :/

Well, duh. Yes, I can redefine the charted range every single day. Fortunately my case is simple enough.

but OK.

With Excel looks like this would be fully automatic.

[Edited by Moderator]

Badunit wrote:

One other thing you can do is use a filter on the table to hide rows you do not want charted. The default setting for charts is to not show hidden data.

Upvote for BD!

Ian.