charting non-contiguous data

[Tom]

Hi all,
Can someone tell me how to add non-contiguous data to a chart. My data is in
B21, G21, L21........IR21. Ideally, the chart would auto-update to include
data as it is entered on a weekly basis.

Thanks!

 

[Jon Peltier]

Running out of weeks, since the last day goes into IV21.

Ideally, you would set up a contiguous chart data range in another sheet, or
in another part of this sheet, which links to the discontiguous data. The
problem is that the individual cells of your chart data are stored as a
sheet name and cell address in the series formula, taking a dozen or more
characters per cell, and you only get around 250 characters for X and 250
for Y.

You can easily use index or offset formulas to extract the data you need:

In cell B41, for example, use one of these:

=OFFSET($B$21,0,(COLUMN()-2)*5)
=INDEX($B$21:$IR$21,(COLUMN()-2)*5+1)

and fill across to BA41.

- Jon

 

[Bernard Liengme]

If I had this problem I would contrive to collect the data to be plotted in
a contiguous range.
Suppose your data is in Sheet1 B21:IR21
On another sheet in A1 enter =INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5)
Copy this AY1
Now plot this data - the chart can be anywhere you like - it need not be on
the sheet with the data.
To make the chart dynamic change the formula to
=IF(INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),NA())
and plot the whole range A1:AY1. When cells on Sheet1 are blank, the data to
plot will display #N/A and this will be ignore by the chart engine.
best wishes

 

[john the confused]

You can select your data by holding the Ctrl key and then use the chart
wizard. When you want to add new data, enter it in the cell and then either
click & drag it to the chart or use copy & paste
--
John
MOS Master Instructor
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Tom]

Works perfectly....thanks so much!

If I had this problem I would contrive to collect the data to be plotted in
a contiguous range.
Suppose your data is in Sheet1 B21:IR21
On another sheet in A1 enter =INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5)
Copy this AY1
Now plot this data - the chart can be anywhere you like - it need not be on
the sheet with the data.
To make the chart dynamic change the formula to
=IF(INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),INDEX(Sheet1!$A$21:$IR$21,2+(COLUMN()-1)*5),NA())
and plot the whole range A1:AY1. When cells on Sheet1 are blank, the data to
plot will display #N/A and this will be ignore by the chart engine.
best wishes

 

[Andy Pope]

Hi,

A word of caution with this approach.

Using the ctrl key to select dis-contiguous cells will work but only to
a point. Once the length of the series formula exceeds 1024, I think
that's the cutoff length, you will not be able to add anymore points to
that series. When you consider each point requires a sheet and cell
reference the formula can get very long very quickly.

Cheers
Andy

 

[john the confused]

Point taken - I forgot about the limits.
You still gives you upto about 80 data cells to a series though.
--
John
MOS Master Instructor
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Tushar Mehta]

Hi Andy,
It used to be about 250 characters per series component (i.e., x-values
specs, y-values specs etc.). Maybe those individual limits got lifted at
some point but they seem to be back w/2007. I just tried plotting a simple
clustered column chart and XL balked at about 256. Of course, the way it
complained was bizarre.

When creating a chart (changed the worksheet name to the longest allowed,
entered =ROW() in a column and CTRL+picked every alternate cell), the error
message I got was "some types of charts cannot be combined with others."
Don't ask why.

When trying to extend a series formula, nothing happens. Edited the current
formula by entering a comma before the closing parentheis and CTRL+clicked to
add new cells. If the resulting length was > 250-whatever, pressing ENTER
did nothing. No error message, no nothing. It showed the updated formula in
the formula bar but it wouldn't accept it nor provide any kind of error
message. You just had to know that the problem was the length of the
y-values references.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Jon Peltier]

I did a little playing in Excel 2003. If you use the Source Data > Series
dialog, you cannot enter more than about 256 characters into the X Values or
Y Values box. If you directly edit the series formula, it seems you can use
nearly all of the 1024 characters for the Y Values, provided the X Values
argument is omitted. This last surprised me, because I've thought for a long
time that the overall series length didn't matter, but only the length of
the X and Y components of the formula. It may be that earlier Excels had
this limit, and by 2003 the limit was removed, or it may be that the limit
still exists in VBA, which is where I've spent more time trying to get
around it. Certainly VBA can't do as much with a chart series as a user can
working within the user interface.

- Jon

 

[Andy Pope]

Hi Tushar,

As Jon has posted the point of entry is key to passing the 256 limit.
BUT I have just had a play. I used the formula bar to extend the chart
series, I realise the cells choose are in fact contiguous I was being lazy.

=SERIES(,,(Sheet2!$A$1,Sheet2!$A$2,Sheet2!$A$3,Sheet2!$A$4,Sheet2!$A$5,Sheet2!$A$6,
Sheet2!$A$7,Sheet2!$A$8,Sheet2!$A$9,Sheet2!$A$10,Sheet2!$A$11,Sheet2!$A$12,Sheet2!$A$13,
Sheet2!$A$14,Sheet2!$A$15,Sheet2!$A$16,Sheet2!$A$17,Sheet2!$A$18,Sheet2!$A$19,Sheet2!$A$20),1)

When you open the Source Data dialog AND the Data Range tab is active it
reports a formula error as it truncates the formula!

Building the non contiguous range using Named ranges seems to work.

ChtD1:
=(Sheet2!$A$1,Sheet2!$A$2,Sheet2!$A$3,Sheet2!$A$4,Sheet2!$A$5,Sheet2!$A$6,Sheet2!$A$7,Sheet2!$A$8,Sheet2!$A$9,Sheet2!$A$10,Sheet2!$A$11,Sheet2!$A$12)

ChtD2 and ChtD3 are the same but for columns B and C

Series formula is
=SERIES(,,(Book1!ChtD1,Book1!CHtD2,Book1!CHtD3),1)

And this does not display in the Data Range section of the dialog.

If you take this a step further and create a named range that joins the
other named ranges,
AllData:
=(ChtD1,CHtD2,CHtD3)

You can cut the series formula back to,
=SERIES(,,Book1!AllData,1)

and still get all the points.

Cheers
Andy

 

[Mark Schreiber]

Bernard,

Thanks for your description of how to combine the INDEX and NA() functions
to get a chart to ignore certain cells. I have been looking for a way to do
that.

Mark Schreiber