Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
Given:
01/05/09 $5,000 02/08/09 $12,000 03/06/09 $0 04/08/09 $13,000 5/13/09 $40,000 6/12/09 $2,000 8/5/09 $15,000 9/8/09 $63,000 10/09/09 $1,000 In example above, I need formula to find most recent date when dollar amount greater than $10,000 became available. So, I need formula to select date 8/5/09 in above example. $12,000 on 2/8/09 was first incidence, 4/8/09 was 2nd incidence and 8/5/09 is last incidence, which is what is needed. Thanks for any help given! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
Not sure, but if you meant you wanted this "date": 9/8/09 (from your sample
data), ie the maximum row down where the amount is 10k then this expression in say, C1, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =INDEX(A1:A9,MATCH(MAX(IF(B1:B910000,(ROW(A1:A9)) )),IF(B1:B910000,(ROW(A1:A9)),0))) will extract it -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Carl S." wrote: Given: 01/05/09 $5,000 02/08/09 $12,000 03/06/09 $0 04/08/09 $13,000 5/13/09 $40,000 6/12/09 $2,000 8/5/09 $15,000 9/8/09 $63,000 10/09/09 $1,000 In example above, I need formula to find most recent date when dollar amount greater than $10,000 became available. So, I need formula to select date 8/5/09 in above example. $12,000 on 2/8/09 was first incidence, 4/8/09 was 2nd incidence and 8/5/09 is last incidence, which is what is needed. Thanks for any help given! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
Try this:
=INDEX(A1:A9,LOOKUP(2,1/((B1:B8<=10000)*(B2:B910000)),ROW(A1:A9))-ROW(A1)+2) Format as Date If the condition is not met you'll get a result of #N/A. -- Biff Microsoft Excel MVP "Carl S." wrote in message ... Given: 01/05/09 $5,000 02/08/09 $12,000 03/06/09 $0 04/08/09 $13,000 5/13/09 $40,000 6/12/09 $2,000 8/5/09 $15,000 9/8/09 $63,000 10/09/09 $1,000 In example above, I need formula to find most recent date when dollar amount greater than $10,000 became available. So, I need formula to select date 8/5/09 in above example. $12,000 on 2/8/09 was first incidence, 4/8/09 was 2nd incidence and 8/5/09 is last incidence, which is what is needed. Thanks for any help given! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
Thank you T. Valko! Worked like a charm! I didn't know you could multiply
ranges like that, much less true and falses. I used offset because I had named ranges...but I'm glad you didn't include that in the provided formula to keep it clean. I tried to simply the formula by doing {MAX (num range<=10000) * (offsetted num range10000) * offsetted Date range } ...and it worked, but only if DATE was present. Since it was blank in some cells, that wouldn't fly, so I used your formula. Thanks again! "T. Valko" wrote: Try this: =INDEX(A1:A9,LOOKUP(2,1/((B1:B8<=10000)*(B2:B910000)),ROW(A1:A9))-ROW(A1)+2) Format as Date If the condition is not met you'll get a result of #N/A. -- Biff Microsoft Excel MVP "Carl S." wrote in message ... Given: 01/05/09 $5,000 02/08/09 $12,000 03/06/09 $0 04/08/09 $13,000 5/13/09 $40,000 6/12/09 $2,000 8/5/09 $15,000 9/8/09 $63,000 10/09/09 $1,000 In example above, I need formula to find most recent date when dollar amount greater than $10,000 became available. So, I need formula to select date 8/5/09 in above example. $12,000 on 2/8/09 was first incidence, 4/8/09 was 2nd incidence and 8/5/09 is last incidence, which is what is needed. Thanks for any help given! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
In reference to my last post dates were only blank at the end of the range.
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
Thanks Max, but I wanted the most recent incidence over 10K, not greatest
amount over 10k most recent. T. Valko gave me a formula that worked great. The amounts are investor dollars for real estate, and we only use their money when we have 10k or more available, and I wanted to know how long it had been since 10K became available, so we can fairly place investors in the next property we obtained. Thanks for trying, I appreciate it! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Carl S." wrote in message ... Thank you T. Valko! Worked like a charm! I didn't know you could multiply ranges like that, much less true and falses. I used offset because I had named ranges...but I'm glad you didn't include that in the provided formula to keep it clean. I tried to simply the formula by doing {MAX (num range<=10000) * (offsetted num range10000) * offsetted Date range } ...and it worked, but only if DATE was present. Since it was blank in some cells, that wouldn't fly, so I used your formula. Thanks again! "T. Valko" wrote: Try this: =INDEX(A1:A9,LOOKUP(2,1/((B1:B8<=10000)*(B2:B910000)),ROW(A1:A9))-ROW(A1)+2) Format as Date If the condition is not met you'll get a result of #N/A. -- Biff Microsoft Excel MVP "Carl S." wrote in message ... Given: 01/05/09 $5,000 02/08/09 $12,000 03/06/09 $0 04/08/09 $13,000 5/13/09 $40,000 6/12/09 $2,000 8/5/09 $15,000 9/8/09 $63,000 10/09/09 $1,000 In example above, I need formula to find most recent date when dollar amount greater than $10,000 became available. So, I need formula to select date 8/5/09 in above example. $12,000 on 2/8/09 was first incidence, 4/8/09 was 2nd incidence and 8/5/09 is last incidence, which is what is needed. Thanks for any help given! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find LAST date when corresponing min value became available
No prob. Thanks for feeding back.
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Carl S." wrote in message ... Thanks Max, but I wanted the most recent incidence over 10K, not greatest amount over 10k most recent. T. Valko gave me a formula that worked great. The amounts are investor dollars for real estate, and we only use their money when we have 10k or more available, and I wanted to know how long it had been since 10K became available, so we can fairly place investors in the next property we obtained. Thanks for trying, I appreciate it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Specific date in Biwwekly Based on date | Excel Discussion (Misc queries) | |||
Julian date - find next highest date/number | Excel Worksheet Functions | |||
Find the date of the coming up Saturday given the current date. | Excel Worksheet Functions | |||
If specific text result,... corresponing cell info in another colu | Excel Worksheet Functions | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions |