index function? (Office 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Daniel Rozenberg.

Reply | Quote

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

Reply | Quote

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)?

Reply | Quote

I have attached your workbook with two different formulas.

Reply | Quote

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

Daniel Rozenberg.

Reply | Quote

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

Reply | Quote