• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to set X and Y Intercept to zero in graphs

K

Kamarlon

New Member
Hi. My challenge is that I have a dynamic graph that plots positive and negative numbers. I want to force the graph to have 0 as the point of origin (whether its plotting positive or negative numbers), without having to use VBA. I get the impression that this is impossible. I cannot tell it that the minimum value is 0, as this wont be true when it's plotting the negative numbers (in that case 0 is the highest number).
 
Kamarlon


Firstly, Welcome to the Chandoo.org Forums


Am I correct that your data is always in just 1 quadrant and isn't in 2 or more at the same time ?
 
Thank you for the warm welcome. The data is in different columns (if that's what you mean by quadrant). I had a graph for each variable but it was overwhelming. (I have 20 variables to plot). A suggestion was made to have one graph where the user would select which variable they want to see, do that's what I did.
 
Quadrant as in Upper Left, Upper Right, Lower Left, Lower Right, corners of a circle based around 0,0
 
See file here https://skydrive.live.com/?cid=16d0ed9ee1cb5b75&sc=documents&id=16D0ED9EE1CB5B75!102

I'm new to this, so let me know if the link doesn't work.
 
I think this link should work.


https://skydrive.live.com/redir.aspx?cid=16d0ed9ee1cb5b75&resid=16D0ED9EE1CB5B75!102&authkey=LyNV5hQzjSk%24


The graph is on the worksheet named "Dashboard" It plots actual and target figures
 
Are you referring to the Bullet Chart at approx J0:K26 ?


Under what conditions isn't it working ?
 
For instance, when you select Income Kingston Beach, the graph starts at 8 million, instead of zero, this gives the impression that the target is far from the actual.


If you change the numbers to negative (as I just did) the graph starts at -8 million instead of zero.


I want the graph to always start at zero whether its plotting positive or negative numbers.


The dataset I have at work consists of several negative numbers, the file I sent the link for was generated using positive random numbers. It's my fault I should have generated some negative random numbers too. Please forgive my oversight.
 
Kamarlon


Lets solve this by adding some dummy data to the chart


Select the chart

Right Click Seelct Data

Add

X Axis ='Dashboard for review.xlsx'!CurrentMonth

Y Axis ={0}

Save

Now select the chart use arrows to seelct the new data series

Change it's Marker to None

Delete the legend etc

enjoy
 
Wow. You most definitely are an excel ninja :)Thank you, thank you, thank you. I have been battling with this for months. It works beautifully. Thank you again.
 
Good thing here is that it works for positives, negatives and doesn't affect the chart if there mixed positive and negative
 
You must log in or register to reply here.
Back
Top