Find LAST date when corresponing min value became available

[Carl S.]

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!

 

[Max]

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:B9>10000,(ROW(A1:A9)))),IF(B1:B9>10000,(ROW(A1:A9)),0)))
will extract it
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[T. Valko]

Try this:

=INDEX(A1:A9,LOOKUP(2,1/((B1:B8<=10000)*(B2:B9>10000)),ROW(A1:A9))-ROW(A1)+2)

Format as Date

If the condition is not met you'll get a result of #N/A.

 

[Carl S.]

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 range>10000) * 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!

 

[Carl S.]

In reference to my last post dates were only blank at the end of the range.

 

[Carl S.]

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!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Max]

No prob. Thanks for feeding back.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik