Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
Hi,
I'm not sure if this is possible, but I would like to create a combination chart the has one axis correspond to the other: For argument's sake, let's say I buy veggies at a particular price and want a chart to reflect the price and the amount purchased: Qty in lbs. 100 300 200 Price .5 1 2 I want the chart to have a marker for each of the Qty values, but the second axis displaying the Price values directly opposite the corresponding Qty value. Any help would be appreciated! -- Thanks! Dee |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
Do you have categories as well? Or would an XY chart, with Qty as X and
Price as Y suffice? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi, I'm not sure if this is possible, but I would like to create a combination chart the has one axis correspond to the other: For argument's sake, let's say I buy veggies at a particular price and want a chart to reflect the price and the amount purchased: Qty in lbs. 100 300 200 Price .5 1 2 I want the chart to have a marker for each of the Qty values, but the second axis displaying the Price values directly opposite the corresponding Qty value. Any help would be appreciated! -- Thanks! Dee |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
Yes, the column headings are dates (years).
-- Thanks! Dee "Jon Peltier" wrote: Do you have categories as well? Or would an XY chart, with Qty as X and Price as Y suffice? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi, I'm not sure if this is possible, but I would like to create a combination chart the has one axis correspond to the other: For argument's sake, let's say I buy veggies at a particular price and want a chart to reflect the price and the amount purchased: Qty in lbs. 100 300 200 Price .5 1 2 I want the chart to have a marker for each of the Qty values, but the second axis displaying the Price values directly opposite the corresponding Qty value. Any help would be appreciated! -- Thanks! Dee |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
Create your chart, with series in rows, so that years appear on the
horizontal axis. I'd say a line chart is probably best. Double click one series, and on the Axis tab, select Secondary. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Yes, the column headings are dates (years). -- Thanks! Dee "Jon Peltier" wrote: Do you have categories as well? Or would an XY chart, with Qty as X and Price as Y suffice? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi, I'm not sure if this is possible, but I would like to create a combination chart the has one axis correspond to the other: For argument's sake, let's say I buy veggies at a particular price and want a chart to reflect the price and the amount purchased: Qty in lbs. 100 300 200 Price .5 1 2 I want the chart to have a marker for each of the Qty values, but the second axis displaying the Price values directly opposite the corresponding Qty value. Any help would be appreciated! -- Thanks! Dee |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
On Mon, 27 Aug 2007, in microsoft.public.excel.charting,
dee said: "Jon Peltier" wrote: Do you have categories as well? Or would an XY chart, with Qty as X and Price as Y suffice? Yes, the column headings are dates (years). With two independent variables each against year, you need two lines, one for price against year and one for quantity against year; then you can use two y axes. Matching the axes so that you only use one line would require the two variables to be linearly dependent, as for instance when plotting temperature, when you can have an axis for Centigrade and Fahrenheit. You can still follow the XY chart solution, if you label the data points with their year, either manually or using one of the free add-ins available. -- 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. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
Hi Jon,
Thanks for your help. I tried this, but it doesn't seem to do what I am trying to do, unless I misunderstood you. Let's say I have Jan, Feb, Mar, Apr, etc. as the column headings Then I have, say row 2 that contains: $10,000 $45,000 $50,000 $18,000 and row 3 that contains: 55 65 73 20 I would like one axis to show the amounts on row 2 - no problem there. I would like the secondary axis to show the values on row 3, but... I want them directly opposite the values. So, the $10,000 would have a tick directly opposite it on the value axis that marks 55... Thanks! -- Thanks! Dee "Jon Peltier" wrote: Create your chart, with series in rows, so that years appear on the horizontal axis. I'd say a line chart is probably best. Double click one series, and on the Axis tab, select Secondary. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Yes, the column headings are dates (years). -- Thanks! Dee "Jon Peltier" wrote: Do you have categories as well? Or would an XY chart, with Qty as X and Price as Y suffice? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi, I'm not sure if this is possible, but I would like to create a combination chart the has one axis correspond to the other: For argument's sake, let's say I buy veggies at a particular price and want a chart to reflect the price and the amount purchased: Qty in lbs. 100 300 200 Price .5 1 2 I want the chart to have a marker for each of the Qty values, but the second axis displaying the Price values directly opposite the corresponding Qty value. Any help would be appreciated! -- Thanks! Dee |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
But then it's not an axis on the right, since the row 3 values are not
proportional to the row 2 values. For example, 18000 10000 (row 2, columns 1 and 4), but 20 < 55 (row 3, same columns). This is what Del pointed out in his post. It would be less confusing to add the row 3 values as data labels to the row 2 points. Use one of these free utilities to handle this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi Jon, Thanks for your help. I tried this, but it doesn't seem to do what I am trying to do, unless I misunderstood you. Let's say I have Jan, Feb, Mar, Apr, etc. as the column headings Then I have, say row 2 that contains: $10,000 $45,000 $50,000 $18,000 and row 3 that contains: 55 65 73 20 I would like one axis to show the amounts on row 2 - no problem there. I would like the secondary axis to show the values on row 3, but... I want them directly opposite the values. So, the $10,000 would have a tick directly opposite it on the value axis that marks 55... Thanks! -- Thanks! Dee "Jon Peltier" wrote: Create your chart, with series in rows, so that years appear on the horizontal axis. I'd say a line chart is probably best. Double click one series, and on the Axis tab, select Secondary. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Yes, the column headings are dates (years). -- Thanks! Dee "Jon Peltier" wrote: Do you have categories as well? Or would an XY chart, with Qty as X and Price as Y suffice? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi, I'm not sure if this is possible, but I would like to create a combination chart the has one axis correspond to the other: For argument's sake, let's say I buy veggies at a particular price and want a chart to reflect the price and the amount purchased: Qty in lbs. 100 300 200 Price .5 1 2 I want the chart to have a marker for each of the Qty values, but the second axis displaying the Price values directly opposite the corresponding Qty value. Any help would be appreciated! -- Thanks! Dee |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
Hi Jon,
For some reason, I never even saw Del's response until today. I can arrange the data in any way, so that isn't a problem. Del mentioned adding labels manually, which wouldn't be a problem, as I don't have many items to chart. I'm not sure how to do this, though. Thanks so much! -- Thanks! Dee "Jon Peltier" wrote: But then it's not an axis on the right, since the row 3 values are not proportional to the row 2 values. For example, 18000 10000 (row 2, columns 1 and 4), but 20 < 55 (row 3, same columns). This is what Del pointed out in his post. It would be less confusing to add the row 3 values as data labels to the row 2 points. Use one of these free utilities to handle this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi Jon, Thanks for your help. I tried this, but it doesn't seem to do what I am trying to do, unless I misunderstood you. Let's say I have Jan, Feb, Mar, Apr, etc. as the column headings Then I have, say row 2 that contains: $10,000 $45,000 $50,000 $18,000 and row 3 that contains: 55 65 73 20 I would like one axis to show the amounts on row 2 - no problem there. I would like the secondary axis to show the values on row 3, but... I want them directly opposite the values. So, the $10,000 would have a tick directly opposite it on the value axis that marks 55... Thanks! -- Thanks! Dee "Jon Peltier" wrote: Create your chart, with series in rows, so that years appear on the horizontal axis. I'd say a line chart is probably best. Double click one series, and on the Axis tab, select Secondary. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Yes, the column headings are dates (years). -- Thanks! Dee "Jon Peltier" wrote: Do you have categories as well? Or would an XY chart, with Qty as X and Price as Y suffice? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi, I'm not sure if this is possible, but I would like to create a combination chart the has one axis correspond to the other: For argument's sake, let's say I buy veggies at a particular price and want a chart to reflect the price and the amount purchased: Qty in lbs. 100 300 200 Price .5 1 2 I want the chart to have a marker for each of the Qty values, but the second axis displaying the Price values directly opposite the corresponding Qty value. Any help would be appreciated! -- Thanks! Dee |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
On Thu, 30 Aug 2007, in microsoft.public.excel.charting,
dee said: I can arrange the data in any way, so that isn't a problem. Del mentioned adding labels manually, which wouldn't be a problem, as I don't have many items to chart. I'm not sure how to do this, though. If you create some labels with one of the automatic options Excel provides (value or category), you can then select the label series by single-clicking on one of the labels. If you then carefully single-click again on a label (not too fast; you don't want to double-click), a single label out of the whole range is selected. Now if you go up to the formula bar above the chart and spreadsheet, and type in an equals sign followed by a cell reference, or type an equals sign and then navigate to the cell you want that label to refer to, the label will display the contents of that cell. (if you find you've accidentally created a text box, that means you hadn't selected one label: the text box will correctly display the cell value, but won't move around properly to follow the series marker when you change the graph) Repeat for each label, and you've now got customized data labels that can display anything, instead of only the value or category of the series marker. Excel should long ago have included an option to select a cell range to create all the labels at once (Lotus 123 had this back in the 1980s!), but fortunately there are little add-ins distributed free by friendly Excel experts if you have a heavy load of such labels. Whether you want to do it manually or install one of the add-ins depends on how much of this kind of thing you expect to do. -- 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. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
Hi again - thanks for your detailed message. So, basically, if I understand
correctly, because my data is "scaleable", such as cel/far temperature, it isn't possible to have markers that correspond on the two axis. The only way to reflect the value for a certain amount is to add the labels as per your instructions. Just want to make sure I'm totally clear on this. Thanks!! -- Thanks! Dee "Del Cotter" wrote: On Thu, 30 Aug 2007, in microsoft.public.excel.charting, dee said: I can arrange the data in any way, so that isn't a problem. Del mentioned adding labels manually, which wouldn't be a problem, as I don't have many items to chart. I'm not sure how to do this, though. If you create some labels with one of the automatic options Excel provides (value or category), you can then select the label series by single-clicking on one of the labels. If you then carefully single-click again on a label (not too fast; you don't want to double-click), a single label out of the whole range is selected. Now if you go up to the formula bar above the chart and spreadsheet, and type in an equals sign followed by a cell reference, or type an equals sign and then navigate to the cell you want that label to refer to, the label will display the contents of that cell. (if you find you've accidentally created a text box, that means you hadn't selected one label: the text box will correctly display the cell value, but won't move around properly to follow the series marker when you change the graph) Repeat for each label, and you've now got customized data labels that can display anything, instead of only the value or category of the series marker. Excel should long ago have included an option to select a cell range to create all the labels at once (Lotus 123 had this back in the 1980s!), but fortunately there are little add-ins distributed free by friendly Excel experts if you have a heavy load of such labels. Whether you want to do it manually or install one of the add-ins depends on how much of this kind of thing you expect to do. -- 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. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Combination Chart Problem
On Thu, 30 Aug 2007, in microsoft.public.excel.charting,
dee said: Hi again - thanks for your detailed message. So, basically, if I understand correctly, because my data is "scaleable", such as cel/far temperature, it isn't possible to have markers that correspond on the two axis. The only way to reflect the value for a certain amount is to add the labels as per your instructions. Basically, your data has two completely independent dimensions to it, so it's got two degrees of freedom. In any year the data could have a certain quantity, and then a certain price, and the quantity and the price are free to be any value independently of each other. So if you're going to represent year, quantity and price all in one line on a two-dimensional surface, you've got to make the most of the two dimensions by letting one of them be the quantity, and the other be the price, in a graph of *orthogonal coordinates* (orthogonal being a fancy word for "at right angles to each other". (Temperature doesn't have this problem because every centigrade temperature forces a fahrenheit temperature, and vice versa: it's only got one degree of freedom, leaving you with a spare dimension to display your years in) If you want price and quantity to be in *parallel coordinates*, you'll need to have two lines, one of them quantity against year, and the other one price against year. If you want price and quantity in one line, you have to drop the year (or somehow twist the line into a third dimension). Labelling the point with their year is still possible though, so that's good. So you have two graphs to choose from, an Excel Line Chart type with two lines, and an Excel XY (Scatter) Chart type with one line, neatly labelled so you can see the years. Why not try both, and see which your audience prefers. Sometimes it's good to show the same data two different ways, as it brings out different features. -- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination of stock chart and line chart | Charts and Charting in Excel | |||
Combination Chart Problem | Charts and Charting in Excel | |||
Combination of Validation List and VLOOKUP Problem | Excel Worksheet Functions | |||
How to lock combination chart in pivot chart? | Charts and Charting in Excel | |||
Combination bar and line problem | Charts and Charting in Excel |