Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LONG IF (diff columns) STATMENT(s)
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LONG IF (diff columns) STATMENT(s)
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)+36)*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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Daniel Q." wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LONG IF (diff columns) STATMENT(s)
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! "Bernard Liengme" wrote: 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)+36)*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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Daniel Q." wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert multiple columns into one long row | Excel Discussion (Misc queries) | |||
Message or input | Excel Discussion (Misc queries) | |||
"Text to Columns" for many columns in Excel 2003 | Excel Discussion (Misc queries) | |||
Need help setting the worksheet header/Footer margins based on string height? | Excel Discussion (Misc queries) | |||
Conditional Format as a MACRO | Excel Worksheet Functions |