Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Index/Match Help
I have the following formula to extract the date of the
most recent sale from a list. =INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATCH (O4,O13:T13,0)) I would like to build another formula to extract the next recent sales date entry. This entry may have the same date as the most recent in the list. I have tried entering -1 in various places, only to retrive errors or yesterdays date with no data (in the case there was no sale yesterday). Does anyone have any ideas? Thank you. |
#2
|
|||
|
|||
You have not said what is in the various ranges referenced by your
formula. Assuming that you have a range of sale dates, =MAX(dateRange) is the date of the most recent sale and =LARGE(dateRange,2) is the date of the second most recent sale. Jerry Ben wrote: I have the following formula to extract the date of the most recent sale from a list. =INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATCH (O4,O13:T13,0)) I would like to build another formula to extract the next recent sales date entry. This entry may have the same date as the most recent in the list. I have tried entering -1 in various places, only to retrive errors or yesterdays date with no data (in the case there was no sale yesterday). Does anyone have any ideas? Thank you. |
#3
|
|||
|
|||
I'm not sure I understand... maybe this helps:
SALES - total area of data, including date, customer, etc. SALE_DATE - list of dates, may have skipped/blank rows O4 - Cell w/ "DATE", "Customer" etc O13:T13 - List titles including "DATE", "CUSTOMER" etc SALE_DATE never exceeds today() but may equal it. Numerous entries may have the same date. Entry to entry may skip numerous days, ie O14=11/11/04 and O15=12/1/04. Is this better? Thanks -----Original Message----- You have not said what is in the various ranges referenced by your formula. Assuming that you have a range of sale dates, =MAX(dateRange) is the date of the most recent sale and =LARGE (dateRange,2) is the date of the second most recent sale. Jerry Ben wrote: I have the following formula to extract the date of the most recent sale from a list. =INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATCH (O4,O13:T13,0)) I would like to build another formula to extract the next recent sales date entry. This entry may have the same date as the most recent in the list. I have tried entering -1 in various places, only to retrive errors or yesterdays date with no data (in the case there was no sale yesterday). Does anyone have any ideas? Thank you. . |
#4
|
|||
|
|||
You said that you want to extract the dates of the two most recent
sales. Why do the following not do what you want? =MAX(SALE_DATE) =LARGE(SALE_DATE,2) Jerry Ben wrote: I'm not sure I understand... maybe this helps: SALES - total area of data, including date, customer, etc. SALE_DATE - list of dates, may have skipped/blank rows O4 - Cell w/ "DATE", "Customer" etc O13:T13 - List titles including "DATE", "CUSTOMER" etc SALE_DATE never exceeds today() but may equal it. Numerous entries may have the same date. Entry to entry may skip numerous days, ie O14=11/11/04 and O15=12/1/04. Is this better? Thanks -----Original Message----- You have not said what is in the various ranges referenced by your formula. Assuming that you have a range of sale dates, =MAX(dateRange) is the date of the most recent sale and =LARGE (dateRange,2) is the date of the second most recent sale. Jerry Ben wrote: I have the following formula to extract the date of the most recent sale from a list. =INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATCH (O4,O13:T13,0)) I would like to build another formula to extract the next recent sales date entry. This entry may have the same date as the most recent in the list. I have tried entering -1 in various places, only to retrive errors or yesterdays date with no data (in the case there was no sale yesterday). Does anyone have any ideas? Thank you. |
#5
|
|||
|
|||
Actually I want the last 5 dates with additional data with
each date. -----Original Message----- You said that you want to extract the dates of the two most recent sales. Why do the following not do what you want? =MAX(SALE_DATE) =LARGE(SALE_DATE,2) Jerry Ben wrote: I'm not sure I understand... maybe this helps: SALES - total area of data, including date, customer, etc. SALE_DATE - list of dates, may have skipped/blank rows O4 - Cell w/ "DATE", "Customer" etc O13:T13 - List titles including "DATE", "CUSTOMER" etc SALE_DATE never exceeds today() but may equal it. Numerous entries may have the same date. Entry to entry may skip numerous days, ie O14=11/11/04 and O15=12/1/04. Is this better? Thanks -----Original Message----- You have not said what is in the various ranges referenced by your formula. Assuming that you have a range of sale dates, =MAX(dateRange) is the date of the most recent sale and =LARGE (dateRange,2) is the date of the second most recent sale. Jerry Ben wrote: I have the following formula to extract the date of the most recent sale from a list. =INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATC H (O4,O13:T13,0)) I would like to build another formula to extract the next recent sales date entry. This entry may have the same date as the most recent in the list. I have tried entering -1 in various places, only to retrive errors or yesterdays date with no data (in the case there was no sale yesterday). Does anyone have any ideas? Thank you. . |
#6
|
|||
|
|||
Thanks Jerry. I am getting ahead of myself. I pick the
date with the method you give, then the additional information using the extacted date in the first match clause. I was making it too complicated. Thank you very much. Ben -----Original Message----- You said that you want to extract the dates of the two most recent sales. Why do the following not do what you want? =MAX(SALE_DATE) =LARGE(SALE_DATE,2) Jerry Ben wrote: I'm not sure I understand... maybe this helps: SALES - total area of data, including date, customer, etc. SALE_DATE - list of dates, may have skipped/blank rows O4 - Cell w/ "DATE", "Customer" etc O13:T13 - List titles including "DATE", "CUSTOMER" etc SALE_DATE never exceeds today() but may equal it. Numerous entries may have the same date. Entry to entry may skip numerous days, ie O14=11/11/04 and O15=12/1/04. Is this better? Thanks -----Original Message----- You have not said what is in the various ranges referenced by your formula. Assuming that you have a range of sale dates, =MAX(dateRange) is the date of the most recent sale and =LARGE (dateRange,2) is the date of the second most recent sale. Jerry Ben wrote: I have the following formula to extract the date of the most recent sale from a list. =INDEX(SALES,MATCH(TODAY(),SALE_DATE,1),MATC H (O4,O13:T13,0)) I would like to build another formula to extract the next recent sales date entry. This entry may have the same date as the most recent in the list. I have tried entering -1 in various places, only to retrive errors or yesterdays date with no data (in the case there was no sale yesterday). Does anyone have any ideas? Thank you. . |
#7
|
|||
|
|||
You're welcome; glad it helped.
Jerry Ben wrote: Thanks Jerry. I am getting ahead of myself. I pick the date with the method you give, then the additional information using the extacted date in the first match clause. I was making it too complicated. Thank you very much. Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|