1

I want to create a chart with two different scalings (steps of 500 and steps of 10.000) on the y-axis.

This is needed because there is a fixed cost of 1000 (so a straight line from left to right) and three graphs for different scenarios, which all have rather high values in the end.

Currently this leads to the fixed cost line being extremely small and barely visible, because I have to choose a rather big scale (10.000) in order to get a reasonably sized chart.

However, I want this fixed cost line and especially the points of contact clearly visible.

Imho, to achieve that I need to use two different scales. I came up with a quick and dirty sketch of my desired result in MS Paint:

Is it possible to achieve this with Excel? Appreciate any tips

  • As I imply in a comment below, I think you should allow your Y Axis to scale from 0:50000 and allow your 1000 value to appear small-it is small compared to the overall range of values, there's no sense in trying to make it more than it is. – dav Jun 5 '15 at 14:44
  • When you say 10.000, do you mean 10 ⁰⁰⁰∕₁₀₀₀, or do you mean 10⁴?  I suspect that most of the people on this site will, at least at first glance, interpret 10.000 to mean 10 ⁰⁰⁰∕₁₀₀₀, and will write 10⁴ as 10,000 (or, perhaps, simply as 10000, which is how Excel displays it by default on my system). – Scott Jun 14 '15 at 4:53
  • I mean 10^4. I can't see how anyone would think I mean anything different? Is it the .? Probably an imperial system thing again... If I would mean 10000/1000 I would write 1000 ;-) I mean who states numbers as a calculation instead of the result value? – daZza Jun 15 '15 at 14:54
  • @daZza: Sorry, I only just noticed that you had responded to me.  BLUF (Bottom Line Up Front): Yes, it’s all about the ".".  “Who states numbers as a calculation instead of the result value?”  I don’t understand the question.  “If I would mean 10000/1000 I would write 1000”  I don’t understand that, either, and I can only hope it means that we have a typography/formatting/rendering problem; you might not be seeing the characters that I’m using the same way I see them.  … (Cont’d) – Scott Jun 29 '15 at 17:20
  • (Cont’d) …  To clarify, I said (i.e., I meant to say) “…most of the people on this site will, at least at first glance, interpret 10.000 to mean 10 + ⁰⁰⁰∕₁₀₀₀, …”.  I never said “10000/1000”, so I guess we can write that one off as a misunderstanding.  (But even if that’s the problem, why would you say that 10000 divided by 1000 is 1000?  Surely 10000 divided by 1000 is 10, anywhere in the world — even if you’re using hex, octal, or binary.)  … (Cont’d) – Scott Jun 29 '15 at 17:20
1

As Jon Peltier mentions in the answer to the question linked to by Mate Juhasz, a panel chart might be more appropriate for your situation. It can show an overall view, with a detail of the contact points. Excel can easily make this type of chart:

Panel chart example

By using similar proportions in each chart, the line slope is the same. But the intercept points can be highlighted and each "scenario" line can be labeled with the intercept.

This could also be a scenario where you use a dual-axis chart, like this:

Dual Axis

But that could lead to a serious mis-interpretation, so proceed with caution. If choosing this route, you may consider adding a definitive break, like this:

Dual Axis with break

| improve this answer | |
  • The dual axis looks exactly like what I need. Will remove the second axis via Photoshop and manipulate the remaining axis to the values I need. Thanks! – daZza Jun 6 '15 at 16:45
1

You might simply apply a logarithimic scale (select Y axis -> format axis [or double-click on axis] -> axis options -> logarithmic scale with base of 10).

| improve this answer | |
  • Thanks for the suggestion, however, doing this destroys the whole purpose of the chart. Somehow the straight lines turn into parabolas, i.e. they don't have a static growth anymore. – daZza Jun 5 '15 at 14:18
  • You may consider that the parabola is the correct visual interpretation for the log axis, so if that's a problem for correct interpretation of your chart, will any other manipulation of your Y axis (e.g. breaking it) also be subject to misinterpretation? Simply put, 1000 is a small value compared to 50000, and when graphed, its appropriate that it appears very small. – dav Jun 5 '15 at 14:40
  • Breaking it wouldn't cause misinterpretation, as the look of the chart would still be the same as before, just with a clearer view on the fixed cost line. In theory it would indeed be appropriate to appear very small, however, it is just not practical for representation on a PowerPoint slide. I might just photoshop the stuff... – daZza Jun 5 '15 at 15:13
  • Hmm - of course it's your chart, so you have to decide - but even with a logarithmic scale, the different scenarios are still clearly distinguishable and discernable. What about providing two charts - one with linear growth (and no fixed cost line), and one with logarithmic scale, to make the small values better discernable? – tohuwawohu Jun 5 '15 at 15:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.