View original (French)

How to shift overlapping curves?

Solved
g0refest Posted messages 32 Registration date   Status Member Last intervention   -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
Hello,

I have a fairly simple problem:

I have several curves that overlap in the same .xlsx chart (points/values very close to or even identical) and I would like them all to be fully visible in a line (but not on the same plot of course). Purists might say that this is not a very mathematical solution, but aesthetics will surely understand me ^^

PS: Changing colors, transparency, or using dotted lines is, of course, not what I am trying to do ;-)

PPS: I would also like to avoid having to alter my beautiful table by implementing "fictitious" values to create the offset.

Does anyone have a solution? Thanks a lot. =)

Configuration: Windows 7 / Firefox 18.0

4 answers

  1.
    Lebossist Posted messages 5 Status Member 4
     
    Hello g0refest,

    It's possible.

    You need to add a second y-axis on the right side of your chart, assign one of your series to it, and adjust the scale of that axis.

    See you later.
    1
    1. ergor Posted messages 157 Status Member 12
       
      Better solution than mine ;)
      0
    2. g0refest Posted messages 32 Registration date   Status Member Last intervention   2
       
      It's already much better - Thank you ;-)

      Is it possible to add/create other axes? Tertiary? Quaternary? There are still a few curves that remain hidden.
      0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    proposal with checkboxes:
    https://www.cjoint.com/c/CAxuDrzl1iJ

    eric

    --
    You will never respond to an unsolicited DM...
    Well, that's done.
    1
    1. g0refest Posted messages 32 Registration date   Status Member Last intervention   2
       
      Good evening,

      Thank you, it’s an interesting solution... but how to achieve this result? (how to incorporate the checkboxes & the +1+2)?
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      you need to define names with a formula and use these names for the curves.

      The checkbox1 is linked to G1, etc.
      For each curve, define a name according to this model:
      Name: series1
      Refers to: =Sheet1!$B$2:$B$26+Sheet1!$G$1*Sheet1!$G$3
      (if the box is checked, the value in G3 is added)

      Select a curve on the chart, its formula appears at the top in the editing box.
      Replace the value range (3rd parameter) with its name:
      =SERIES(Sheet1!$B$1;Sheet1!$A$2:$A$26;Workbook1.xls!series1;1)
      https://www.cjoint.com/c/CAxwsSJe1xE

      eric
      0
    3. g0refest Posted messages 32 Registration date   Status Member Last intervention   2
       
      I managed to incorporate the checkboxes with a value and I named my series - success! But I didn't quite understand how to link the values of a curve with TRUE/FALSE*VALUE (1 for example).
      Is it by selecting the data source of this curve in the graph and modifying them in x or y? I systematically get an error message :-(
      0
    4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      No, you cannot set a name as the data source.
      You need to click (left) on a curve in the chart to select it. Then you will see a formula appear in the editing area. It is in this formula that you need to replace the range from Sheet1!$B$2:$B$26 with the calculated name series1.
      Once done, take the opportunity to shift it to more easily select the others.

      eric
      0
    5. g0refest Posted messages 32 Registration date   Status Member Last intervention   2
       
      AAAAAH! It works! Fantastic!!!

      I struggled a bit because the "Name a range" field in my Excel version was slightly different from the example you posted (i.e. proof).

      A big thank you to everyone for your suggestions, and especially to you Eriiic, as well as for your availability and patience!!
      0
  3. ergor Posted messages 157 Status Member 12
     
    Hello,

    As for me, the only solution I can propose is to make 2 charts :).

    If you really want to offset one of the two curves, apply an offset to one of your series (e.g., + 3), but it's mathematically ugly and completely wrong if you display the markers.
    0
    1. g0refest Posted messages 32 Registration date   Status Member Last intervention   2
       
      It's a function that is quite interesting, but I'm going to try my luck with a "graphic" solution ;-) Thank you ^^
      0
  4. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Hello g0refest.

    After 5 messages, you still haven't thought about submitting your file so we can test the different solution approaches...
    In the meantime, may I suggest replacing the curves with histograms?

    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0