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 |
#11
|
|||
|
|||
Re-imagining charting software (was Axis with 2 labels)
(and therein lies an interesting bug/feature/property that's the key to a
favourite trick of mine for quick and dirty step charts) Ooh, tell us more! - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Wed, 2 May 2007, in microsoft.public.excel.charting, Kelly O'Day said: If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart - Line Chart terminology. To me, all 2 D charts are XY charts; scatter and line charts are just special forms of XY charts. How many questions show up on the Chart forum because users want to have a "line" chart with numeric values for X and Y. Yes, there is an important difference between the Scatter chart and the Line chart, but it's a difference whose value should not be held in the "Chart type" field, but in the "Axis type" field. The Format Axis dialogue box should offer the types "Nominal", "Ordinal", and "Interval" as named by S. S. Stevens in the 1940s. "Nominal" is like Excel's "Category" "Ordinal" is like Excel's "Time-scale" (and therein lies an interesting bug/feature/property that's the key to a favourite trick of mine for quick and dirty step charts) "Interval" is the scale type Excel calls "Category" when the chart type is Scatter. But the chart type is an inappropriate way of controlling the difference, a bad early design choice by Microsoft that's now frozen in. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#12
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
On Wed, 2 May 2007, in microsoft.public.excel.charting,
Jon Peltier said: "Del Cotter" wrote "Ordinal" is like Excel's "Time-scale" (and therein lies an interesting bug/feature/property that's the key to a favourite trick of mine for quick and dirty step charts) Ooh, tell us more! (This works for me using Excel 97 under Windows XP) Consider the following range Series1 1 4 2 2 3 8 4 4 5 7 6 9 7 5 8 6 Where the label "Series1" occupies the cell B2, the X range is A2..A9 and the Y range is B2..B9. If you quickly use the Chart Wizard to create a scatter chart with lines and markers, the Source Data will look like this: X Values: =Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9 So far, nothing surprising. Now if you go in and manually edit the range (first remembering that F2 key!) so it looks like this: X Values: =Sheet1!$A$2:$A$9,Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9,Sheet1!$B$2:$B$9 (i.e. the same ranges copied twice and separated by a comma) nothing changes on the scatter graph, except that there is now a line zipping back to the beginning: the data is just being drawn twice. Now go in and manually edit the range again so it looks like this: X Values: =(Sheet1!$A$3:$A$9,Sheet1!$A$2:$A$9) Y Values: =(Sheet1!$B$2:$B$8,Sheet1!$B$2:$B$9) (i.e. the X range first part has lost its first row cell, and the Y range first part has lost its last row cell) the scatter chart now looks like it has double vision: The line is drawn across the chart, zips back, and draws itself again displaced one place to the left. Now change the chart type to Line, and change the X axis to Time-scale, and see what you get. !! How does it work? Whereas the line in a scatter chart is drawn strictly in the order the data points appear in the range, the line in a Time-scale Line chart is drawn from left to right in the order the points appear on the time-scale! I expect somebody who knows VBA could turn this into a macro. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#13
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
I wondered if that was your trick. A fellow named Michel Gerday sent me a
file in which he used names to get this effect in line charts: http://peltiertech.com/Excel/ChartsHowTo/StepChart.html Pretty neat, unfortunately in breaks down if the data is in an Excel 2003 list, because the list will incorrectly modify the definition of the names if the size of the list changes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Wed, 2 May 2007, in microsoft.public.excel.charting, Jon Peltier said: "Del Cotter" wrote "Ordinal" is like Excel's "Time-scale" (and therein lies an interesting bug/feature/property that's the key to a favourite trick of mine for quick and dirty step charts) Ooh, tell us more! (This works for me using Excel 97 under Windows XP) Consider the following range Series1 1 4 2 2 3 8 4 4 5 7 6 9 7 5 8 6 Where the label "Series1" occupies the cell B2, the X range is A2..A9 and the Y range is B2..B9. If you quickly use the Chart Wizard to create a scatter chart with lines and markers, the Source Data will look like this: X Values: =Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9 So far, nothing surprising. Now if you go in and manually edit the range (first remembering that F2 key!) so it looks like this: X Values: =Sheet1!$A$2:$A$9,Sheet1!$A$2:$A$9 Y Values: =Sheet1!$B$2:$B$9,Sheet1!$B$2:$B$9 (i.e. the same ranges copied twice and separated by a comma) nothing changes on the scatter graph, except that there is now a line zipping back to the beginning: the data is just being drawn twice. Now go in and manually edit the range again so it looks like this: X Values: =(Sheet1!$A$3:$A$9,Sheet1!$A$2:$A$9) Y Values: =(Sheet1!$B$2:$B$8,Sheet1!$B$2:$B$9) (i.e. the X range first part has lost its first row cell, and the Y range first part has lost its last row cell) the scatter chart now looks like it has double vision: The line is drawn across the chart, zips back, and draws itself again displaced one place to the left. Now change the chart type to Line, and change the X axis to Time-scale, and see what you get. !! How does it work? Whereas the line in a scatter chart is drawn strictly in the order the data points appear in the range, the line in a Time-scale Line chart is drawn from left to right in the order the points appear on the time-scale! I expect somebody who knows VBA could turn this into a macro. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#14
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
On Thu, 3 May 2007, in microsoft.public.excel.charting,
Jon Peltier said: Pretty neat, unfortunately in breaks down if the data is in an Excel 2003 list, because the list will incorrectly modify the definition of the names if the size of the list changes. If you can't do it with names in XL2003, can you do it with OFFSET? -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#15
|
|||
|
|||
Del's Quick & Dirty Step Chart (was Re-imagining charting software)
You can do it with names if you don't use lists. I presume one could define
names in a different clever way to overcome the problem. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Del Cotter" wrote in message ... On Thu, 3 May 2007, in microsoft.public.excel.charting, Jon Peltier said: Pretty neat, unfortunately in breaks down if the data is in an Excel 2003 list, because the list will incorrectly modify the definition of the names if the size of the list changes. If you can't do it with names in XL2003, can you do it with OFFSET? -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
|
Thread Tools | |
Display Modes | |
|
|