If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formula to lookup data in cell NEXT to another cell
I have downloaded some historical quotes for MSFT in one spreadsheet. IN
another sheet I want to write a formula to say find June 19, 2009 and look for the previous TRADING DAY (not previous day, which could be a weekend day). How do I do that? |
#2
|
|||
|
|||
formula to lookup data in cell NEXT to another cell
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "TraderXL" wrote in message ... I have downloaded some historical quotes for MSFT in one spreadsheet. IN another sheet I want to write a formula to say find June 19, 2009 and look for the previous TRADING DAY (not previous day, which could be a weekend day). How do I do that? |
#3
|
|||
|
|||
formula to lookup data in cell NEXT to another cell
In most cases, we would use a VLOOKUP() on the date in question. In your
case however, we want to lookup using the previous day (excluding Sat or Sun) In A1, setup a list of consecutive day, in B1 enter: =A1-(WEEKDAY(A1)=1)-1-2*(WEEKDAY(A1)=2) and copy down. We see: Saturday 06/20/09 Friday 06/19/09 Sunday 06/21/09 Friday 06/19/09 Monday 06/22/09 Friday 06/19/09 Tuesday 06/23/09 Monday 06/22/09 Wednesday 06/24/09 Tuesday 06/23/09 Thursday 06/25/09 Wednesday 06/24/09 Friday 06/26/09 Thursday 06/25/09 Saturday 06/27/09 Friday 06/26/09 Sunday 06/28/09 Friday 06/26/09 Monday 06/29/09 Friday 06/26/09 Tuesday 06/30/09 Monday 06/29/09 Wednesday 07/01/09 Tuesday 06/30/09 Thursday 07/02/09 Wednesday 07/01/09 Friday 07/03/09 Thursday 07/02/09 Saturday 07/04/09 Friday 07/03/09 Sunday 07/05/09 Friday 07/03/09 Monday 07/06/09 Friday 07/03/09 Tuesday 07/07/09 Monday 07/06/09 Wednesday 07/08/09 Tuesday 07/07/09 Thursday 07/09/09 Wednesday 07/08/09 Friday 07/10/09 Thursday 07/09/09 Saturday 07/11/09 Friday 07/10/09 Sunday 07/12/09 Friday 07/10/09 Monday 07/13/09 Friday 07/10/09 Tuesday 07/14/09 Monday 07/13/09 Wednesday 07/15/09 Tuesday 07/14/09 Thursday 07/16/09 Wednesday 07/15/09 Friday 07/17/09 Thursday 07/16/09 So instead of using a formula of the form: =VLOOKUP(A1,some_table,some_column) we can use: =VLOOKUP(A1-(WEEKDAY(A1)=1)-1-2*(WEEKDAY(A1)=2),some_table,some_column) -- Gary''s Student - gsnu200858 "TraderXL" wrote: I have downloaded some historical quotes for MSFT in one spreadsheet. IN another sheet I want to write a formula to say find June 19, 2009 and look for the previous TRADING DAY (not previous day, which could be a weekend day). How do I do that? |
#4
|
|||
|
|||
formula to lookup data in cell NEXT to another cell
Hi,
Assume you have the dates in range C16:C23. Enter the date in cell G17 (in your case, it is June 19, 2009). In cell G20, enter the following array formula (Ctrl+Shift+Enter) =MAX(INDEX((C16:C23G17)*(WEEKDAY(ROW(INDIRECT(C16 &":"&C23)),2)6)*(C16:C23),,1)) This formula will give the last weekday prior to the date in cell G20. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "TraderXL" wrote in message ... I have downloaded some historical quotes for MSFT in one spreadsheet. IN another sheet I want to write a formula to say find June 19, 2009 and look for the previous TRADING DAY (not previous day, which could be a weekend day). How do I do that? |
Thread Tools | |
Display Modes | |
|
|