+ Reply to Thread
Results 1 to 3 of 3

Need help solving a lookup function for date range with graph

  1. #1
    Registered User
    Join Date
    09-05-2012
    Location
    Tacoma
    MS-Off Ver
    Excel 2010
    Posts
    39

    Need help solving a lookup function for date range with graph

    I have a spreadsheet that currently allows me to lookup historic returns based on a starting date (historic) forward (current). I want to modify that with cell K4 and have a date range of any month(s) within the spreadsheet (example is 01-01-2012 through 07-01-2012) that would change the investment value accordingly. In addition to that I want to see if its possible to have a graph that would graph that date range for the investor returns similar to that of the Total Fund Performance graph that is dynamic and changes with the date range.

    I attached the spreadsheet. Any help would be very much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Need help solving a lookup function for date range with graph

    Hi, I used to live in Fircrest

    The key to this enterprise is named dynamic ranges.

    I created two static ranges:
    End_Date =Toggles!$K$4
    Start_Date =Toggles!$I$3

    I didn't have to do that, but they look better in the formulas.

    I then added some helper cells to do some calculations. You can "clean these up" as you see fit. I left them there so you can see the intermediate calculations and how they change when you change the data.
    M3 =MATCH(Start_Date,Data!6:6,0)
    M4 =MATCH(End_Date,Data!6:6,0)
    M5 =M4-M3

    M3 gets the column where the start date is found
    M4 gets the column where the end date is found
    M5 is the number of columns (months) between them

    I have two dynamic ranges that are based on these values:
    Inv_Date =OFFSET(Data!$A$6,0,Toggles!$M$3-1,1,Toggles!$M$5+1)
    INV_ROI =OFFSET(Data!$A$40,0,Toggles!$M$3-1,1,Toggles!$M$5+1)

    For an explanation of how the offset formula works, see this article: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

    I did my calculations for Total ROE in cell M10.

    I built the chart by inserting a blank line chart and right clicking on it and selecting select data. If you do that and click on Edit for the series and X-axis, you'll see how I used the named dynamic ranges to plot the data.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    09-05-2012
    Location
    Tacoma
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Need help solving a lookup function for date range with graph

    Impressive. That is exactly what I needed. Thank you so much! I have a lot of people I know in Fircrest. Nice little town. Thanks again!

+ 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. [SOLVED] LOOKUP TODAY function within a date range to return a value
    By Mike Brewer in forum Excel General
    Replies: 9
    Last Post: 12-04-2013, 12:09 PM
  2. [SOLVED] Formula to Lookup value based on date within a date range
    By MHamid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-26-2013, 02:24 PM
  3. Replies: 2
    Last Post: 06-07-2013, 02:21 PM
  4. Replies: 2
    Last Post: 10-31-2012, 11:44 AM
  5. [SOLVED] lookup date within date range grid to return fiscal month value
    By tigerseye001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2012, 02:27 PM
  6. Replies: 3
    Last Post: 06-10-2012, 11:02 AM
  7. Automatically update graph range based on date in column
    By basalisbury in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2012, 12:03 PM
  8. Replies: 0
    Last Post: 06-20-2006, 03:40 AM

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