VLookup/if/... Help

D

deacs

I'm stumped because I know what I want to do, but am unsure how to do
it.

Here's the setup:

I have in Column A, a list of days. In Column B is a list of numbers
that correspond to the days in Column A.

I would like for Column C to do this:
If Column A is equal to Monday, then take the percentage change in
number from Column B for this Monday from the previous Monday that
appears in Column A. I could contruct a simple IF statement if the
days in Column A did not skip days (i.e. repeated Sunday to Saturday
down the column). Unfortunately, there are times when certain days of
the week are skipped.

I hope this is clear. Thanks in advance for any help! Please let me
know if I can make this more understandable.
 
D

Dave Peterson

Something like this will get the value from column B for the previous monday:

=IF(WEEKDAY(A6)<>2,"notMonday",VLOOKUP(A6-7,$A$1:$B$99,2,FALSE))

So I _think_ you want:
=IF(WEEKDAY(A2)<>2,"notMonday",(B2-VLOOKUP(A2-7,$A$1:$B$999,2,FALSE))/B2/100)
 
D

deacs

Thanks, Dave for your reply. I'm sorry I was a little unclear with m
inquiry. Let me try to do this right this time. I wish I knew how t
post a picture of my excel sheet to make this more visual.

First a little background. The purpose of this excercise to to figur
out the weekly returns of a stock index (let's assume we're talkin
about the S&P 500) for each day of the week. So, we're trying to se
the weekly return of the index for a week measured Monday-Friday
Tuesday-Monday, Wednesday-Tuesday,... Thus, my original post inquirin
about the skipped days due to market holidays was important, bu
incomplete.

Here's the set up(this time with the appropriate columns):
Column A: Days of the week that the market has traded
Column H: Index values
Column J: % change of Column H for weeks starting with Monday in Colum
A.
Column K:% change of Column H for weeks starting with Tuesday in Colum
A.
Column L: % change of Column H for weeks starting with Wednesday i
Column A.
Column M:% change of Column H for weeks starting with Thursday i
Column A.
Column N:% change of Column H for weeks starting with Friday in Colum
A.

Thanks again, everyone
 
Top