Dynamic Excel Graph Problem (Axis bounds)

[dinesababu1]

Hello Everyone,

There is a dashboard I update every month for which I can use some help better automating.

I only need the green column labeled "Prior Year" to appear on certain "forecast" months (hence for the example below the months November and December...and next month I will roll forward to just display in December)

Every month I have been clearing out the formula for this green column so it appears only in the forecast months.

I would like to automate the formula for this column so it is truly blank or nonexistent for all months except the forecast months.

I tried combining IF formulas with NA() formula or "" however this effects the axis my graph. (in the example below I use "IF(AG3="FRCST",AG12,NA())")

It seems that if there are any formulas at all the graph interprets it as data and starts the axis at 0 rather than auto adjusting the axis based on the data.

Is there anyway to solve this issue prefably without using VBA?

..... If there is truly no way to solve it without using VBA than can someone tell me how to use VBA to solve this issue. Thank you everyone.

 

[GraH]

Hello,

One could argue the second chart is better since the y-axis starts at 0. In the first chart you exaggerate the differences because you start at 34,000.
Visually NOV is about half of JAN (50%), while in reality the difference is only 5%.

Since you can't set the bounds via a cell value/formula, you need to set it manually. Or use VBA.

Or maybe an alternative chart can be used, that takes JAN as base 0 (% or Amount). Or the deviation of your budget line (so budget is 0). But I could not derive those values from your picture.
NOV is 5% less then JAN and difference is 2K, clearly depicted in the chart.

 

[dinesababu1]

Thank you for response but I would prefer not to change the graph.

Is there any formula I can use so the graph ignores the data and the axis adjusts? (similar to NA(), or maybe even a format change?)

If there isn't any formula that can do what I need, may I have a VBA code that can help?

 

[GraH]

I can't help with VBA. These links may ...

Charts - auto format the scale (x-axis) by cell ref?

I tried a search, but cannot get this to work today. Is there a way to change the format of an axis in a chart by referencing a specific cell? By this, can the MAX or MIN on the scale be referenced to a specific cell?

www.mrexcel.com

How to set chart axis based on a cell value

The chart axis min and max settings are fixed values. This posts looks at a automated method to set chart axis based on a cell value.

exceloffthegrid.com