+ Reply to Thread
Results 1 to 18 of 18

Help on Complex Graph

  1. #1
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Help on Complex Graph

    Hi
    this is probably not possible - at least without an addon - but . . . .
    I have a graph of results which looks like this
    ExcelGrapheg.jpg

    The x axis is time

    The Y axis is a value (Rating)

    The bars show 'actual' ratings for up to 4 attributes
    The lines show actual (Red Solid) and Target (Green dotted) for an aggregate value derived from the attributes (eg average value of all attributes)

    What I would like to do is to show the Target values for each of ratings. (The bars show actual or reported values).
    Easiest seems to be as a thinner column or line in the middle of the bar but I am open to suggestions.

    Thanks for any ideas

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,670

    Re: Help on Complex Graph

    That should be possible using an additional series. Hard to comment further without a sample workbook...
    Rory

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,444

    Re: Help on Complex Graph

    Further to attaching a workbook: unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Re: Help on Complex Graph

    well . . . . . . if you really think so.
    the workbook is big and complicates so I'll have to mock up an example. Hopefully later today

    Thanks at least holding out hope !!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,444

    Re: Help on Complex Graph

    Yes, a mocked up example, please, not the whole kerboodle!!!

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,770

    Re: Help on Complex Graph

    Your chart looks like a pretty basic clustered column + line combination chart. You might review this tutorial: https://www.excel-easy.com/examples/...ion-chart.html which shows how to create a clustered column + line combination chart. The tutorial shows two data series with one of the series on the secondary axis. In your case, you have 5 data series (3 clustered column and 2 line) and all series are plotted on the primary axis.

    You also don't include your Excel version in your profile. I would note that these instructions apply to the newest version of Excel. It is still possible in older versions, but the locations of the different commands are different.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Re: Help on Complex Graph

    Well knocking upi the example was pretty straightforward.
    Getting it onto this site was a nightmare.
    After hitting advanced I kept getting
    vBulletin Message
    Your submission could not be processed because you have logged in since the previous page was loaded.
    Ive logged in; been back, reloaded several times.
    I hate technology!!!

    And I cant even post this.
    I'll log off and start over on the site :-)

    And the Go Advanced takes me to some stuff on updated forum rules

    I tried to send this and got the same error message
    Your submission could not be processed because you have logged in since the previous page was loaded.


    Horray its worked. Now back to the main topic!!
    Logged in and out again and . . .

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,444

    Re: Help on Complex Graph

    So, where is the workbook? Go Advanced should not take you to the forum rules!

  9. #9
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Re: Help on Complex Graph

    Well I dont really care what it should nor should not do that is where it keeps taking me.

    I have witched browsers (I presume there is some rubbish in the cache) and it now says I can post attachments so here we go (after posting this!)

  10. #10
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Re: Help on Complex Graph

    Well thanks for offering to have a look
    I (will hopefully) attach a small sample spreadsheet with graph that replicates what I have at the moment

    So there is a Main value and 3 sub values all of which have an actual and an expected value
    (THe main value already has a comparison - the other line which is main_expected value)

    The objective is to provide a comparison for the actual values of Sub 1, 2 and 3. The relevant values are held in Sun 1_EV, Sub 2_EV and Sun 3_EV

    Best visual approach seems to me to have a line in the middle of the corresponding bar that represents the expected value. But of you have a better / easier idea, I am all ears !!
    Attached Files Attached Files
    Last edited by RincewindWIZZ; 10-16-2018 at 11:19 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,444

    Re: Help on Complex Graph

    You did it - well done!

    So, is it the expected values (see below) that you want to show?

    Excel 2016 (Windows) 32 bit
    C
    D
    E
    F
    G
    H
    19
    Sub 1_EV
    0.94
    1.11
    1.45
    1.87
    2.65
    20
    Sub 2_EV
    0.46
    0.63
    0.95
    1.46
    2.56
    21
    Sub 3_EV
    0.49
    0.68
    1.05
    1.57
    2.61
    Sheet: Sheet1
    Last edited by AliGW; 10-16-2018 at 11:21 AM.

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,670

    Re: Help on Complex Graph

    You could do something like this, with the Expected values plotted as columns on a secondary axis, with no fill, but a border.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Re: Help on Complex Graph

    This is a truly horrid website. It seems to log me out after a period of inactivity but not tell me (and still shows logout at the top) But when I try to complete a reply it deletes all my typing (as opposed to keeping it and telling me I need to log in). And I have to type it all again. :-(

    So back to the main issue: Anyway, promising idea. Not very clear but better than nothing. I'll experiment and see i I can make it prettier. Hopefully I can work out how you set it up

    What determines which bar gets plotted on top of another bar?

    Maybe it works if I give the one of them transparent border?

    Thanks

    Maybe more tomorrow

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,444

    Re: Help on Complex Graph

    I am sorry you are having such issues with the forum. We haven't had any other reports of the problems you are experiencing, so I fear it must be an issue with your browser. I hope you will find it works better for you next time.

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,670

    Re: Help on Complex Graph

    Quote Originally Posted by RincewindWIZZ View Post
    What determines which bar gets plotted on top of another bar?
    The series on the secondary axis are plotted in front of the ones on the primary axis.

  16. #16
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Re: Help on Complex Graph

    Well I've had a play and, although I can make something work it does not really do 'what it says on the tin'!

    So I set the 'Actual' to a yellow bar. Just fine
    I set 'Expected' to a blue bar on a second axis and it appears in front. OK

    I want a thin vertical black bar for 'Expected' to I set
    Border to 4pt red (to see what is happening)
    Fill to solid black
    That is fine.
    Then I set border to transparent (100%) but all I see is the black fill.

    I would expect to see a border with 4 pt of "what ever was underneath the secondary axis bar" showing and the fill as a thinner black bar in the middle!

    I can see how the implementation might make it work like this but I don't think
    • it really accords with the description of 'transparent border' and
    • its very helpful - at least not as helpful as narrowing the fill to fit within the transparent border

    Disappointing and not very helpful

    Unless, of course, I have misunderstood - he said hopefully?
    Last edited by RincewindWIZZ; 10-18-2018 at 07:58 AM.

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,770

    Re: Help on Complex Graph

    Then I set border to transparent (100%) but all I see is the black fill.
    You set the border's (not the fill's) transparency to 100%? I would not expect the border's transparency to have any effect on the fill's transparency or the fill's size.
    If I understand what you are trying to do, when I have seen someone try to use the column border in this way, it is to apply the thick border to the "underneath" (primary axis, 'actual' yellow bar). This causes the underneath column to appear thicker than the column on top.

    I think what you have misunderstood (if there is enough documentation out there to misunderstand) is that the border appears to "add" to the outside of the column. The border does not "cut into" the fill space. This is why I think others have used the border on the underneath column to make it appear wider.

    I don't know if this will create confusion -- ignore if you would prefer to pursue the current approach. If I were doing this, I think I would use a column + XY scatter (rather than column + line) chart to create a "clustered columns and lines" chart. (two approaches in this tutorial: https://peltiertech.com/Excel/Charts...olAndLine.html ). The Main, and Main_EV series would be formatted to have lines without markers and would be centered over the clustered columns. The individual Sub n_EV series would be formatted to have markers without lines and positioned over the appropriate column. If I really wanted a horizontal bar effect, I would add horizontal error bars to the Sub_EV series, and maybe get rid of the marker.

  18. #18
    Registered User
    Join Date
    03-03-2005
    Posts
    41

    Re: Help on Complex Graph

    MrShorty: Thanks for this insight. It would certainly fit with what I observed.

    If you have a single column, what you mention would work well. But clustered columns present more of a problem which is why I think this is a pretty short sighted (lazy is probably better) approach to providing the feature

    With these clustered columns the width of the set of columns and therefore each individual column is effectively fixed. So to make a column larger seems a pretty daft design to me! And if I have adjacent columns (as I will) each with a larger border, one will overlap the other making this less than obvious Presumably there will be a sequence rule - second drawn overlaps the first drawn.

    What does seem to sort of work is to add a bunch of extra columns on the secondary axis. YO can increase the number to give the effect you want. I ended up with 2 extra cols in each position)
    one before the 1st value, 1 between each value and 1 after the last value. The the secondary axis bars are smaller and within the confines of the primary axis bars. They are not quite centralised but not seriously out.

    But its a fudge to get round poor design of software!

    For example:
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. complex charting/graph
    By tlbw73 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-25-2015, 03:44 PM
  2. Complex graph required
    By missRoxanne in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-27-2014, 03:27 PM
  3. Using a complex formula for both X and Y in a graph
    By mhrobson in forum Excel General
    Replies: 2
    Last Post: 03-22-2012, 04:42 PM
  4. Need help with Complex Graph
    By wilhan01 in forum Excel General
    Replies: 0
    Last Post: 08-15-2011, 03:22 PM
  5. Complex Chart/Graph
    By TBaker14 in forum Excel General
    Replies: 1
    Last Post: 07-05-2010, 10:08 AM
  6. Creating a complex graph
    By chiggy99 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 05-08-2009, 10:51 AM
  7. Complex Graph?
    By Joeseph Dafow in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-14-2006, 10:47 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1