• 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.

bar & line chart: issues with displaying negatives, updating data

arobbins10

New Member
This is sort of a difficult question to type out here, so it might be easier to just check my attachment as I included charts with examples of what I'm trying to do.

Basically, I have a set of data that will be updating frequently: 5 products, Absolute $ Change, and % $ Change. I want Absolute $ Change displayed as a bar, and % Change displayed as a line - so the user can see, in one glance, if by how much volume went up or down, and what is this as a %.

The issue is, when putting the % change on the secondary axis and changing to a line chart, the chart doesn't hold 0% as being in line with 0 on the primary axis. E.g., 0% might be set to halfway up the secondary axis, so a -10% is above the line for the labels, so to the user it looks like there was positive % growth (when hiding the axis from view). I want 0% to be "planted" right at the 0 line for the primary axis - i.e. right in line with the labels. So a negative % change will be below this line, and all positive % changes go above.

The only way I've found to do the above is to manually change the secondary axis so that the min/max ratio is the same as the primary axis. E.g., if the primary axis min/max is -50 and 300, the secondary axis needs to be changed to -2 and 12, maintaining the -1:6 ratio.

This won't work because the data will be updated every week or so, and there will be at least a dozen of these charts, so I would have to go in an manually change the secondary axis each time to match the new ratio of the primary axis.

Hopefully that makes sense - and I'm 100% open to suggestions or even improvements on how to display this data. The important things are: 1) the chart needs to update automatically and correctly display the data with each change to the raw data, and 2) in one chart, be able to quickly and intuitively convey Absolute $ Change and % Change.

Thanks for any and all help!
 

Attachments

  • Excel charting question.xlsx
    17 KB · Views: 1
Thanks for the response! Definitely close to what I'm looking for with one small hiccup - if the data changes and, say, cell D2 goes from 280 to 400, because the macro fixes the min/max of the primary axis also, now the bar is appearing off of the chart. Perhaps if I just did this?

Button 1: macro that goes into each chart and sets the min/max to be automatic.
Button 2: runs the macro you sent over to readjust the Y axis and re-fix them align to Y=0

So anytime the data updated, all the charts would correct with 2 button clicks. Makes sense to me unless you see some reason it wouldn't work :)

Thanks again!
 
Hi, arobbins!

For which I can see, you have a main procedure, AlignY, which receives one the 4 parameters possible:
1: Y1 (PRI) MIN
2: Y1 (PRI) MAX
3: Y2 (SEC) MIN
4: Y2 (SEC) MAX
with 4 helper macros to call it:
AlignY_<axis><value>, where <axis> is Primary/Secondary and <value> is Minimum/Maximum.

As in any simple mathematical proportionality relation, a/b=c/d, given 3 of the 4 values, you could easily calculate the 4th one. But it's up to you to choose which will you change. Let us put in other terms:

Ymin(pri)/Ymax(pri) = Ymin(sec)/Ymax(sec)

I'd start keeping YPriMin and YPriMax fixed, also YSecMax, and calculate how much should YSecMin be (as in your example where the main issue was at the bottom right of the chart).

And the resulting/chosen macro should be manually run each time your data changes or automatically, calling it for example from the worksheet change event of the worksheet where the rough data is stored.

Regards!
 
Back
Top