I am running a macro to reset the scales of a chart. I'm running into a problem when I have a negative number. I would like to have the following major units on the secondary axis(100%, 80%, 60%, 40%, 20%, and 0% or a negative % if applicable). As I have it now, the negative number causes the major units to adjust down to 97%, 77%, 57%, 37%, 17%, and -3%. is there a way to set the major units separately or another way to change this with VBA. Attached is the file with macro.
You will need to round your values to the nearest 20%
K8: =CEILING(MAX(F2:F11),0.2)
N8: =IF(MIN(E2:E11)<0,-CEILING(ABS(MIN(E2:E11)/E12),0.2),0)
Or do it to 10% and adjust the Major value to 10%
When I put those formulas in, my line graph now only aligns with the secondary axis and not the primary axis. And the bar graphs don't align with the secondary axis. Any way to resolve that? The minimum seems to be throwing this off when it's set at -20%
If you want it to align then have a look at this page.
http://peltiertech.com/Excel/Charts/AlignXon2Ys.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks