I want to create a formula or indirect reference which will return a range. This formula would be used as the chart series data. This would allow me to automatically change my series as data is added to the table. Is this possible in Numbers?
If I remember well this problem was already treated.
Build the table to chart with more rows than the ones extracting values from already filled cells.
Insert the formula extracting values in every cells of the table using the function ISERROR.
This way, the cells pointing to inexistent cells will contain the nil string and the chart will be correctly built.
When you will insert new values, they will appear in the charted range so they will be charted.
In cell B2, the formula is :
=IFERROR(Source :: B2,"")
In cell C2 the formula is :
=IFERROR(Source :: C2,"")
Apply Fill Down to fill the two columns.
Yvan KOENIG (VALLAURIS, France) vendredi 1 juillet 2011 12:10:46
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8
Please : Search for questions similar to your own before submitting them to the community
To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !
If I remember well this problem was already treated.
Build the table to chart with more rows than the ones extracting values from already filled cells.
Insert the formula extracting values in every cells of the table using the function ISERROR.
This way, the cells pointing to inexistent cells will contain the nil string and the chart will be correctly built.
When you will insert new values, they will appear in the charted range so they will be charted.
In cell B2, the formula is :
=IFERROR(Source :: B2,"")
In cell C2 the formula is :
=IFERROR(Source :: C2,"")
Apply Fill Down to fill the two columns.
Yvan KOENIG (VALLAURIS, France) vendredi 1 juillet 2011 12:10:46
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8
Please : Search for questions similar to your own before submitting them to the community
To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !
Thanks. That works for scatter charts, and scatter charts limit you to one series. Is there a workaround to get a scatter chart to work for more than one series, or is there a way to do this with line charts as well?
I applied that to scatter chart but it apply exactly the same way for other charts.
I wish to add that a spreadsheet document is not a rabid dog.
I never got report of a table snapping at hand a user testing it's behavior😉
Yvan KOENIG (VALLAURIS, France) vendredi 1 juillet 2011 13:44:05
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8
Please : Search for questions similar to your own before submitting them to the community
To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !
Raymos wrote:
...and scatter charts limit you to one series.
Not so.
Here's a scatter chart with three series, all sharing the same x values. The individual series can have independent x values. In that case, you will need two columns on the table for each series of x,y pairs.
Regards,
Barry
Thanks Barry, that worked although I have to duplicate the x axis (2n -1) times [n = # of series] to get this to work as intended.
The scatter chart automatically adjusts for the null set generated by the IFERROR formula. If that is not used, the chart just goes on forever as shown in KOENIG's post.
The goal is to get the chart to automatically resize and adjust its start and finished axis based on dates entered by a user in a form on a separate sheet. (Basically, pull the data between a start and end date & use that to create the chart. The columns to graph are selected by the form user as well.) Numbers forces you to duplicate data unless it is formatted exactly as the chart requires.
I ended up getting this to work using Excel, without all of the data duplication.
Thanks for your assistance.
KOENIG, if I may pose a very similar question.
I am building a very simple spreadsheet to plot the daily sales of my store. As the original poster had said, I want the spreadsheet to automatically expand as new data is put in. I tried the method you described above.
In the table that the chart pulls from, the IFERROR function does return a blank value, however, the chart does not just constrict it self to that data.
Any ideas?
It's the normal behavior.
If you look at my screenshots, the X_axis is drawn for every selected rows.
IFERROR isn't used to conscript the chart but to get rid of the red triangles flagging errors issued when the formulas are pointing a cell which doesn't exist in the source table.
As the formula is the same in the entire column, you may reduce the height of the table and add seven rows every week so that the empty area remain with an acceptable size.
An alternate scheme is to use a scatter chart with lines joining points.
This kind of chart doesn't draw extraneous axis.
Yvan KOENIG (VALLAURIS, France) lundi 4 juillet 2011 20:45:07
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8
Please : Search for questions similar to your own before submitting them to the community
To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !
Ah ok. I'll play around with some other options. Thanks for your help!
Can Numbers have a formula for a chart series?
