Great advice is hard to beat. I get that in this group. Thank you!

I have a column of dates. Another column contains ordinary
integers, positive or negative. I want to find the lowest date
where the offset column is a negative number.

Example:

Col. F Col. G
------ ------
18-Apr-08 -12
14-Apr-08 12
7-Apr-08 -6
1-Apr-08 -24
24-Mar-08 -20
19-Mar-08 18


I'm looking for 24-Mar-08 -- the lowest date where Col. G is
negative. The dates might not always be sorted, in case that
matters. Can someone think of how? I'm stumped.

=dman=

Try this array* formula:

=MIN(IF(G1:G6<0,F1:F6,40000))

Adjust the ranges to suit your real data.

* As this is an array formula you must use CTRL-SHIFT-ENTER (CSE) to
commit it rather than the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - do not type these yourself. Use CSE again if you edit
the formula.

Hope this helps.

Pete

On Jun 22, 12:42*am, Dallman Ross <dman@localhost. wrote:
Great advice is hard to beat. *I get that in this group. *Thank you!

I have a column of dates. *Another column contains ordinary
integers, positive or negative. *I want to find the lowest date
where the offset column is a negative number.

Example:

* * * Col. F * *Col. G
* * * ------ * *------
* * 18-Apr-08 * *-12
* * 14-Apr-08 * * 12
* * *7-Apr-08 * * -6
* * *1-Apr-08 * *-24
* * 24-Mar-08 * *-20
* * 19-Mar-08 * * 18

I'm looking for 24-Mar-08 -- the lowest date where Col. G is
negative. *The dates might not always be sorted, in case that
matters. *Can someone think of how? *I'm stumped.

=dman=

Pete: excellent. Thank you!

=dman=

----------
In ,
Pete_UK spake thusly:

Try this array* formula:

=MIN(IF(G1:G6<0,F1:F6,40000))

Adjust the ranges to suit your real data.

* As this is an array formula you must use CTRL-SHIFT-ENTER (CSE) to
commit it rather than the usual ENTER. If you do this correctly then
Excel will wrap curly braces { } around the formula when viewed in the
formula bar - do not type these yourself. Use CSE again if you edit
the formula.

Hope this helps.

Pete

On Jun 22, 12:42*am, Dallman Ross <dman@localhost. wrote:
Great advice is hard to beat. *I get that in this group. *Thank you!

I have a column of dates. *Another column contains ordinary
integers, positive or negative. *I want to find the lowest date
where the offset column is a negative number.

Example:

* * * Col. F * *Col. G
* * * ------ * *------
* * 18-Apr-08 * *-12
* * 14-Apr-08 * * 12
* * *7-Apr-08 * * -6
* * *1-Apr-08 * *-24
* * 24-Mar-08 * *-20
* * 19-Mar-08 * * 18

I'm looking for 24-Mar-08 -- the lowest date where Col. G is
negative. *The dates might not always be sorted, in case that
matters. *Can someone think of how? *I'm stumped.

=dman=

You're welcome - thanks for feeding back.

Pete

On Jun 22, 2:30*am, Dallman Ross <dman@localhost. wrote:
Pete: excellent. *Thank you!

=dman=