+ Reply to Thread
Results 1 to 10 of 10

Formula to get number based on sequence

  1. 11-16-2016, 08:16 PM #1
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Formula to get number based on sequence

    Hello:

    Please refer to attached file.
    I have date data as shown in column A and corresponding number (data) in column B.
    I need a formula in cell B8 to get me the number as above sequence.

    Let me know if you have any questions.
    Thanks.
    Riz
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-16-2016, 08:32 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to get number based on sequence

    Here's the data in your file:

    Data Range
    A
    B
    1
    1/1/2014
    2
    2
    1/2/2014
    3
    3
    1/3/2014
    4
    4
    1/4/2014
    5
    5
    1/5/2014
    6
    6
    1/6/2014
    7
    7
    1/7/2014
    8
    8
    11/16/2016
    9
    ------
    ------


    Tell us what result you expect in B8.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 11-16-2016, 08:39 PM #3
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get number based on sequence

    Hello Tony:

    I am expecting B8 = 1058
    Please let me know if you have any questions.
    Thanks.

    Riz
    Reply With QuoteRegister To Reply
  4. 11-16-2016, 08:46 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to get number based on sequence

    Not sure I understand the logic but this is what I used to get the result of 1058.

    =A8-A1+B7
    Reply With QuoteRegister To Reply
  5. 11-16-2016, 09:02 PM #5
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get number based on sequence

    Hi Tony:

    My mistake, B8 needs to be 1052.
    Hello:

    Sorry for misunderstanding.
    Lets see if this makes it clear.
    I have
    A2 = 1/1/2014 and i need formula in cell B2 to give number 2
    A3 = 1/2/2014 and i need formula in cell B3 to give number 3
    A2 = 1/3/2014 and i need formula in cell B4 to give number 4
    A5 = 1/4/2014 and i need formula in cell B5 to give number 5

    and so on

    Please let me know if you have any questions.
    Thanks.

    Riz
    Reply With QuoteRegister To Reply
  6. 11-16-2016, 09:27 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to get number based on sequence

    A
    B
    C
    1
    1/1/2014
    2
    		 B1: =A1 - "12/30/13"
    2
    1/2/2014
    3
    3
    1/3/2014
    4
    4
    1/4/2014
    5
    5
    1/5/2014
    6
    6
    1/6/2014
    7
    7
    1/7/2014
    8
    8
    11/16/2016
    1052


    Your question could hardly be more cryptic, riz. How about just "elapsed days"?
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 11-16-2016, 10:09 PM #7
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get number based on sequence

    Hi shg and Tony:

    I got it figured out.
    Convert column A dates into number and then start B2 = A2-41638 and copy down.

    Thanks

    Riz
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  8. 11-17-2016, 12:57 AM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to get number based on sequence

    You didn't like my solution?
    Reply With QuoteRegister To Reply
  9. 11-17-2016, 05:30 AM #9
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get number based on sequence

    Hi sng:

    Yes i liked your solution but i posted and figured out the solution as above just before you posted the solution.
    Great logic, will work.
    Thanks a lot.
    Riz
    Reply With QuoteRegister To Reply
  10. 11-17-2016, 07:29 AM #10
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to get number based on sequence

    Good deal. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. auto- sequence number based on another column?
    By maynard in forum Excel General
    Replies: 6
    Last Post: 07-29-2016, 10:31 PM
  2. generate number sequence based on cell value
    By Pmaldini3 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-22-2015, 07:13 AM
  3. Formula to calculate next number in sequence based on varying criteria
    By Twaddy006 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2014, 05:53 PM
  4. [SOLVED] sequence number based on date
    By denise in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2014, 03:09 PM
  5. VBA - Add a Sequence Number based on Condition
    By AGALLEGOS in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2014, 06:03 PM
  6. [SOLVED] Formula to display sequence number based on contiguous start and end dates
    By AlanCake in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-04-2013, 07:37 AM
  7. Formula for calculating sequence number in third column based upon first 2 columns
    By arunmann in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2013, 03:59 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1