Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tom" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Tom" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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" wrote: 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! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
Works perfectly....thanks so much!
"Bernard Liengme" wrote: 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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Tom" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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 wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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) "Andy Pope" wrote: 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 wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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 "Andy Pope" wrote: 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 wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message ... 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 "Andy Pope" wrote: 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 wrote: 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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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,She et2!$A$4,Sheet2!$A$5,Sheet2!$A$6, Sheet2!$A$7,Sheet2!$A$8,Sheet2!$A$9,Sheet2!$A$10,S heet2!$A$11,Sheet2!$A$12,Sheet2!$A$13, Sheet2!$A$14,Sheet2!$A$15,Sheet2!$A$16,Sheet2!$A$1 7,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,Sh eet2!$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 Tushar Mehta wrote: 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. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting non-contiguous data
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Tom" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting Hidden Data | Charts and Charting in Excel | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |