I am making some charts for a customer that depict 4 different values each month. I have to use the 2 dimensionel bar chart as the format. The fourth value is a percentage between 99 and 100 and does not show well because one of the other value goes to 370. I tried setting this value to the right hand axis. It numbers the axis fine for the percentage range of the numbers but, the bar for this value becomes twice as wide, jumps to the middle, and covers the other bars totally covering one of them. Can this actually be done?
I have attached an example with the before & after graph. Network availability is the value I am tring to get to reference the right axis, the light green bar. See how it gets distorted?
Man, I tried to get Excel to do what you wanted, but I failed. Excel always centers the one data series that is on the right axis, and scales it to fit between the tic marks, which is why it ends up so fat and overlaps the other three categories that are on the left axis.
If you make the "Availability" category a line instead of a column, you can see the other three columns behind it. I know it's not what you want, but...well, you know the Rolling Stones song.
If you mess with the "gap" and "overlap" settings of the data categories (double-click on a column and select the "Options" tab in the dialog that pops up) you can get the columns set up so you can at least see the three set to the left axis behind the Availability column (see attachment). In my example, the columns set to the left axis have an overlap of 0 and a gap of 20, while the Availability column has an overlap of 0 and a gap of 500. Again, not exactly pretty, but....oh well.
I'll mess around with it a bit more and see if I can come up with a real solution. Sorry!
Thanks Scary, I played around today and got the same results you did. I went to the two guys in financial analysis who are the Excel gurus, and they couldn't do it either. I might just have to go with the first chart. I also thought about the line thing, but the customer has given all vendors the example they want followed. Problem was in their examples the other 3 values were less than 10, so the availabilty running to the top looked ok.
I seem to remember doing this with Harvard Graphics years ago, but maybe I was using bars & a line, like a Paretto chart. I know I've seen graphs with two axis of different values. One thing I didn't try was inserting spare blank columns to see if that would allow proper spacing.
Just a thought. Is it permissible for your client for you to show the 370 value as a "divided by 10" value? I.E. for the purposes of display, set a column (or row) as the actual value/10 so that the max is only 37 on the graph, and the legend can be labeled to indicate that the actual value for that parameter is 10x that which is shown. Than way, the 99 to 100 value will be the max of the chart.
Reply With Quote
Bookmarks