+ Reply to Thread
Results 1 to 10 of 10

Formula to get number based on sequence

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,377

    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

  2. #2
    Forum Guru 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.

  3. #3
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,377

    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

  4. #4
    Forum Guru 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

  5. #5
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,377

    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

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    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

  7. #7
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,377

    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

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Formula to get number based on sequence

    You didn't like my solution?

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,377

    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

  10. #10
    Forum Guru 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 to 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. 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. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1