Expanding Graph Axis?

[Mido Allam]

Hi my name is Mido,

My boss as just asked of me to build a graph on excel that shows the relationship between Sales and Cost of goods sold to show profitability. Seems easy enough but the problem is he wants the graph to be able to shift while continuing to show values. Basically he wants that when you extend the axis either y or x you can see a difference in the value and the graph line itself. is that even possible? please i really need your help. Thank you

 

[Luke M]

Hi Mido, and welcome to the forum!

Can you elaborate on what you mean by "extend the axis"? In XL, you can change the upper and lower limits that the chart displays. If you change these min/max, then the value on the chart would physically change position so that it still aligns to the axis. Is this what you're wanting? If so, we don't need to do anything other than figure out how he wants the chart axis changed.

If that's not it, can you provide an example perhaps? Either through description, or even a picture? You can upload files up to 1 MB in size when you post stuff here.

 

[Mido Allam]

Hi Mido, and welcome to the forum!

Can you elaborate on what you mean by "extend the axis"? In XL, you can change the upper and lower limits that the chart displays. If you change these min/max, then the value on the chart would physically change position so that it still aligns to the axis. Is this what you're wanting? If so, we don't need to do anything other than figure out how he wants the chart axis changed.

If that's not it, can you provide an example perhaps? Either through description, or even a picture? You can upload files up to 1 MB in size when you post stuff here.

Well Luke thanks for your prompt response ,
from what i understood, he wants it so u can easily drag the axis left or right, up or down and consequently causing the actual graph values to change. So that means you would see a change in the scatter pattern or line so it would be like a forecast of some kind, so if i increase the COGs ( x-axis) the profit would decrease, vice versa if I increase. I've only been able to make a dynamic chart that can be updated with fixed values with a forecast function, but that's not what he wanted.

thanks

 

[Luke M]

Wow...I give you my sympathies, as it sounds like you may have a case of your boss not understanding how charts work. But, we'll see what we can do.

The axis on charts don't affect the values plotted, nor can they in XL. It's like changing the resolution on your monitor. It changes where you physically see something, but a date point at (2,1) would still be at (2,1), no matter how you change the axis.

Now, what he is sort of describing sounds like a basic f(x) function, where you have some sort of input and output. However, everything in an XL that is physically part of the chart (and not a button or something near it) is an output from somewhere else. With the example of COGs, it sounds like there is an inherent inverse relationship...but how does XL know what the relationship is? Does it change at a 1 to 1 amount?

Could you mock up some basic charts perhaps illustrating the change? Could be a before-and-after type look?

 

[Mido Allam]

Hi Luke,

Yes I'm beginning to think that as well :D . Yes i basically want to input the formula and be able to scroll through the graph to show different values. I think that is the closest thing i can do for him. like a dynamic chart of some kind. However i don't know how exactly that would work. I've made a simple chart so you can see the relationship between Sales and COGs. I hope it illustrates just that.

Thank you,

 

[Luke M]

Hi Mido,

Here's an attempt at maing a dynamic chart. I added in some more dummy data to your original set. I then setup a new range of formulas that retrieve info from the original set. The chart is now based off of this new formulas. The chart has a scroll bar underneath the COG axis, so boss can "move along" this axis. You can also change the number of data points plotted. Do you think this is what boss was looking for (or close)?