Can't get the x-axis to cross at value 0 for both y-axes
Hello -
I am working on an Excel chart with both a primary and secondary y-axis and only one x-axis. The primary has positive and negative values. The secondary has all positive values. I am using the secondary as a rank (by selecting the box to invert values so the highest rank has a value of 1 and appears at the top of the secondary y-axis, where rank 25 is the lowest possible rank and appears at the bottom of the secondary y-axis), in theory where the x and y axes meet. The x-axis is different points in time. So this chart shows trends over time. The issue I am having is the primary x-axis crosses the primary y-axis at 0 (y-axis value), which is what I want. However, the primary x-axis crosses the secondary y-axis at a value that is not the lowest value on the secondary y-axis. In this case, since I am using it as a rank, it is not crossing at the lowest rank. I am not showing the secondary x-axis associated with rank. I don't have this issue if all of the values on the primary y-axis are positive. In other words, the primary x-axis crosses at 0 on the primary y-axis and at the lowest rank on the secondary axis. My question is, is there a way to fix my problem? Is there an option where I can set the value where the primary x-axis crosses a value on the secondary y-axis? I know I can set the value for Primary x-axis to primary y-axis - and do the same for the secondary x and y axes, but I'm not aware of a way to cross this primary - secondary boundary. I imagine the graph's axes looking like a backwards 4 - the primary y-axis goes above and below the primary x-axis. The primary x-axis is approximately in the middle. The secondary y-axis is as high as the primary y-axis but only goes down to the x-axis (i.e., it does not extend below the x-axis). Is this possible? Something like this: | | |_______| | | Thanks! - Derek |
Can't get the x-axis to cross at value 0 for both y-axes
You have to fix the secondary axis so it goes as far negative as a
proportion of its full scale as the primary axis does. You can do it manually or using a VBA procedu http://peltiertech.com/Excel/Charts/AlignXon2Ys.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Derek" wrote in message ... Hello - I am working on an Excel chart with both a primary and secondary y-axis and only one x-axis. The primary has positive and negative values. The secondary has all positive values. I am using the secondary as a rank (by selecting the box to invert values so the highest rank has a value of 1 and appears at the top of the secondary y-axis, where rank 25 is the lowest possible rank and appears at the bottom of the secondary y-axis), in theory where the x and y axes meet. The x-axis is different points in time. So this chart shows trends over time. The issue I am having is the primary x-axis crosses the primary y-axis at 0 (y-axis value), which is what I want. However, the primary x-axis crosses the secondary y-axis at a value that is not the lowest value on the secondary y-axis. In this case, since I am using it as a rank, it is not crossing at the lowest rank. I am not showing the secondary x-axis associated with rank. I don't have this issue if all of the values on the primary y-axis are positive. In other words, the primary x-axis crosses at 0 on the primary y-axis and at the lowest rank on the secondary axis. My question is, is there a way to fix my problem? Is there an option where I can set the value where the primary x-axis crosses a value on the secondary y-axis? I know I can set the value for Primary x-axis to primary y-axis - and do the same for the secondary x and y axes, but I'm not aware of a way to cross this primary - secondary boundary. I imagine the graph's axes looking like a backwards 4 - the primary y-axis goes above and below the primary x-axis. The primary x-axis is approximately in the middle. The secondary y-axis is as high as the primary y-axis but only goes down to the x-axis (i.e., it does not extend below the x-axis). Is this possible? Something like this: | | |_______| | | Thanks! - Derek |
Can't get the x-axis to cross at value 0 for both y-axes
Hi Jon -
Thanks for the quick response. I understand what you are saying in terms of having the primary and secondary y-axes in proportion to each other, but I think the wrinkle in my chart is I am using the secondary y-axis as a rank with the lowest rank at the bottom where the y-axis meets the x-axis. I completely agree with your suggestion if I were dealing with numbers going in the same direction on both y-axes, but that is not the case here, since you can't have a negative rank and I have rank in descending order with $ values in ascending order. So my envisioned chart looks like this with $ as a bar graph (primary axis) and rank as a line graph (secondary axis). I want the secondary y-axis to cross / stop at the primary x-axis at x-axis value $0. If I have the rank proportions to the $ amounts, it implies a $0 is not the bottom rank, which is not always the case in my data set. I don't want the primary x-axis to imply $0 is not ranked the lowest. $100 | | Rank 1 | | | | $0 |_________|Rank 25 | | ($100)| Q1 Q2 Q3 - Derek ----------------------- "Jon Peltier" wrote: You have to fix the secondary axis so it goes as far negative as a proportion of its full scale as the primary axis does. You can do it manually or using a VBA procedu http://peltiertech.com/Excel/Charts/AlignXon2Ys.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Derek" wrote in message ... Hello - I am working on an Excel chart with both a primary and secondary y-axis and only one x-axis. The primary has positive and negative values. The secondary has all positive values. I am using the secondary as a rank (by selecting the box to invert values so the highest rank has a value of 1 and appears at the top of the secondary y-axis, where rank 25 is the lowest possible rank and appears at the bottom of the secondary y-axis), in theory where the x and y axes meet. The x-axis is different points in time. So this chart shows trends over time. The issue I am having is the primary x-axis crosses the primary y-axis at 0 (y-axis value), which is what I want. However, the primary x-axis crosses the secondary y-axis at a value that is not the lowest value on the secondary y-axis. In this case, since I am using it as a rank, it is not crossing at the lowest rank. I am not showing the secondary x-axis associated with rank. I don't have this issue if all of the values on the primary y-axis are positive. In other words, the primary x-axis crosses at 0 on the primary y-axis and at the lowest rank on the secondary axis. My question is, is there a way to fix my problem? Is there an option where I can set the value where the primary x-axis crosses a value on the secondary y-axis? I know I can set the value for Primary x-axis to primary y-axis - and do the same for the secondary x and y axes, but I'm not aware of a way to cross this primary - secondary boundary. I imagine the graph's axes looking like a backwards 4 - the primary y-axis goes above and below the primary x-axis. The primary x-axis is approximately in the middle. The secondary y-axis is as high as the primary y-axis but only goes down to the x-axis (i.e., it does not extend below the x-axis). Is this possible? Something like this: | | |_______| | | Thanks! - Derek |
Can't get the x-axis to cross at value 0 for both y-axes
I didn't say plot numbers along the negative secondary Y axis, I was telling
you how to align the 0 point of each Y axis. If you don't want the negative part of the axis to show, don't display any part of the axis (format: no line), and add an XY series to the chart to serve as a fake axis: http://peltiertech.com/Excel/Charts/ArbitraryAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Derek" wrote in message ... Hi Jon - Thanks for the quick response. I understand what you are saying in terms of having the primary and secondary y-axes in proportion to each other, but I think the wrinkle in my chart is I am using the secondary y-axis as a rank with the lowest rank at the bottom where the y-axis meets the x-axis. I completely agree with your suggestion if I were dealing with numbers going in the same direction on both y-axes, but that is not the case here, since you can't have a negative rank and I have rank in descending order with $ values in ascending order. So my envisioned chart looks like this with $ as a bar graph (primary axis) and rank as a line graph (secondary axis). I want the secondary y-axis to cross / stop at the primary x-axis at x-axis value $0. If I have the rank proportions to the $ amounts, it implies a $0 is not the bottom rank, which is not always the case in my data set. I don't want the primary x-axis to imply $0 is not ranked the lowest. $100 | | Rank 1 | | | | $0 |_________|Rank 25 | | ($100)| Q1 Q2 Q3 - Derek ----------------------- "Jon Peltier" wrote: You have to fix the secondary axis so it goes as far negative as a proportion of its full scale as the primary axis does. You can do it manually or using a VBA procedu http://peltiertech.com/Excel/Charts/AlignXon2Ys.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Derek" wrote in message ... Hello - I am working on an Excel chart with both a primary and secondary y-axis and only one x-axis. The primary has positive and negative values. The secondary has all positive values. I am using the secondary as a rank (by selecting the box to invert values so the highest rank has a value of 1 and appears at the top of the secondary y-axis, where rank 25 is the lowest possible rank and appears at the bottom of the secondary y-axis), in theory where the x and y axes meet. The x-axis is different points in time. So this chart shows trends over time. The issue I am having is the primary x-axis crosses the primary y-axis at 0 (y-axis value), which is what I want. However, the primary x-axis crosses the secondary y-axis at a value that is not the lowest value on the secondary y-axis. In this case, since I am using it as a rank, it is not crossing at the lowest rank. I am not showing the secondary x-axis associated with rank. I don't have this issue if all of the values on the primary y-axis are positive. In other words, the primary x-axis crosses at 0 on the primary y-axis and at the lowest rank on the secondary axis. My question is, is there a way to fix my problem? Is there an option where I can set the value where the primary x-axis crosses a value on the secondary y-axis? I know I can set the value for Primary x-axis to primary y-axis - and do the same for the secondary x and y axes, but I'm not aware of a way to cross this primary - secondary boundary. I imagine the graph's axes looking like a backwards 4 - the primary y-axis goes above and below the primary x-axis. The primary x-axis is approximately in the middle. The secondary y-axis is as high as the primary y-axis but only goes down to the x-axis (i.e., it does not extend below the x-axis). Is this possible? Something like this: | | |_______| | | Thanks! - Derek |
All times are GMT +1. The time now is 06:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com