# Thread: index function? (Office 2003)

1. ## 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. ## 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.

3. ## Re: index function? (Office 2003)

Here I am attaching a sample.

Daniel Rozenberg.

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

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

5. ## 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. ## Re: index function? (Office 2003)

I have attached your workbook with two different formulas.

7. ## 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. ## 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
•