dual axes charts, 2 questions....
i am trying to chart 2 different sets of data:
1. average temperature for various cities in both celsius and fahrenheit. PROBLEM: if i have celsius on the primary x axis and the fahrenheit on a secondary x axis it gives me 2 columns. i want one column per city which will give me the temp in celsius on the primary x axis and the corresponding fahrenheit temperature on secondary y axis (conversion of celsius to fahrenheit done with formula of celsius temp*9/5+32). can this be done? 2. i am charting rainfall in 5 cities over 12 months but the data range is very wide. one of the cities has 10x more rain than 3 of the other cities. this makes it almost impossible to read the chart. if i plot that city on a secondary axis, when someone else looks at my chart, how can they know what city corresponds to primary x axis values and what corresponds to secondary x axis values? it is very confusing. fern |
dual axes charts, 2 questions....
1. It is hard to get the axes to line up correctly. You can instead just use
Celsius, and make a dummy axis for the Fahrenheit. See dummy axes: http://peltiertech.com/Excel/Charts/...tml#AxisScales Say the Celsius temperatures are 0°C to 50°C. That's 32°F and 132°F, not convenient values for a scale. But 40°F to 130°F is good. Set up a data range like this: 5.5 4.44°C 40°F 5.5 10.00°C 50°F 5.5 15.56°C 60°F 5.5 21.11°C 70°F 5.5 26.67°C 80°F 5.5 32.22°C 90°F 5.5 37.78°C 100°F 5.5 43.33°C 110°F 5.5 48.89°C 120°F The first column is just a convenient number that comes out to the right edge of the chart's X axis. The right hand column are the values in °F for the tick marks, and the middle column is the corresponding °C values. To get the '°C' and '°F' to appear, use custom number formats of 0°C and 0°F for the cells; that's zero plus the degree sign plus the letter; to get the degree sign, hold Alt while pressing 0176 on the numeric keypad. Add a series to the chart using the first two columns. Change the added series to an XY series (if it isn't already), and format it using black crosses, which look somewhat like tick marks. Use one of these handy Excel utilities to add the labels from the third column to the added series as data labels, aligning them to the right of the chart: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com 2. Does this technique help: http://peltiertech.com/Excel/Charts/BrokenYAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "chasida" wrote in message ... i am trying to chart 2 different sets of data: 1. average temperature for various cities in both celsius and fahrenheit. PROBLEM: if i have celsius on the primary x axis and the fahrenheit on a secondary x axis it gives me 2 columns. i want one column per city which will give me the temp in celsius on the primary x axis and the corresponding fahrenheit temperature on secondary y axis (conversion of celsius to fahrenheit done with formula of celsius temp*9/5+32). can this be done? 2. i am charting rainfall in 5 cities over 12 months but the data range is very wide. one of the cities has 10x more rain than 3 of the other cities. this makes it almost impossible to read the chart. if i plot that city on a secondary axis, when someone else looks at my chart, how can they know what city corresponds to primary x axis values and what corresponds to secondary x axis values? it is very confusing. fern |
dual axes charts, 2 questions....
thanx for your help. i have been looking at the broken y axis but can't
figure out how to do it. i guess i am dense. thank you though. "Jon Peltier" wrote: 1. It is hard to get the axes to line up correctly. You can instead just use Celsius, and make a dummy axis for the Fahrenheit. See dummy axes: http://peltiertech.com/Excel/Charts/...tml#AxisScales Say the Celsius temperatures are 0°C to 50°C. That's 32°F and 132°F, not convenient values for a scale. But 40°F to 130°F is good. Set up a data range like this: 5.5 4.44°C 40°F 5.5 10.00°C 50°F 5.5 15.56°C 60°F 5.5 21.11°C 70°F 5.5 26.67°C 80°F 5.5 32.22°C 90°F 5.5 37.78°C 100°F 5.5 43.33°C 110°F 5.5 48.89°C 120°F The first column is just a convenient number that comes out to the right edge of the chart's X axis. The right hand column are the values in °F for the tick marks, and the middle column is the corresponding °C values. To get the '°C' and '°F' to appear, use custom number formats of 0°C and 0°F for the cells; that's zero plus the degree sign plus the letter; to get the degree sign, hold Alt while pressing 0176 on the numeric keypad. Add a series to the chart using the first two columns. Change the added series to an XY series (if it isn't already), and format it using black crosses, which look somewhat like tick marks. Use one of these handy Excel utilities to add the labels from the third column to the added series as data labels, aligning them to the right of the chart: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com 2. Does this technique help: http://peltiertech.com/Excel/Charts/BrokenYAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "chasida" wrote in message ... i am trying to chart 2 different sets of data: 1. average temperature for various cities in both celsius and fahrenheit. PROBLEM: if i have celsius on the primary x axis and the fahrenheit on a secondary x axis it gives me 2 columns. i want one column per city which will give me the temp in celsius on the primary x axis and the corresponding fahrenheit temperature on secondary y axis (conversion of celsius to fahrenheit done with formula of celsius temp*9/5+32). can this be done? 2. i am charting rainfall in 5 cities over 12 months but the data range is very wide. one of the cities has 10x more rain than 3 of the other cities. this makes it almost impossible to read the chart. if i plot that city on a secondary axis, when someone else looks at my chart, how can they know what city corresponds to primary x axis values and what corresponds to secondary x axis values? it is very confusing. fern |
dual axes charts, 2 questions....
It's a little complicated. Just remember it's using some helper XY series to
put the labels you need where you need them, instead of Excel's default labels. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "chasida" wrote in message ... thanx for your help. i have been looking at the broken y axis but can't figure out how to do it. i guess i am dense. thank you though. "Jon Peltier" wrote: 1. It is hard to get the axes to line up correctly. You can instead just use Celsius, and make a dummy axis for the Fahrenheit. See dummy axes: http://peltiertech.com/Excel/Charts/...tml#AxisScales Say the Celsius temperatures are 0°C to 50°C. That's 32°F and 132°F, not convenient values for a scale. But 40°F to 130°F is good. Set up a data range like this: 5.5 4.44°C 40°F 5.5 10.00°C 50°F 5.5 15.56°C 60°F 5.5 21.11°C 70°F 5.5 26.67°C 80°F 5.5 32.22°C 90°F 5.5 37.78°C 100°F 5.5 43.33°C 110°F 5.5 48.89°C 120°F The first column is just a convenient number that comes out to the right edge of the chart's X axis. The right hand column are the values in °F for the tick marks, and the middle column is the corresponding °C values. To get the '°C' and '°F' to appear, use custom number formats of 0°C and 0°F for the cells; that's zero plus the degree sign plus the letter; to get the degree sign, hold Alt while pressing 0176 on the numeric keypad. Add a series to the chart using the first two columns. Change the added series to an XY series (if it isn't already), and format it using black crosses, which look somewhat like tick marks. Use one of these handy Excel utilities to add the labels from the third column to the added series as data labels, aligning them to the right of the chart: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com 2. Does this technique help: http://peltiertech.com/Excel/Charts/BrokenYAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "chasida" wrote in message ... i am trying to chart 2 different sets of data: 1. average temperature for various cities in both celsius and fahrenheit. PROBLEM: if i have celsius on the primary x axis and the fahrenheit on a secondary x axis it gives me 2 columns. i want one column per city which will give me the temp in celsius on the primary x axis and the corresponding fahrenheit temperature on secondary y axis (conversion of celsius to fahrenheit done with formula of celsius temp*9/5+32). can this be done? 2. i am charting rainfall in 5 cities over 12 months but the data range is very wide. one of the cities has 10x more rain than 3 of the other cities. this makes it almost impossible to read the chart. if i plot that city on a secondary axis, when someone else looks at my chart, how can they know what city corresponds to primary x axis values and what corresponds to secondary x axis values? it is very confusing. fern |
dual axes charts, 2 questions....
Unfortunately this requires a bit more maintenance. The two vertical axes
must be aligned, then kept in alignment if the data changes, whereas the dummy axis can be made with points covering a broader range than at first needed, and points outside the range will not appear. Also you are forced (in most cases) to use irregular min or max to make it work (e.g., 32 and 92°F) instead of natural multiples of 5 or 10. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... I know Jon has already given you the steps to align two axis but a worked out example using Centigrade and Farenheit is at Single Chart Dual Axis http://www.tushar-mehta.com/excel/ch...0graph%20dual% 20axis.htm As far as the other issue goes, you can use color and text to indicate which series goes with which chart. For example, if you have only one series on each axis, simply color the axis and the corresponding series the same color. If you have multiple series on each axis, add a textbox that tells the reader which series goes on which axis. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... i am trying to chart 2 different sets of data: 1. average temperature for various cities in both celsius and fahrenheit. PROBLEM: if i have celsius on the primary x axis and the fahrenheit on a secondary x axis it gives me 2 columns. i want one column per city which will give me the temp in celsius on the primary x axis and the corresponding fahrenheit temperature on secondary y axis (conversion of celsius to fahrenheit done with formula of celsius temp*9/5+32). can this be done? 2. i am charting rainfall in 5 cities over 12 months but the data range is very wide. one of the cities has 10x more rain than 3 of the other cities. this makes it almost impossible to read the chart. if i plot that city on a secondary axis, when someone else looks at my chart, how can they know what city corresponds to primary x axis values and what corresponds to secondary x axis values? it is very confusing. fern |
dual axes charts, 2 questions....
Jon,
Each approach has its limitations. After all, each is working around limitations in XL and nothing will be as good as having XL do the needful. In the case of the dummy axis, the 3rd column labels don't update as the data range expands. I've been burnt by *that* on more than one occasion. In the case of using a secondary axis, it does need realignment. So, six of one... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Unfortunately this requires a bit more maintenance. The two vertical axes must be aligned, then kept in alignment if the data changes, whereas the dummy axis can be made with points covering a broader range than at first needed, and points outside the range will not appear. Also you are forced (in most cases) to use irregular min or max to make it work (e.g., 32 and 92°F) instead of natural multiples of 5 or 10. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... I know Jon has already given you the steps to align two axis but a worked out example using Centigrade and Farenheit is at Single Chart Dual Axis http://www.tushar-mehta.com/excel/ch...0graph%20dual% 20axis.htm As far as the other issue goes, you can use color and text to indicate which series goes with which chart. For example, if you have only one series on each axis, simply color the axis and the corresponding series the same color. If you have multiple series on each axis, add a textbox that tells the reader which series goes on which axis. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... i am trying to chart 2 different sets of data: 1. average temperature for various cities in both celsius and fahrenheit. PROBLEM: if i have celsius on the primary x axis and the fahrenheit on a secondary x axis it gives me 2 columns. i want one column per city which will give me the temp in celsius on the primary x axis and the corresponding fahrenheit temperature on secondary y axis (conversion of celsius to fahrenheit done with formula of celsius temp*9/5+32). can this be done? 2. i am charting rainfall in 5 cities over 12 months but the data range is very wide. one of the cities has 10x more rain than 3 of the other cities. this makes it almost impossible to read the chart. if i plot that city on a secondary axis, when someone else looks at my chart, how can they know what city corresponds to primary x axis values and what corresponds to secondary x axis values? it is very confusing. fern |
dual axes charts, 2 questions....
Half dozen of the other. I've been very successful with the dummy axis
approach. I will define a range pseudo-secondary Y axis values calculated by formulas similar to those I use to determine optimum axis scale parameters (min/max/major). I define more than I need, say 20 labels for an axis which I think will use 12 or 15 labels, and the formulas return #N/A if they are not needed. I temporarily put non-#N/A values in the 20 cells while I apply 20 labels (cell links, actually) to the 20 data points; Excel remembers the data label links as long as they were defined one time. This becomes a dynamic, self-contained, non-VBA approach to this issue. Obviously a full discussion is light-years beyond a newsgroup thread, but I have gotten to the point where I can set up one of these axes in 15 minutes, then forget it. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... Jon, Each approach has its limitations. After all, each is working around limitations in XL and nothing will be as good as having XL do the needful. In the case of the dummy axis, the 3rd column labels don't update as the data range expands. I've been burnt by *that* on more than one occasion. In the case of using a secondary axis, it does need realignment. So, six of one... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Unfortunately this requires a bit more maintenance. The two vertical axes must be aligned, then kept in alignment if the data changes, whereas the dummy axis can be made with points covering a broader range than at first needed, and points outside the range will not appear. Also you are forced (in most cases) to use irregular min or max to make it work (e.g., 32 and 92°F) instead of natural multiples of 5 or 10. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... I know Jon has already given you the steps to align two axis but a worked out example using Centigrade and Farenheit is at Single Chart Dual Axis http://www.tushar-mehta.com/excel/ch...0graph%20dual% 20axis.htm As far as the other issue goes, you can use color and text to indicate which series goes with which chart. For example, if you have only one series on each axis, simply color the axis and the corresponding series the same color. If you have multiple series on each axis, add a textbox that tells the reader which series goes on which axis. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... i am trying to chart 2 different sets of data: 1. average temperature for various cities in both celsius and fahrenheit. PROBLEM: if i have celsius on the primary x axis and the fahrenheit on a secondary x axis it gives me 2 columns. i want one column per city which will give me the temp in celsius on the primary x axis and the corresponding fahrenheit temperature on secondary y axis (conversion of celsius to fahrenheit done with formula of celsius temp*9/5+32). can this be done? 2. i am charting rainfall in 5 cities over 12 months but the data range is very wide. one of the cities has 10x more rain than 3 of the other cities. this makes it almost impossible to read the chart. if i plot that city on a secondary axis, when someone else looks at my chart, how can they know what city corresponds to primary x axis values and what corresponds to secondary x axis values? it is very confusing. fern |
All times are GMT +1. The time now is 02:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com