+ Reply to Thread
Results 1 to 24 of 24

Help with creating line chart or scatter plot.

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Help with creating line chart or scatter plot.

    Hi all,

    Please see attached picture and workbook.

    Chart.png

    I have some source data that I'd like to display graphically. This is price data where one row represents one day. Each day starts at 09:30 and ends at 16:00 where the two swing points in between vary. I run into two problems:

    1. Ideally, I'd like to use the "source data" to create charts directly, but I haven't been able to do so. Maybe it can be set up differently to accomplish this. Ideally, it would be one row per each day.

    To create the associated charts, I've manually set up my data as can be seen to the far left. I don't know how I'd proceed doing this for an entire set of data.

    2. Regardless of when the price points (Point 1 Time & Point 2 Time) are recorded, they are each given the same weight/length. This is shown very clearly on the both the first and last day where the swing point 2 is almost at 16:00, yet it's just as lengthy as the first swing of the day.

    Using a scatter diagram seems like it might solve this actually. At least it's a big improvement.

    But the scale isn't right...Id like it to always start/end at 09:30/16:00.

    Any advice on how to proceed with this?

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Help with creating line chart or scatter plot.

    Id like it to always start/end at 09:30/16:00
    set MIN/MAX values for the X-axis

    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Hi, humdingaling,

    Thank you! Those charts look pretty much exactly how I'd like them to.

    I just tried making one.

    Any idea on how to solve my issue with regards to creating charts directly from the data I have in columns M-R? Or setting up that data differently in order to accomplish that objective?

    An alternative solution would be to simply have cells G2/3 and H2/3 reference the relevant data from columns O/P and Q/R. A bit more cumbersome perhaps, but it would work for sure.

    Regardless, thanks for all help so far.

    Elijah

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Help with creating line chart or scatter plot.

    Any idea on how to solve my issue with regards to creating charts directly from the data I have in columns M-R? Or setting up that data differently in order to accomplish that objective?
    I find that scatter charts are easiest to create when the X values are in a contiguous range and the Y values are also in a contiguous range. In your table in column M-R, some of your x values/times are interspersed with the Y values, and the start and end times are only present at the top of the table. If I wanted to work from something like your M-R table, I might arrange it so that the times are in 4 adjacent columns, followed by the y values in 4 adjacent columns:
    Please Login or Register  to view this content.
    Then each scatter chart can refer to columns N:Q as the X values for its row, and column R:U for the Y values for its row. Once you have created one scatter chart, you can make the other charts by copying the chart and moving the series definitions down a row (or use a loop in VBA to loop down the rows).
    Last edited by MrShorty; 05-01-2018 at 11:23 AM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Hi, MrShorty,

    Thank you for your input!

    What you're suggesting is certainly a more clever way of organizing the data. I did that just now. But creating those charts weren't quite as easy or straightforward as previously. My labels ain't quite correct for the time data...

    Uten navn.png

    As for creating additional charts, I was initially hoping I could have some VBA code which let's me have only one single chart on the top of my sheet and where I can change it based on which date is selected.

    But, I'm not able to do that on my own, so I gave up on that for now and will probably just go about doing it manually at least initially.
    Attached Files Attached Files

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

    Re: Help with creating line chart or scatter plot.

    My labels ain't quite correct for the time data...
    the x axis labels look "correct" to me (for a fixed min and max and automatic major and minor units). What is incorrect about the x-axis labels?
    I was initially hoping I could have some VBA code which let's me have only one single chart on the top of my sheet and where I can change it based on which date is selected.
    Does it have to be VBA code? I would probably use a bunch of lookup functions in a helper row:

    1) Enter desired date in V1 (or where ever you want to put it). If desired, use data validation to restrict entry to the list in V3:V9 (https://support.office.com/en-us/art...6-eff3ce5f7249 ).
    2) W1 =VLOOKUP($V1,$V$3:$AD$10,columns($V1:W1),FALSE). Note the mix of relative and absolute references for easy copying. Also note the "exact match" linear search option in the 4th argument. The COLUMNS() function is there to compute the desired column to return from. Copy W1 into X1:AD1. Note that this works well for a small sheet like this. If this were to be applied to a much larger database of dates, I might do something different here to avoid the slow linear lookup.
    3) Build chart using W1:Z1 as the x values, AA1:AD1 as the Y values. Other formatting as desired.
    4) To change the date of the chart, enter the desired date in V1 (or use the dropdown list if you applied the data validation). The other values should update automatically and the chart should follow.

    Assuming I understand, that's probably how I'd approach this part of the question.

  7. #7
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Quote Originally Posted by MrShorty View Post
    the x axis labels look "correct" to me (for a fixed min and max and automatic major and minor units). What is incorrect about the x-axis labels?
    Hmmm...re-opening the workbook now and it is indeed correct, but before saving (and on picture attached) it says hh:01, hh;02, etc.


    Quote Originally Posted by MrShorty View Post
    Does it have to be VBA code? I would probably use a bunch of lookup functions in a helper row:

    1) Enter desired date in V1 (or where ever you want to put it). If desired, use data validation to restrict entry to the list in V3:V9 (https://support.office.com/en-us/art...6-eff3ce5f7249 ).
    2) W1 =VLOOKUP($V1,$V$3:$AD$10,columns($V1:W1),FALSE). Note the mix of relative and absolute references for easy copying. Also note the "exact match" linear search option in the 4th argument. The COLUMNS() function is there to compute the desired column to return from. Copy W1 into X1:AD1. Note that this works well for a small sheet like this. If this were to be applied to a much larger database of dates, I might do something different here to avoid the slow linear lookup.
    3) Build chart using W1:Z1 as the x values, AA1:AD1 as the Y values. Other formatting as desired.
    4) To change the date of the chart, enter the desired date in V1 (or use the dropdown list if you applied the data validation). The other values should update automatically and the chart should follow.

    Assuming I understand, that's probably how I'd approach this part of the question.
    VBA is absolutely not necessary. I don't really know VBA, so probably better for me to see if I can pull it off without that.

    I have never used this formula and don't fully understand it, but I tried following your steps and I do understand your logic. It seems like I'm not getting it quite right. Probably a very small adjustment.

    I'm attaching my attempt.

    I noticed you using $V1 and not $V$1. Is that intentional? I've always used a double. I did try using both single/double here and it didn't seem to make a difference.

    As for size of database, I am currently using just a small sample size in the uploaded sheet. Typically, I have say 1000-2000 rows of data. Would that work? I don't mind if it takes a bit to load as long as it's not crashing my system or taking forever.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Help with creating line chart or scatter plot.

    I assume you mean the VLOOKUP() function when you say you have never used this function: https://support.office.com/en-us/art...8-93a18ad188a1 It is probably worth familiarizing yourself with this function, as lookups are a common Excel task.

    If you use the evaluate formula utility (https://support.office.com/en-us/art...6-a70aa409b8a7 ), you will see that the COLUMN($V1:W1) function is returning the number 22. there are only 7 columns in the lookup table. As explained in the VLOOKUP() help file, if col_index_num argument is outside of the number of columns in the lookup table, an error is returned. Note that, in the formula I suggested, I used the COLUMNS() function (which returns the number of columns in a reference) where you used the COLUMN() function (which returns the column number for a reference). It should work just fine if you will add that "S". (Alternatively, leave the COLUMN() function, and change the reference to B1 so it will return 2).

    As for the difference between $V1 and $V$1, I intentionally left the row reference relative. In this case, where all copies are on the same row, it doesn't change anything. In the future, if you ever wanted to expand this to two rows, or something, you would need to think about whether or not you would want the row reference to be absolute or relative.

    1k to 2k rows? I think you can only try it and see if it is too slow. I would probably be sure to save before expanding the lookup table, just in case. I would expect that 1k to 2k rows would make it slow but not completely hung (though sometimes the difference between "slow" and "hung" is a matter of your own patience and not what is going on in the computer). Try it on a larger data set and see if it tries your patience or not.

  9. #9
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Thank you so much, MrShorty!

    I won't have time to implement this tonight, but will try tomorrow and report back when I have done so.

    Best regards.

    Elijah

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Help with creating line chart or scatter plot.

    hmm
    since its date, how are you distinguishing between the 1st n 2nd occurrence of the same date?

    also if you having 1-2k rows
    how many graphs are you showing/keeping at once time
    and how you are thinking of indicating which ones of these should be shown?

  11. #11
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Since my date format has year in the end, I assume there won't be multiple occurences...?

    With regards to how many graphs to show, I will be happy to have one (or maybe two for comparison) at a time.

    I'm attaching an update implementing what MrShorty showed me. Now the chart can be updated dynamically based on which date I select in the list. I have not yet been able to try it for my larger sheet (1-2 K rows) yet.

    I'd also be very happy if I could add a thicker or colored line at "0", but I'll look into that formatting later.

    Thanks for all help, guys! You are awesome.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Help with creating line chart or scatter plot.

    in your sample file 2/3/18 appears twice, i assume the 2nd should be 2019 then?
    anyways that is a non issue then

    it would seem you have figured out dynamic graph based on data validation
    so then you should not have problem setting up another for your comparison

    With the zero line, just add another series into the chart ..make it as thick or thin (or color/etc) as you want
    just tuck away the data behind the chart to something to obscure it

    As you have now fixed chart you dont need to reset the min max all the time, as such you dont need an XLSM file
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    humdingaling,

    Excellent! Thank you very much!

    The date appearing twice is an error on my end. Nice observation. So that won't be an issue.

    Thanks for showing me the zero line. Very nice.

    Regarding the min/max - is that done just adjusting these values in the formatting option? Seems like it. I just did it and that seems to work very well.

    Allright guys. Thank you so much for all help. This is really great.

    I'll report back when I've completed the sheet. Should be able to find time tomorrow.

    Best regards.

  14. #14
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Help with creating line chart or scatter plot.

    Thanks for showing me the zero line. Very nice.
    just remember it needs to be hidden "from view" (so either behind a chart or far away from where the action is)
    if you go and actually hide row then it will disappear from the chart itself

    Regarding the min/max - is that done just adjusting these values in the formatting option?
    that is correct
    if you only plan to have a static chart and only change the values then you just need to set and forget the formatting options of the axis

    Good luck!

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

    Re: Help with creating line chart or scatter plot.

    I'd also be very happy if I could add a thicker or colored line at "0", but I'll look into that formatting later.
    If it helps, or if you decide the "dummy series" approach is unsuitable, I simply formatted the axis to have a thicker line: Select horizontal axis -> Format axis -> Line style -> make it about 2pts thick (or whatever you like) -> to change color, click on line color and select the desired color. This assumes that the vertical axis is formatted so that the horizontal axis always crosses at 0 and that 0 will always be present on the vertical axis.

  16. #16
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Excellent, guys. Thanks!

    The 'dummy series' approach is suitable, but of course it's easier if one can avoid it. I followed your advice, MrShorty, and it turned out great! The key that I've missed in the past is to make sure that the horizontal axis always crosses at 0 like you suggested.

    I finally got around to implement this on my main sheet and it seems to work great! I currently have 1300 rows of data and to me it's lightning fast (1-2 seconds to update the chart). That suggests to me that there shouldn't be any problems to work with a even larger sheet. So, thanks for all help guys! This is more than I could have asked for. Really appreciated.

  17. #17
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Quote Originally Posted by MrShorty View Post
    If it helps, or if you decide the "dummy series" approach is unsuitable, I simply formatted the axis to have a thicker line: Select horizontal axis -> Format axis -> Line style -> make it about 2pts thick (or whatever you like) -> to change color, click on line color and select the desired color. This assumes that the vertical axis is formatted so that the horizontal axis always crosses at 0 and that 0 will always be present on the vertical axis.
    Hi again, MrShorty,

    This seemed to work great, but suddenly it doesn't. Guess I didn't test it enough.

    To clarify, it still works on some charts, but then loading a new chart, it doesn't. Adjusting the settings (automatic/manual) seems to do the trick for the selected chart, but I still get trouble loading new charts again.

    Is there some universal setting that will work for all charts or is this perhaps inevitable?

    Thanks in advance!
    Attached Images Attached Images
    Last edited by Elijah; 05-29-2018 at 04:09 PM.

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Help with creating line chart or scatter plot.

    I am not sure what you are describing. Are you claiming that Excel's chart is displaying the horizontal axis somewhere other than where you have told it to (at 0)? I cannot say that I have ever seen Excel put the axis anywhere it wants when I have told it to cross the axis at 0. I cannot see anything in your pictures that indicates why Excel would be placing the yellow line at something other than y=0 as in the second picture, nor why a manual/automatic toggle would "fix" it.

    Best way forward might be for you to upload another sample file that exhibits the problem, and maybe include detailed instructions that leads up to the problem and what you do to fix it. Then we can see if we can recreate the issue and better understand what you are seeing.

  19. #19
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Hey, MrShorty,

    Thank you!

    I'm attaching a workbook in case you want to take a look. Currently, on chart 1, I have displayed a chart which does not plot the horizontal line at zero.

    Unless I'm doing something wrong, I think my settings are correct even now. But the chart isn't correct. Adjusting it back-and-forth seems to solve it for that single chart. But then the problem returns...

    Thanks in advance for all help.

    Elijah
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Oh, and just try a few chart combinations on your own (I just copy and paste values into cell B2/B3). I'm sure you'll also experience that something will go wrong... At least with current settings.

    It's like it works with 60-70% of my charts, but on some, it just don't work.

  21. #21
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Hi all,

    I'm bumping this one. Thanks again for all help I received the last time this thread was up.

    Haven't used this in a while, but I wanted to use it now and discovered that this issue with the zero line not sticking to zero is not resolved. Please see attached Excel file.

    When opening the workbook everything should look correct. Axis is set at 0,00 and the chart is otherwise correct.

    But, if I paste in for example row 30, Date 24.05.2018 in cell B2, I end up having the zero line at - 5,00.

    If I now go into the chart setting and adjust some value, it seems to update correctly. Maybe this is some inbuilt flaw in Excel? Or am I messing something up?

    Might end up going with the 'dummy' approach if I can't figure this one out.

    Thanks in advance!

    Elijah
    Attached Files Attached Files

  22. #22
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Help with creating line chart or scatter plot.

    I cannot replicate what you describe. When I open the file, B2 is already 5/24/2018, and the chart looks correct. If I try to enter 24.05.2018 (or any other date in that format) my copy of Excel does not recognize it as a date, so it enters that exact text string and everything kind of breaks. If I enter any date from column Y in a way that Excel recognizes the date as a date into B2, the chart automatically updates correctly. I note that the y axis limits are currently set to automatic, so I could conceive of a scenario where Excel's automatic axis limits algorithm might choose a range that does not include 0 -- in which case the yellow axis would appear at whatever Excel chooses as the axis min (or max, if all the values are less than 0). Since it appears that all of the open values in column AD are 0, then I don't see Excel ever automatically choosing an axis range that does not include 0.

    Is it something unique to Excel 365? Or something unique to your installation? I cannot recreate the issue here (Excel 2007).

  23. #23
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Help with creating line chart or scatter plot.

    Hi,

    Thanks! On my way out so would have to make a quick comment only:

    Do you get the same error if you copy and paste dates from the table into B2 instead of typing?

  24. #24
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Help with creating line chart or scatter plot.

    pasting values from B28+ or from Y28+ behaves the same -- the chart updates as expected, and the yellow axis stays anchored at 0. I cannot replicate the behavior you describe by pasting, either.

+ 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. Converting Scatter plot to Line plot and back
    By SPDavern in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-26-2015, 10:49 AM
  2. [SOLVED] creating step chart through scatter plot with x y error bars excel 2007
    By fight2 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-16-2015, 11:14 AM
  3. Plot the line of a function and also scatter dots on the same chart
    By zman44 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-05-2013, 08:54 AM
  4. [SOLVED] Add x=y line to scatter plot
    By danipoak in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-09-2012, 09:32 PM
  5. Plot points on a area chart or plot areas on a scatter chart
    By maggy in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-17-2012, 04:39 AM
  6. Chart - finding the position of the line on a scatter plot
    By shamedt in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-08-2011, 02:16 PM
  7. Converting XY Scatter plot to Line Plot and back
    By SPDavern in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2005, 10:06 PM

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