+ Reply to Thread
Results 1 to 5 of 5

Formula (?) to plot a predicted date based on two values

  1. #1
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Question Formula (?) to plot a predicted date based on two values

    Hi everyone,

    I'm creating a spreadsheet where I am trying to specifiy a future date based on three criteria;

    1) a date
    2) a 'level' (either a value of '2' or '3')
    3) a 'credit' value, which will fall into 1 of four ranges

    Because the credit falls into a range rather than a fixed value I'm struggling to even get started; I did think a lookup or Index function or something, but calculating which range the credit value belongs in has lost me. In the attached spreadsheet I have included the criteria table, the bands and the expected output (you can ignore columns H and J for this).

    I hope that makes sense, if not let me know what you need. I am learning so much from this forum and consequently that's got me trying to do more and more which leads to these problems!

    Thanks in advance,

    Dabooka
    Attached Files Attached Files
    Last edited by Dabooka; 12-03-2014 at 07:53 AM. Reason: Typos

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Formula (?) to plot a predicted date based on two values

    Hi, Refer the attachment.. a little cell formatting done in cells b3:b6
    Attached Files Attached Files
    Click just below left if it helps, Boo?ath?

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Formula (?) to plot a predicted date based on two values

    My proposition is simitlar, but based on single VLOOKUP:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Data in "source table" shall have only numbers (in first column "equal or bigger than") - see attachment

    PS. Od course you can use custom formatting of cells to have " mths" text displayed.
    Attached Files Attached Files
    Best Regards,

    Kaper

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Formula (?) to plot a predicted date based on two values

    Change the data in your Credit/Unit table
    Column B, we know their credits it says so in B2, change this column so it reads 5,13,25,37 (just the first value each row of column B)
    Change column C so it reads 4,6,8,12
    Change column D so it reads 5,7,10,12

    in J3
    =INDEX(IF(G3=2,C$3:C$6,D$3:D$6),MATCH(I3,B$3:B$6,TRUE),1)
    and copy down the column

    in M3
    =DATE(YEAR(K3),MONTH(K3)+J3,DAY(K3))
    and copy down the column

    Cue drum roll, fanfare etc... :-)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Registered User
    Join Date
    10-24-2014
    Location
    North East England
    MS-Off Ver
    Pro Plus ver 10
    Posts
    34

    Re: Formula (?) to plot a predicted date based on two values

    Thanks for all your help guys, greatly appreciated!

    I assumed there would be a formula without the table, and when I played with a table, it didn't occur to me to just change the range data to the first value! What a tube....

    Three different solutions, I'm not going to even pretend I undertsand the differences!

    Cheers,

    Dabooka

+ 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. Macro to plot graph based on date from inputbox
    By reesjordan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-30-2013, 07:52 AM
  2. Scatter Plot Min/Max Values in Numbers not Date
    By Jhawk in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-28-2013, 03:38 PM
  3. Macro to plot chart based on date time and data
    By stormlab in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-05-2011, 12:04 PM
  4. Replies: 5
    Last Post: 08-22-2009, 05:45 PM
  5. generating plot based on xstart xstop values
    By frankp in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-26-2007, 02:50 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