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!

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!

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!

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!

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.

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!

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!

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!