+ Reply to Thread
Results 1 to 11 of 11

Bar & Line graph for 30 day rolling data points

  1. #1
    Registered User
    Join Date
    06-23-2014
    Location
    New York
    MS-Off Ver
    2003 & 2007
    Posts
    9

    Bar & Line graph for 30 day rolling data points

    Hi,

    I am trying to create a graph using the below two data points -

    1. Balance sheet amount (in USD)
    2. Rate of Return (in basis points)

    These two data points are calculated for each business day on a daily basis. Each file contains only two values (balance sheet and rate as of that business day only). I want the graph to-

    1. Plot the Balance sheet amount as a bar column and the corresponding rate of return as a line (on the 2nd Y axis).
    2. Plot the data points for only the last 30 days at any given point in time in the same graph (though each day's file does not contain historical data for these two data points).

    I assume there would have to be another sheet to store historical data for the graph.

    Appreciate if anyone could provide any insights.

    Thanks
    ardent_guy

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Bar & Line graph for 30 day rolling data points

    You will need a histroical record of everything. Here is an example of how it might look. Add a row onto the bottom of the Table and it will display on the graph and the 31st entry will disappear...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    06-23-2014
    Location
    New York
    MS-Off Ver
    2003 & 2007
    Posts
    9

    Re: Bar & Line graph for 30 day rolling data points

    Thanks a lot Glenn! Will try this; What kind of graph is this, since if i right click on the graph, it points to 'Templates' and theres nothing under it. If you could provide the type of graph (column, line?), I'll replicate it in my spreadsheet.

    Thanks again!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Bar & Line graph for 30 day rolling data points

    It started life as a column graph, with two sets of columns. I then changesd one of the sets to a scatter graph to get the line graph you wnated. if you have any more queries, shout. If not, pelase mark the thread as solved and (preferably) click the Add Reputation button at the foot of this post.

  5. #5
    Registered User
    Join Date
    06-23-2014
    Location
    New York
    MS-Off Ver
    2003 & 2007
    Posts
    9

    Re: Bar & Line graph for 30 day rolling data points

    Thanks Glenn. It worked with one weird issue - the graph is showing the latest date first and not the other way as in your file, for instance, 06/23 shows first and 5/30 shows last, while i need 06/23 to be shown last. Please see attached. Whats the fix for this?

    Thanks
    ardent_guy
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Bar & Line graph for 30 day rolling data points

    Im on my phone, not my PC... So it'll be the morning before I get to this. But...

    By normal conventions, the most recent event will be at the right hand side of the graph. Change the value of the most recent day to something really large (100000, say). Which point on the graph changes: the one on the right (normally regarded as correct), or the one on the left (NOT normally regarded as correct).

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Bar & Line graph for 30 day rolling data points

    Hi there. Fixed.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-23-2014
    Location
    New York
    MS-Off Ver
    2003 & 2007
    Posts
    9

    Re: Bar & Line graph for 30 day rolling data points

    Hi Glenn, thanks for the file. I tried a different way, i selected the date axis and right clicked and went to axis options -> checked 'Categories in reverse order' and got the same result as yours.

    But, i dont want to see gaps since i dont have that much data yet. Is there a way to size the graph to show only days with data and ignore gaps? btw, i went to data source to remove gaps from the data, but couldnt.

    Thanks
    ardent_guy

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Bar & Line graph for 30 day rolling data points

    For now, I have assumed that by "Gaps" you mean weekends, etc. They have been removed by changing the X-axis to text. If you mean the gaps caused by the fact that you don't have 30 days worth of data yet, you can lose them by changing the range of the charts. However, I'd be tempted to advise you to be patient, for in little less than another week or so, you wont have any gaps!
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-23-2014
    Location
    New York
    MS-Off Ver
    2003 & 2007
    Posts
    9

    Re: Bar & Line graph for 30 day rolling data points

    Thanks Glenn.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Bar & Line graph for 30 day rolling data points

    Cheers! Glsd to have been able to help.

+ 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. Replies: 3
    Last Post: 11-15-2013, 06:28 PM
  2. Problem charing w/line graph when a series has missing data points
    By tvdij in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-08-2008, 04:05 AM
  3. Naming specific data points in line graph
    By Innov8ive in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-02-2007, 03:54 PM
  4. [SOLVED] Need 2 rows to display X axis data points for a line graph
    By SusanG in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-08-2006, 01:40 PM
  5. [SOLVED] missing data points causes my line graph not to connect
    By liebw in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 09-01-2005, 08:05 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