Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    index function? (Office 2003)

    Hi Everyone;

    I need some help to indicate me the proper function which will give the desired data. Scenario is as follows:
    I have a table, first column are dates at weekly intervals; second are sales at end of week. I want to find the function which will generate for every row a new column listing percent increment (or decrement) in sales relative to a YEAR before given date in column 1.
    I explored the Index function, but it seems to complex.
    I have an Excel book (for Office 97) where I will read and train myself to solve this.
    Any guidance to where I should look will be very much appreciated.

    DanielR.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: index function? (Office 2003)

    Since weeks do not end on the same date each year, what do you define the previous years date to be? Are the dates for the previous year in the same column on the same sheet as this years data? It would help a lot if you would upload a sample workbook with false data that shows what you have and indicates what you want.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: index function? (Office 2003)

    Thanks for your reply.
    Here I am attaching a sample.
    Thanks in advance for your help.

    Daniel Rozenberg.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: index function? (Office 2003)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> I think I am onto it, but it is an idea for the moment. In cell C2 copy this formula into it:

    =TRUNC(((A2-DATE(YEAR(A2),1,0))+6)/7)

    and then copy down this will give you the absolute week number that date falls into.

    I then created a pivot table against

    first row header - Week number
    Second Row Header - Date

    and did a sum of the sales, it grouped the week dates nicely for you to do your percentage calculation
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: index function? (Office 2003)

    How do you want to handle missing dates such as 27-Aug-04?
    And dates that don't match the pattern, such as 04-Sep-04 (a Saturday instead of a Friday)?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: index function? (Office 2003)

    I have attached your workbook with two different formulas.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: index function? (Office 2003)

    Gentlemen,
    Thank you very much for all your replies.
    I will analyze (digest?) them, and let you know asap.
    Cordially,

    Daniel Rozenberg.

  8. #8
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: index function? (Office 2003)

    After viewing your replies, I find that will be easier for me to use again VLOOKUP (I had some successful experience with it from the past), so a "brush up" will be easier.
    Thanks for you approach Jeeza, but it will require me to further go into Pivot Tables (which gave a bit of trouble in the past).
    Thank you all again.

    Daniel Rozenberg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •