LONG IF (diff columns) STATMENT(s)

[Guest]

i have a complex if statement that i need help with. I have a contract that
tells to post a price from different days then from the day the price was
published. I need a formula that will give me Thursday's price for Monday
etc.

Day Price Publication Price
Mon Thurs (3days after mon)
Tue Fri (" ")
Wed Sat(" ")
Thurs Mon
Fri Wed
Sat/Sun Wed

I have the day (ddd) in one column - can i reference back to that somehow?

day(ddd) is in column c, the price is in column w, how can i tell column s
to give me thurs price for the monday cell, etc based on the price date
parameters? Is there even a way?

thx for all your help in advance!

 

[Bernard Liengme]

I have the days of the week (Mon, Tue....) in C1:C7
I have the prices in S1:S7 starting with Monday's price
In D1 I have =INDEX($S$1:$S$7,MOD(ROW(),7)+3-(MOD(ROW(),7)+3>6)*6)
This is copied down the D7
I hope Fri needed Tue's price not Wed's !
If you have the days in C but not starting in C1: say in C5 then replace
ROW() by ROW()-4 in both places.
Adjust S1:S7 to fit your model
best wishes

 

[Guest]

I thought about it this wknd and I finally came up with what i need rather
than what i want (haha).

Col B is a date column for the month [B11:B41 (Oct) ]
Col O is a sum column that will give me a number <0 or >0. (O11:O41)
Col S should have a price derived dependant on what day it is (S11:S41)
"Price" is another sheet that has a base price feed A5:A∞ (date) and
B5:B∞($$). "Price" automatically updates every day from a db.

I would like S11 to first check if O11 is > or < 0. [ if(O11<0,O11,0) ]
if O11 is less than 0 then i want it to check Col B for the day (sun,mon,tue
etc) - which i am guessing will evaluate it as sun=1, mon=2, tue=3 etc..
Once it has been checked, if B11 is:
- Sunday (in this case 10/1/06), i need Wednesday (3 days after-
10/4/06) pricing. (Price!B952)
- Mon(10/2/06), i need Thursday (10/5/06) pricing.
- Tue(10/3/06), i need Friday (10/6/06) pricing.
- Wed(10/4/06), i need Saturday (10/7/06) pricing.
- Thur(10/5/06), i need Monday(10/9/06) pricing.
- Fri/Sat, i need Wed(10/11/06) pricing.

This is continous and i don't know how tomake it keep checking downward as
the feed populates the date and price downward.

Thanks again for all the help!