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 <0c539b6b-88c3-4c8c-8cbc-(E-Mail Removed)>,
Pete_UK <(E-Mail Removed)> 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=
>