If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I get 3 series in sync with the x-axis?
Long version of my question:
When I put three series in one chart (for example a line chart with a time-scale as the x-axis) the data items in the first series are distributed properly along the x-axis according to their associated dates, but the items that belong to the other two series (fewer items than the first series posesses) are displayed in the same positions with respect to the x-axis as the initial items in the first series and therefore are displayed as belonging to the wrong dates. If the first series has two items occuring on the same date, then every series has two items occuring on that date, whether or not the data agrees. This happens with integers and such as well -- not just with dates. It's as though the x-axis is only for the first series and the items of other series just piggyback the first n items of the first series, where n is the numer of items in the secondary or tertiary series. Short version of my question: Why is the chart ignoring the associated date data of all but the first series? Perhaps I only need somehow to inform it or are Excel charts not capable of this? Thanks for your time. |
#3
|
|||
|
|||
(a) Scatter charts seems to have the same problem. So far I've tried line,
column, bar, and area, as well to no avail. (b) Yep, the x-axis already has its Type set to Time. Thank you for the suggestions nonetheless. Here's an example for anyone who wants to test it out: (This would be the REALLY long version of my question.) Let's assume the first date is in cell A1 and the first integer is in cell B1. 1/1/2004 1 1/3/2004 3 1/4/2004 5 1/7/2004 7 1/1/2004 22 1/5/2004 44 1/7/2004 66 Insert a chart. In the Series tab: Make a new series named Series1and set it to B1:B4. Make a new series named Series2 and set it to B5:B7. Set "Catagory (X) axis labels" to A1:A4. When you get to the Axes tab, set Category (X) axis to Time-scale. Check it out. Series1 is fine, appearing on the 1st, 3rd, 4th, and 7th. Series2 which should appear on the 1st, 5th, and 7th instead appears on the 1st, 3rd, and 4th, which are by no coincidence the first three dates in Series1. (By the way, including the dates related to Series2 in the time-scale by setting "Catagory (X) axis labels" to A1:A7 does not change anything.) See how the chart ignores the dates associated with 22, 44, and 66? Worse yet... sort the data by the date so that it looks like this: 1/1/2004 1 1/1/2004 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 1/7/2004 66 The series get messed up because they refer to the same cell locations as they did before the sort, so redefine the series by right-clickign on the chart|Source Data|Series Tab -- You can use the control key to select non-adjacent cells. So now Series1 once again refers to 1, 3, 5, and 7 and Series 2 refers to 22, 44, and 66. Check out the chart. It's ignoring the dates associated with BOTH series. It lines up the first n items in Series1 and the first n items in Series 2 with the first n dates. That is actaully what it was doing all along, but it was harder to see before the sort when the first n dates were appropriate for the first n items in Series1. Surely it would be more useful for a chart to match up items in series with their associated labels which are a subset of axis labels than with the first n axis labels, yes? Does anyone have more ideas? "Tushar Mehta" wrote: (a) Consider a XY Scatter chart (b) If you must use a Line chart, see if setting the x-axis to type Time helps. With the chart selected, select Chart | Chart Options... | Axes tab. In there, for the Primary Category (X) axis, set the type to Time (not Automatic and not Category). -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
#4
|
|||
|
|||
Thanks for sharing the example. A XY Scatter chart works just fine.
Note that it is different from a line chart. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... (a) Scatter charts seems to have the same problem. So far I've tried line, column, bar, and area, as well to no avail. (b) Yep, the x-axis already has its Type set to Time. Thank you for the suggestions nonetheless. Here's an example for anyone who wants to test it out: (This would be the REALLY long version of my question.) Let's assume the first date is in cell A1 and the first integer is in cell B1. 1/1/2004 1 1/3/2004 3 1/4/2004 5 1/7/2004 7 1/1/2004 22 1/5/2004 44 1/7/2004 66 {snip} |
#5
|
|||
|
|||
Oooh, you're right -- a scatter chart does work! What didn't work was
converting from one of the other types to a scatter. Thank you. Unfortunately I can't do everything with scatter charts. Alas. Anyone with more ideas? -Nathan "Tushar Mehta" wrote: Thanks for sharing the example. A XY Scatter chart works just fine. Note that it is different from a line chart. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
#6
|
|||
|
|||
Tushar -
Like all Line charts, the chart uses only the first series' categories for all the series in that chart group. It doesn't matter that the categories are almost treated as numerical values. What can be done is to use the same dates for all series, with a blank cell if the date has no Y value. Then under Tools Options Chart, the Interpolate Blanks option should be selected. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Tushar Mehta wrote: (a) Consider a XY Scatter chart (b) If you must use a Line chart, see if setting the x-axis to type Time helps. With the chart selected, select Chart | Chart Options... | Axes tab. In there, for the Primary Category (X) axis, set the type to Time (not Automatic and not Category). |
#7
|
|||
|
|||
What can't you do with an XY Scatter chart? I'll bet someone has a workaround.
I gave a suggestion in my reply to Tushar's first response: list all the dates in one column and use this as the X data source. In the columns with the series Y values, put data only next to the dates that have values, and leave the rest blank. Make your line chart, then from Tools Options Chart, choose Interpolate Blank Cells. See, what happens is that a category axis (line chart, area chart, column chart, bar chart) has fixed categories, defined by the first series. All subsequent series are made to conform to these categories. A time scale axis seems to break free of the category framework, but all it does is arrange the categories by date, but the third point of any series plots above the third category, without any regard to the series having independently specified categories of its own. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ zizbird wrote: Oooh, you're right -- a scatter chart does work! What didn't work was converting from one of the other types to a scatter. Thank you. Unfortunately I can't do everything with scatter charts. Alas. Anyone with more ideas? -Nathan "Tushar Mehta" wrote: Thanks for sharing the example. A XY Scatter chart works just fine. Note that it is different from a line chart. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
#8
|
|||
|
|||
What I can't do with an XY Scatter chart is have it be a Column chart
Other than that, yes, XY Scatter seems golden. So as I understand your suggestion my data will plot right if I reorganize it to look like this (and use the dates to define the x-axis, the first column of integers as Series1, and the second colum of integers as Series2): 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 I tried this and it works great with an XY Scatter and the other types of charts as well! Wonderful to have a non-XY-Scatter chart display the data accurately for a change Unfortunately however, some people don't have authority to change their layouts. I do in this case, but I'm sure I won't always. In my case I can't reorganize my layout this way for another reason. In the examples, your layout is marginally more complex than mine, but in real life for my data your layout would be unmanageable as an entry point for data -- and I'm not faulting you here of course -- you have no way to know what my real-life data looks like. That said, inherent in the reorganization you suggested is a tendency toward unmanagability for lots of folks. I'll try to explain why this is so for anyone reading who has the same problem as me who doesn't see it... A lot of spreadsheets keep track of events as they occur, such as expenditures of funds or accumulation of whatever quantities. When an event occurs, the date, an item that implies distinction between series such as a person's name or the reason for the expenditure, and perhaps 5 quantities are entered, which means 7 columns in total for example. Each time an even occurs, a new row is added. Most anyone looking at the data can understand it quickly and enter the data in the correct cells, probably with no need to scroll horizontally which, let's face it, confuses a lot of people. See how that layout corresponds nicely to the two columns in my initial example? DATE DATA 1/1/2004 1 1/3/2004 3 1/4/2004 5 1/7/2004 7 1/1/2004 22 1/5/2004 44 1/7/2004 66 Note that there is a third implied column in my example that differentiates between the series. I took a shortcut and used single-digit data versus double-digit data to distinguish between the series, but that isn't likely to happen in reality. So with that implied column spelled out, my simple initial example looks like this: DATE DATA SERIES_IDENTIFIER 1/1/2004 1 S1 1/3/2004 3 S1 1/4/2004 5 S1 1/7/2004 7 S1 1/1/2004 22 S2 1/5/2004 44 S2 1/7/2004 66 S2 (S1 is what makes the data belong in Series1, and S2 is... you get it.) See how that looks a lot like my event-tracking example now? Just add 4 more columns like the one titled DATA and it's a perfect match. Maybe we should call the data columns D1, D2, D3, D4, and D5. If the layout is changed to make columns distinguish between different series like this: DATE S1D1 S2D1 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 (Column S1D1 contans the first quantities recorded for Series1 and column S2D1 contains the first quantities recorded for Series2.) ....And then the data plots correctly. Remember when the first simple example layout was fleshed out to match the event-tracking example by adding 4 columns of data? To flesh out this new layout, 8 columns of data must be added: S1D2, S2D2, S1D3, S2D3, S1D4, S2D4, S1D5, and S2D5. So you see one dimension of potential unmanageability inherent here. The other dimension is when more series are needed. In the first layout if you need to plot 10 series, what do you do? DATE DATA SERIES_IDENTIFIER 1/1/2004 1 S1 1/3/2004 3 S1 1/4/2004 5 S1 1/7/2004 7 S1 1/1/2004 22 S2 1/5/2004 44 S2 1/7/2004 66 S2 Record values like S3, S4, S5 ... S10 in the SERIES_IDENTIFIER column and the total number of columns remains 3 (or remains 7 in the case of 5 data columns). In the second layout if you need to plot 10 series, what do you do? DATE S1D1 S2D1 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 Add columns like S3D1, S3D2, S4D1, S4D2... and the total number of columns becomes 11 (or becomes 51 in the case of 5 data columns). Of course with all the empty cells and longer column names, readabilty/manageability suffers. So readers I'm sure can see that with few types of data and few series the second layout with the columns doing double-duty keeping track of the data and keeping track of series works fine (with the added bonus of the chart actually plotting the data in a way that a human being would expect and interpret as correct) but with more types of data and more series the layout can get out of hand. Anyway, what was I going to say? Oh, yeah. Thanks very much Jon. I'm sure half of the future readers with my problem will be able to use your suggested layout and leave happy. I'm sure many times I'll be able to use that suggestion too, or Tushar's, but alas, I won't be able to use it this time, so my question becomes: Is there any way to get 3 series in sync with the x-axis using a non-XY-Scatter chart without changing the basic layout of my data? -Nathan "Jon Peltier" wrote: What can't you do with an XY Scatter chart? I'll bet someone has a workaround. I gave a suggestion in my reply to Tushar's first response: list all the dates in one column and use this as the X data source. In the columns with the series Y values, put data only next to the dates that have values, and leave the rest blank. Make your line chart, then from Tools Options Chart, choose Interpolate Blank Cells. See, what happens is that a category axis (line chart, area chart, column chart, bar chart) has fixed categories, defined by the first series. All subsequent series are made to conform to these categories. A time scale axis seems to break free of the category framework, but all it does is arrange the categories by date, but the third point of any series plots above the third category, without any regard to the series having independently specified categories of its own. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
#9
|
|||
|
|||
To make a long story short...
Your efforts to make the data entry system comprehensible to the user is admirable. Of course, there's no rule that says the data entered must be plotted as is. For uncomplicated data arrangements, simple worksheet formulas or defined names can make the data plottable. The data that you show is perfectly suited to analysis by pivot table. DATE DATA SERIES_IDENTIFIER 1/1/2004 1 S1 1/3/2004 3 S1 1/4/2004 5 S1 1/7/2004 7 S1 1/1/2004 22 S2 1/5/2004 44 S2 1/7/2004 66 S2 In fifteen seconds I turned the data above into the chart-ready table below: Sum of DATA2 SERIES_IDENTIFIER DATE S1 S2 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 If you use a dynamic range to define the pivot table source data, refreshing the table will capture the added data. A pivot chart will update automatically when the pivot table is updated, but cannot render a time scale axis. A regular chart will only update the number of points in a series using named ranges for series data, but will not add series accordingly. So you should use a regular chart, and update it with a macro after refreshing the pivot table. Thanks to Debra Dalgleish, there is a section on my web site that can help you get started with pivot tables: http://peltiertech.com/Excel/Pivots/pivotstart.htm Debra adds to this on her own site. Look under P in the index: http://contextures.com/tiptech.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ zizbird wrote: What I can't do with an XY Scatter chart is have it be a Column chart Other than that, yes, XY Scatter seems golden. So as I understand your suggestion my data will plot right if I reorganize it to look like this (and use the dates to define the x-axis, the first column of integers as Series1, and the second colum of integers as Series2): 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 I tried this and it works great with an XY Scatter and the other types of charts as well! Wonderful to have a non-XY-Scatter chart display the data accurately for a change Unfortunately however, some people don't have authority to change their layouts. I do in this case, but I'm sure I won't always. In my case I can't reorganize my layout this way for another reason. In the examples, your layout is marginally more complex than mine, but in real life for my data your layout would be unmanageable as an entry point for data -- and I'm not faulting you here of course -- you have no way to know what my real-life data looks like. That said, inherent in the reorganization you suggested is a tendency toward unmanagability for lots of folks. I'll try to explain why this is so for anyone reading who has the same problem as me who doesn't see it... A lot of spreadsheets keep track of events as they occur, such as expenditures of funds or accumulation of whatever quantities. When an event occurs, the date, an item that implies distinction between series such as a person's name or the reason for the expenditure, and perhaps 5 quantities are entered, which means 7 columns in total for example. Each time an even occurs, a new row is added. Most anyone looking at the data can understand it quickly and enter the data in the correct cells, probably with no need to scroll horizontally which, let's face it, confuses a lot of people. See how that layout corresponds nicely to the two columns in my initial example? DATE DATA 1/1/2004 1 1/3/2004 3 1/4/2004 5 1/7/2004 7 1/1/2004 22 1/5/2004 44 1/7/2004 66 Note that there is a third implied column in my example that differentiates between the series. I took a shortcut and used single-digit data versus double-digit data to distinguish between the series, but that isn't likely to happen in reality. So with that implied column spelled out, my simple initial example looks like this: DATE DATA SERIES_IDENTIFIER 1/1/2004 1 S1 1/3/2004 3 S1 1/4/2004 5 S1 1/7/2004 7 S1 1/1/2004 22 S2 1/5/2004 44 S2 1/7/2004 66 S2 (S1 is what makes the data belong in Series1, and S2 is... you get it.) See how that looks a lot like my event-tracking example now? Just add 4 more columns like the one titled DATA and it's a perfect match. Maybe we should call the data columns D1, D2, D3, D4, and D5. If the layout is changed to make columns distinguish between different series like this: DATE S1D1 S2D1 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 (Column S1D1 contans the first quantities recorded for Series1 and column S2D1 contains the first quantities recorded for Series2.) ....And then the data plots correctly. Remember when the first simple example layout was fleshed out to match the event-tracking example by adding 4 columns of data? To flesh out this new layout, 8 columns of data must be added: S1D2, S2D2, S1D3, S2D3, S1D4, S2D4, S1D5, and S2D5. So you see one dimension of potential unmanageability inherent here. The other dimension is when more series are needed. In the first layout if you need to plot 10 series, what do you do? DATE DATA SERIES_IDENTIFIER 1/1/2004 1 S1 1/3/2004 3 S1 1/4/2004 5 S1 1/7/2004 7 S1 1/1/2004 22 S2 1/5/2004 44 S2 1/7/2004 66 S2 Record values like S3, S4, S5 ... S10 in the SERIES_IDENTIFIER column and the total number of columns remains 3 (or remains 7 in the case of 5 data columns). In the second layout if you need to plot 10 series, what do you do? DATE S1D1 S2D1 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 Add columns like S3D1, S3D2, S4D1, S4D2... and the total number of columns becomes 11 (or becomes 51 in the case of 5 data columns). Of course with all the empty cells and longer column names, readabilty/manageability suffers. So readers I'm sure can see that with few types of data and few series the second layout with the columns doing double-duty keeping track of the data and keeping track of series works fine (with the added bonus of the chart actually plotting the data in a way that a human being would expect and interpret as correct) but with more types of data and more series the layout can get out of hand. Anyway, what was I going to say? Oh, yeah. Thanks very much Jon. I'm sure half of the future readers with my problem will be able to use your suggested layout and leave happy. I'm sure many times I'll be able to use that suggestion too, or Tushar's, but alas, I won't be able to use it this time, so my question becomes: Is there any way to get 3 series in sync with the x-axis using a non-XY-Scatter chart without changing the basic layout of my data? -Nathan "Jon Peltier" wrote: What can't you do with an XY Scatter chart? I'll bet someone has a workaround. I gave a suggestion in my reply to Tushar's first response: list all the dates in one column and use this as the X data source. In the columns with the series Y values, put data only next to the dates that have values, and leave the rest blank. Make your line chart, then from Tools Options Chart, choose Interpolate Blank Cells. See, what happens is that a category axis (line chart, area chart, column chart, bar chart) has fixed categories, defined by the first series. All subsequent series are made to conform to these categories. A time scale axis seems to break free of the category framework, but all it does is arrange the categories by date, but the third point of any series plots above the third category, without any regard to the series having independently specified categories of its own. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
#10
|
|||
|
|||
My temporary solution before reading your latest post was to use a slew of
named ranges with inelegant names like 2004eoq3c2 and to add a workbook sheet whose sole purpose was gathering totals/averages based on the actual data sheets. I added rows marked "Do not edit" to contain the named ranges so that the named ranges get moved properly when rows are inserted at the edges of the ranges. That sounds like your suggestion for "uncomplicated data arrangements". Since my data needs to be sorted by date for one chart and sorted by another item for a second chart, ranged names for both sort orders would gridlock the data: Sorted one way, inserting a row would require the redefinition of all named ranges for sorting the other way that happened to be down-page of the inserted row. Removing the simpler set of named ranges and its associated chart and re-creating that chart each time it is needed removes the gridlock, but isn't a permanent solution. I had begun to look at pivot tables and charts as a possible permanent solution but found that pivot tables appear to be static data summaries, requiring their re-creation every time new data is entered. Excel seems to have tried very hard to make pivot tables/charts easy and user-friendly but not easy enough for my end users who will only be able to enter rows of data -- nothing else. Fifteen seconds for you (and probably for me once I figure it out) is certainly quick enough but my successors won't be able to make use of this. Using a "dynamic range to define the pivot table source data" and refreshing sounds good... I think I can trust them to refresh rand run a macro... if I write a tutorial. I'll have to learn more about these things myself first -- I'll post again when I get any results to speak of. Thank you very much for the help and links. -Nathan "Jon Peltier" wrote: To make a long story short... Your efforts to make the data entry system comprehensible to the user is admirable. Of course, there's no rule that says the data entered must be plotted as is. For uncomplicated data arrangements, simple worksheet formulas or defined names can make the data plottable. The data that you show is perfectly suited to analysis by pivot table. DATE DATA SERIES_IDENTIFIER 1/1/2004 1 S1 1/3/2004 3 S1 1/4/2004 5 S1 1/7/2004 7 S1 1/1/2004 22 S2 1/5/2004 44 S2 1/7/2004 66 S2 In fifteen seconds I turned the data above into the chart-ready table below: Sum of DATA2 SERIES_IDENTIFIER DATE S1 S2 1/1/2004 1 22 1/3/2004 3 1/4/2004 5 1/5/2004 44 1/7/2004 7 66 If you use a dynamic range to define the pivot table source data, refreshing the table will capture the added data. A pivot chart will update automatically when the pivot table is updated, but cannot render a time scale axis. A regular chart will only update the number of points in a series using named ranges for series data, but will not add series accordingly. So you should use a regular chart, and update it with a macro after refreshing the pivot table. Thanks to Debra Dalgleish, there is a section on my web site that can help you get started with pivot tables: http://peltiertech.com/Excel/Pivots/pivotstart.htm Debra adds to this on her own site. Look under P in the index: http://contextures.com/tiptech.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with the display of a 2 axis chart | Peter B | Charts and Charting | 1 | August 4th, 2004 02:39 AM |
Ineffective change of plot order in series formula | Jon Peltier | Charts and Charting | 3 | May 30th, 2004 02:15 AM |
Formatting a data series to run value axis to value axis | Eva Whitley | Charts and Charting | 2 | April 29th, 2004 04:49 PM |
series and axis alighnment | sammi1977 | Charts and Charting | 1 | March 8th, 2004 12:53 PM |
Dual axis with Bar chart + two series : is it possible ?? | lOURY | Charts and Charting | 3 | February 20th, 2004 03:55 PM |